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