10.4 Real-Time SQL Monitoring
The real-time SQL monitoring feature of Oracle Database enables you to monitor the performance of SQL statements while they are executing. By default, SQL monitoring is automatically started when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or I/O time in a single execution.
You can monitor the statistics for SQL statement execution using the V$SQL_MONITOR
and V$SQL_PLAN_MONITOR
views. These views can be used in conjunction with the following views to get additional information about the execution being monitored:
-
V$ACTIVE_SESSION_HISTORY
-
V$SESSION
-
V$SESSION_LONGOPS
-
V$SQL
-
V$SQL_PLAN
Once monitoring is initiated, an entry is added to the dynamic performance view V$SQL_MONITOR
. This entry tracks key performance metrics collected for the execution, including the elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times. These statistics are refreshed in near real-time as the statement executes, generally once every second. Once the execution ends, monitoring information is not deleted immediately, but is kept in the V$SQL_MONITOR
view for at least one minute. The entry will eventually be deleted so its space can be reclaimed as new statements are monitored.
The V$SQL_MONITOR
view contains a subset of the statistics available in V$SQL
. However, unlike V$SQL
, monitoring statistics are not cumulative over several executions. Instead, one entry in V$SQL_MONITOR
is dedicated to a single execution of a SQL statement. If two executions of the same SQL statement are being monitored, each of these executions will have a separate entry in V$SQL_MONITOR
.
To uniquely identify two executions of the same SQL statement, a composite key called an execution key is generated. This execution key is composed of three attributes, each corresponding to a column in V$SQL_MONITOR
:
-
SQL identifier to identify the SQL statement (
SQL_ID
) -
Start execution timestamp (
SQL_EXEC_START
) -
An internally generated identifier to ensure that this primary key is truly unique (
SQL_EXEC_ID
)
This section contains the following topics:
10.4.1 SQL Plan Monitoring
Real-time SQL monitoring also includes monitoring statistics for each operation in the execution plan of the SQL statement being monitored. This data is visible in the V$SQL_PLAN_MONITOR
view. Similar to the V$SQL_MONITOR
view, statistics in V$SQL_PLAN_MONITOR
are updated every second as the SQL statement is being executed. These statistics persist after the execution ends, with the same duration as V$SQL_MONITOR
. There will be multiple entries in V$SQL_PLAN_MONITOR
for every SQL statement being monitored; each entry will correspond to an operation in the execution plan of the statement.
10.4.2 Parallel Execution Monitoring
Parallel queries, DML and DDL statements are automatically monitored as soon as execution begins. Monitoring information for each process participating in the parallel execution is recorded as separate entries in the V$SQL_MONITOR
and V$SQL_PLAN_MONITOR
views. As a result, the V$SQL_MONITOR
view will have one entry for the parallel execution coordinator process, and one entry for each parallel execution server process. Each of these entries will have corresponding entries in the V$SQL_PLAN_MONITOR
view. Since the processes allocated for the parallel execution of a SQL statement are cooperating for the same execution, these entries share the same execution key (the composite SQL_ID
, SQL_EXEC_START
and SQL_EXEC_ID
). You can therefore aggregate the execution key to determine the overall statistics for a parallel execution.
10.4.3 Generating the SQL Monitor Report
You can use the SQL monitor report to view SQL monitoring data. The SQL monitor report uses data from several views, including:
-
GV$SQL_MONITOR
-
GV$SQL_PLAN_MONITOR
-
GV$SQL
-
GV$SQL_PLAN
-
GV$ACTIVE_SESSION_HISTORY
-
GV$SESSION_LONGOPS
To generate the SQL monitor report, run the REPORT_SQL_MONITOR
function in the DBMS_SQLTUNE
package:
variable my_rept CLOB; BEGIN :my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR(); END; / print :my_rept
The DBMS_SQLTUNE
.REPORT_SQL_MONITOR
function accepts several input parameters to specify the execution, the level of detail in the report, and the report type ('TEXT'
, 'HTML'
, or 'XML'
). By default, a text report is generated for the last execution that was monitored if no parameters are specified as shown in the example.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE
package
Example 10-1 shows the output of the SQL Monitor Report for the last execution of a SQL statement that was monitored.
Example 10-1 Sample SQL Monitor Report
set long 10000000 set longchunksize 10000000 set linesize 200 select dbms_sqltune.report_sql_monitor from dual; SQL Text ---------------------------------------------------------------------------------------- select * from (select O_ORDERDATE, sum(O_TOTALPRICE) from orders o, lineitem l where l.l_orderkey = o.o_orderkey group by o_orderdate order by o_orderdate) where rownum < 100 ---------------------------------------------------------------------------------------- Global Information Status : EXECUTING Instance ID : 1 Session ID : 980 SQL ID : br4m75c20p97h SQL Execution ID : 16777219 Plan Hash Value : 2992965678 Execution Started : 06/07/2007 08:36:42 First Refresh Time : 06/07/2007 08:36:46 Last Refresh Time : 06/07/2007 08:40:02 ----------------------------------------------------------------------------------- | Elapsed | Cpu | IO | Application | Other | Buffer | Reads | Writes | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | | | ----------------------------------------------------------------------------------- | 198 | 140 | 56 | 0.31 | 1.44 | 1195K | 1264K | 84630 | ----------------------------------------------------------------------------------- SQL Plan Monitoring Details ======================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | | | | | (Estim) | | Active(s) | Active | ======================================================================================= | 0 | SELECT STATEMENT | | | 125K | | | | 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 2406 | 125K | | | | 3 | SORT GROUP BY STOPKEY | | 2406 | 125K | 99 | +101 | | -> 4 | HASH JOIN | | 8984K | 123K | 189 | +12 | | | | | | | | | | 5 | INDEX FAST FULL SCAN | I_L_OKEY | 8984K | 63191 | 82 | +1 | | | | | | | | | | 6 | PARTITION RANGE ALL | | 44913K | 57676 | 94 | +84 | | 7 | PARTITION HASH ALL | | 44913K | 57676 | 94 | +84 | | 8 | TABLE ACCESS FULL | ORDERS | 44913K | 57676 | 95 | +84 | | | | | | | | | | | | | | | | | ======================================================================================= continuation of above table ======================================================================================= Starts | Rows | Memory | Temp | Activity | Activity Detail | Progress | | (Actual) | | | (percent) | (sample #) | | ======================================================================================= 1 | | | | | | | 1 | | | | | | | 1 | | | | | | | 1 | 0 | | | 4.02 | Cpu (8) | | 1 | 28130K | 10000K | 724M | 25.13 | Cpu (48) | 87% | | | | | | direct path read temp (2) | | 1 | 32734K | | | 34.17 | Cpu (58) | 100% | | | | | | direct path read (10) | | 1 | 45000K | | | | | | 84 | 45000K | | | | | | 672 | 45000K | | | 36.68 | Cpu (28) | | | | | | | reliable message (3) | | | | | | | direct path read (42) | | =======================================================================================
In the Global Information section of this report, the Status field shows that this statement is still executing. The Time Active(s) column shows how long the operation has been active (the delta in seconds between the first and the last active time). The Start Active column shows, in seconds, when the operation in the execution plan started relative to the SQL statement execution start time. In this report, the fast full scan operation at ID 5 was the first to start (+1s Start Active) and ran for the first 82 seconds.The Starts column shows the number of times the operation in the execution plan was executed. The Rows (Actual) column indicates the number of rows produced, and the Rows (Estim) column shows the estimated cardinality from the optimizer. The Memory and Temp columns indicate the amount of memory and temporary space consumed by each operation of the execution plan.The Activity (percent) and Activity Detail (sample #) columns are derived by joining the V$SQL_PLAN_MONITOR
and V$ACTIVE_SESSION_HISTORY
views. Activity (percent) shows the percentage of database time consumed by each operation of the execution plan. Activity Detail (sample#) shows the nature of that activity (such as CPU or wait event). In this report, this column shows that most of the database time, 36.68%, is consumed by operation ID 8 (TABLE ACCESS FULL
of ORDERS
). This activity consists of 73 samples (28+3+42), of which more than half of the activity is attributed to direct path read (42 samples), and a third to CPU (28 samples).The last column, Progress, shows progress monitoring information for the operation from the V$SESSION_LONGOPS
view. In this report, it shows that the hash-join operation is 87% complete.
10.4.4 Enabling and Disabling SQL Monitoring
The SQL monitoring feature is enabled by default when the STATISTICS_LEVEL
initialization parameter is either set to ALL
or TYPICAL
(the default value). Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS
parameter must be set to DIAGNOSTIC+TUNING
(the default value) because SQL monitoring is a feature of the Oracle Database Tuning Pack. SQL monitoring starts automatically for all long running queries.
Two statement-level hints are available to force or prevent a SQL statement from being monitored. To force SQL monitoring, use the MONITOR
hint:
select /*+MONITOR*/ from dual;
This hint is effective only when the CONTROL_MANAGEMENT_PACK_ACCESS
parameter is set to DIAGNOSTIC+TUNING
. To prevent the hinted SQL statement from being monitored, use the NO_MONITOR
reverse hint.
See Also:
Oracle Database SQLQW Language Reference for information about using the MONITOR
and NO_MONITOR
hints