Shulou(Shulou.com)05/31 Report--

This article will explain in detail the statistical information about how v$sql_monitor monitors running SQL statements in oracle. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

A new dynamic performance view, V$SQL_MONITOR, was introduced in 11g to display SQL statement information monitored by Oracle. SQL monitoring automatically launches SQL statements that execute in parallel or take more than 5 seconds of cpu time or I time, and produce 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, with a frequency of once per second. After the execution of the SQL statement, the monitoring information will not be deleted immediately, and Oracle will ensure that the relevant records will be saved for one minute (controlled by the parameter _ sqlmon_recycle_time, default is 60s), and eventually these records will be deleted and reused. This new SQL performance monitoring feature is enabled only when CONTROL_MANAGEMENT_PACK_ACCESS is DIAGNOSTIC+TUNING and STATISTICS_LEVEL is ALL | TYPICAL.

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

The following conditions must be met when using the SQL statements monitored in the v$sql_monitor view:

1) automatically monitor any parallel statements

2) if a SQL statement consumes more than 5 seconds of CPU or I time, it will be automatically monitored.

3) monitor any SQL statements that enable monitoring at the statement level (using monitor or no_monitor)

Tip: combining v$sql_monitor view with v$sql_plan_monitor view can further query the execution plan of SQL and other information. More information about SQL can be obtained by combining 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. For example, check the top 10 queries in the database that consume CPU resources:

Select * from (

Select sql_id,username,to_char (sql_exec_start,'yyyy-mm-dd hh34: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

Set long 10000000

Set longchunksize 10000000

Set linesize 200

Select dbms_sqltune.report_sql_monitor from dual

This is the end of this article on "Statistics on how v$sql_monitor monitors running SQL statements in oracle". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.