Real-time SQL monitoring

Real-time SQL monitoring can monitor the performance of SQL while it is running. By default, real-time SQL monitoring starts automatically when a single SQL execution takes more than 5 seconds of CPU or I/O time or SQL parallel execution.

You can view the statistics for the SQL statement runtime through view V$sql_monitor, V$sql_plan_monitor.

More information can be obtained by combining the following views: V$active_session_history, V$session, V$session_longops, V$sql, V$sql_plan

Once monitoring is started, a entry is added to the V$sql_monitor, including performance statistics. At the end of execution, the corresponding entry will not be deleted immediately, but will remain for one minute. Unlike V$sql_monitor and V$sql, a entry in the former corresponds to a single executed statement, which is the cumulative result.

For those query SQL that executes the plan, if the implicit parameter "_sqlmon_max_planlines" is exceeded, the default is 300,sql monitor to reduce overhead (CPU and memory).
This parameter can be modified dynamically:

Sql> alter system set "_sqlmon_max_planlines" =500 Scope=both;

1.real-time SQL monitoring mainly contains the following aspects:

(1). SQL Plan monitoring real-time SQL monitoring also contains statistics that monitor each step of the execution plan. Can be viewed through v$sql_plan_monitor. The Save cycle for statistics is similar to V$sql_monitor. There are multiple entry for a SQL in V$sql_plan_monitor.

(2). Parallel execution monitoring parallel queries, parallel DDL, parallel DML are monitored automatically by real-time SQL monitoring.

2. Generate real-time SQL monitoring reports

The real-time SQL Monitoring report covers the following views:

Gv$sql_monitor, Gv$sql_plan_monitor, Gv$sql, Gv$sql_plan, Gv$active_session_history, Gv$session_longops

Dbms_sqltune.report_sql_monitor can return a monitoring report for a specified SQL

sql> var my_rept clob; Sql> begin  2     : my_rept: = Dbms_sqltune.report_sql_monitor (), #默认是text格式, and HTML, XML format  3  end;  4  /pl/sql procedure successfully completed. Sql> print:my_rept

To view the SQL Monitor report for a SQL in HTML format:

Set LONG 1000000 set FEEDBACK offspool monitor_sql.html SELECT dbms_sqltune.report_sql_monitor (sql_id = " 0tqfh0cggfg0v ',type=> ' HTML ') as report from Dual;spool off

View the SQL Monitor report for a SQL in text format:

SET LONG 1000000SET longchunksize 1000000SET linesize 1000SET PAGESIZE 0SET TRIM ONSET trimspool ONSET ECHO OFFSET Feedbac K offselect dbms_sqltune.report_sql_monitor (sql_id = ' <sql_id> ', type = ' TEXT ') as report from dual;

Example Text results:

Set long 10000000set longchunksize 10000000set linesize 200select 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_orde Rkey GROUP BY O_orderdate Order by O_orderdate) where RowNum <------------------------- ---------------------------------------------------------------Global Information status:executing# is performing I Nstance id:1 Session id:980 SQL id:br4m75c20p97h SQL execution id:16777219 Pl  A 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# time Active (s): The duration of the step operation, in seconds # start active: The time in the execution plan relative to when SQL started executing, in seconds ========================= ==============================================================|         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: Indicates number of runs in execution Plan # Rows (Actual): Number of rows generated # activity (percent): The percentage of database time spent on the entire execution plan # activity Detail (Sample #): Displays the nature of the activity, such as CPU, wait events ========================   =============================================================== 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) | |=======================================================================================

List the SQL statements in V$sql_monitor:

SET linesize 300COLUMN sql_text FORMAT a100select sql_id, status, Sql_text from V$sql_monitor;

3. Turn on/Off real-time SQL monitoring
When the parameter statistic_level is set to typical, all, real-time SQL automatic monitoring is turned on automatically.
The control_management_pack_access is also set to diagnostic+tuning because SQL monitoring belongs to the tuning package component.

You can also use hint to turn on/off:

Select/+monitor+/from Dual;select/+no_monitor+/from dual;

Real-time SQL monitoring