【11g】Real-Time SQL Monitoring

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:

• SQL Plan Monitoring

• Parallel Execution Monitoring

• Generating the SQL Monitor Report

• Enabling and Disabling SQL Monitoring

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 MONITORhint:

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