ID 1380492.1 Monitoring SQL statements with Real-Time SQL Monitoring
The Oracle 11g Database has a new interface to monitor long running SQL commands.The feature is called Real-Time SQL Monitoring. By default, SQL monitoring is automatically started when a SQL command runs parallel, or when it has consumed at least five seconds of the CPU or I/O time in a single execution.You can also use the MONITOR hint to switch on SQL Monitoring.
SQL monitoring requires the STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL',
and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to 'DIAGNOSTIC+TUNING'.
The Real-Time SQL Monitoring feature is licensed with Diagnostics and Tuning Pack.
After monitoring is initiated, an entries are added to the dynamic performance V$SQL_MONITOR and V$SQL_PLAN_MONITOR views. 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 command executes, generally once every second.
After the execution ends, monitoring information is not deleted immediately, but is kept in the V$SQL_MONITOR/V$SQL_PLAN_MONITOR view for at least one minute.The entry is eventually deleted so its space can be reclaimed as new commands are monitored.
You can find more details in the attached white paper.
The REPORT_SQL_MONITOR function is used to return a SQL monitoring report for a specific SQL statement. The SQL statement can be identified using a variety of parameters.
The function accepts some optional parameters, the most common ones are:.
Following is an example using REPORT_SQL_MONITOR:
SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT') AS report FROM dual;
To get an overview what SQL commands are in V$SQL_MONITOR you can use:
SET LINESIZE 300
or you can use the function REPORT_SQL_MONITOR_LIST.
For the above example this shows that there is a statement with the sql id 526mvccm5nfy4.
We can get now a report with the following command:
The active option is the best choice for parallel queries.
You can get this using the following:
spool monitor_sql.html
You will then need to open the spool file , remove the first lines before the first <html>.
Then you can open the file in a browser with an Internet connection to OTN.
The REPORT_SQL_MONITOR_LIST function was added in Oracle 11g Release 2 to generate a summary of all SQL stored in V$SQL_MONITOR.
SET LONG 1000000
|
From:http://myoracleguide.com/msa/ID%201380492.1%20Monitoring%20SQL%20statements%20with%20Real-Time%20SQL%20Monitoring.htm