Oracle's V$sql_monitor monitoring statistics for running SQL statements

A new dynamic performance view, V$sql_monitor, is introduced in 11g to display SQL statement information for Oracle monitoring. SQL monitoring automatically starts for SQL statements that execute concurrently or consume more than 5 seconds of CPU time or I/O time, while generating a record in the V$sql_monitor view. When the SQL statement is executing, the statistics in the V$sql_monitor view are refreshed in real time, at a frequency of 1 times per second. Once the SQL statement is executed, the monitoring information will not be deleted immediately, and Oracle will ensure that the records are kept for one minute (controlled by the parameter _sqlmon_recycle_time, which is 60s by default), and eventually the records will be deleted and reused. This new SQL performance monitoring feature is only available in control_management_pack_access for diagnostic+tuning and Statistics_level all| Typical is enabled.

The V$sql_monitor view contains the currently running SQL statement, as well as the most recently run SQL statement.

The following conditions are required when using SQL statements that are monitored in the v$sql_monitor view:
1) automatic monitoring of any parallel statements
2) If an SQL statement consumes more than 5 seconds of CPU or I/O time, it will automatically monitor
3) Monitor any SQL statements that enable monitoring at the statement level (using Monitor or No_monitor)

Tip: Combined with v$sql_monitor view and V$sql_plan_monitor view, you can further query the SQL execution plan, among other information. You can get more information about SQL by federating some other views, such as V$active_session_history, V$session, V$session_longops, V$sql, V$sql_plan, and so on.

Note: For SQL monitoring, the initialization parameter statistics_level must be set to typical or all,control_management_pack_access must be set to diagnostic+tuning.

Sql> Show Parameter Statistics_leve
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Statistics_level string Typical

Sql> Show Parameter Control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Control_management_pack_access string diagnostic+tuning

1, such as the view of the database consumes CPU resources in the top 10-bit query:

SELECT * FROM (
Select Sql_id,username,to_char (Sql_exec_start, ' yyyy-mm-dd hh24:mi:ss ') Sql_exec_start,
Sql_exec_id,sum (buffer_gets) Buffer_gets,
SUM (disk_reads) disk_reads,round (sum (cpu_time/1000000), 1) cpu_secs
From V$sql_monitor
where username not in (' SYS ', ' SYSTEM ')
GROUP BY sql_id,username,sql_exec_start,sql_exec_id
ORDER BY 7 Desc)
where rownum<=10;

sql_id USERNAME sql_exec_start sql_exec_id buffer_gets disk_reads cpu_secs
------------- ------------------------------ ------------------- ----------- ----------- ---------- ----------
8P8ANN3C68VXC SCOTT 2016-11-24 09:50:20 16777216 0 0 37.1

2. Monitor the specified SQL statement:
2.1. Hint prompts to monitor the specified SQL statement
Select/*+ Monitor */e.empno,e.ename,e.sal,d.dname from scott.emp e,scott.dept D where E.deptno=d.deptno;

2.2. View the resource information consumed by the monitored SQL statements in the V$sql_monitor view:

Select Sql_id,username,to_char (Sql_exec_start, ' yyyy-mm-dd hh24:mi:ss ') Sql_exec_start,
Sql_exec_id,sum (buffer_gets) Buffer_gets,
SUM (disk_reads) disk_reads,round (sum (cpu_time/1000000), 1) cpu_secs
From V$sql_monitor
where Sql_text like '%monitor% '
Group BY SQL_ID,USERNAME,SQL_EXEC_START,SQL_EXEC_ID;

2.3. You can then use the Dbms_sqltune package to view the monitoring information generated by the SQL statement (such as execution plans, etc.):
Sql>
Set Long 10000000
Set Longchunksize 10000000
Set Linesize 200
Select Dbms_sqltune.report_sql_monitor from dual;

Oracle's V$sql_monitor monitoring statistics for running SQL statements