A database operation is:
• One or more SQL or PL/SQL statements
• A series of statements done from on or more sessions
A database operation can:
• Be monitored
• Produce active reports

A DB operation is an operation that the database server runs to accomplish tasks. It can be either simple or composite.
• Simple database operations: Consist of a single SQL statement or a single PL/SQL function or procedure
• Composite database operations: Consist of the activity of one or more sessions between two points in time. SQL statements or PL/SQL procedures running in these sessions are part of the composite operations:
– Single sessions: Single-session operations fall into two cases.
– In one case, exactly one session exists for the duration of the database
operation.
– In the other case, multiple sessions exist in the database operation, but no more than one session runs at any given time. The application jumps from session to session.
– Multiple concurrent sessions: In ETL, it is common for a job to involve multiple sessions running at the same time. You can define the entire ETL job as a single database operation.

In this illustration, there are four sessions. The first (upper) session shows that a session can belong to at the most one database operation at a time. It belongs to database operation A at first, then belongs to database operation B. The END_OPERATION and then BEGIN_OPERATION commands cause the session to change database operations.
Sessions 1, 2, and 3 show that the name and execution ID are taken together to uniquely identify the database operation B.
The time that a session belongs to a database operation but is not executing a SQL or PL/SQL statement is idle time.

Enabling Monitoring of Database Operations
•At system level:
– Set STATISTICS_LEVEL to TYPICAL
– Set CONTROL_MANAGEMENT_PACK_ACCESS to DIAGNOSTIC+TUNING
•At database operation level:
– Use FORCED_TRACKING attribute in
DBMS_SQL_MONITOR.BEGIN_OPERATION function
• At statement level:
– Use the MONITOR hint

Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING

Identifying, Starting and Completing a Database Operation
1. Identify the database operation.
– Operation Name
– Execution ID
2. Start the database operation with
DBMS_SQL_MONITOR.BEGIN_OPERATION.
3. Complete the database operation with
DBMS_SQL_MONITOR.END_OPERATION.

SQL> VAR dbop_eid NUMBER;
SQL> EXEC :dbop_eid := DBMS_SQL_MONITOR.BEGIN_OPERATION (‘ORA.MV.refresh’, FORCED_TRACKING => ‘Y’)
SQL> SELECT …
SQL> SELECT …
SQL> EXEC DBMS_SQL_MONITOR.END_OPERATION (‘ORA.MV.refresh’, :dbop_eid)

Completed database operations and reports are stored in AWR and can be reported in the following views:
– DBA_HIST_REPORTS, DBA_HIST_REPORTS_DETAILS
– DBA_HIST_ACTIVE_SESS_HISTORY