REAL TIME QUERY MONITORING

Oracle 11G Database REAL-TIME SQL MONITORING in 11G

A very good evening to all ... Here is an excerpt from one of oracle 11G database's great feature, its not an invention but its just an overview of the feature... Please read it..

The interesting thing here in this feature is that can answer anyone about current statistics of the query right when the query is actually running. I still get lot of questions from DBA's that can we see the access path, the disk_reads, the elapsed time right when the query is ALIVE ? Well SQL Monitoring is the answer for this one.

Oracle internally uses fine-grained SQL statistic that are tracked out-of-the-box with no performance penalty to production systems to monitor REAL TIME SQL. Here in 11G SQL monitoring is automatically started when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or I/O time in a single execution. This allows resource intensive SQL to be monitored as it is executing, as well as giving access to detailed information about queries once they are complete.

The SQL monitoring feature is enabled by default when the STATISTICS_LEVEL initialization parameter is either set to ALL or TYPICAL (the default value). Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING (the default value) because SQL monitoring is a feature of the Oracle Database Tuning Pack. SQL monitoring starts automatically for all long running queries.

Two statement-level hints are available to force or prevent a SQL statement from being monitored. To force SQL monitoring, use the MONITOR hint:

select /*+MONITOR*/ from dual;
Use NO_MONITOR hint to disable monitoring.

You wont believe the statistics for the statements like disk_reads, buffer_gets, elapsed_time, CPU_TIME are refreshed in near real-time as the statement executes, generally once every second. Once the execution ends, monitoring information is not deleted immediately, but is kept in the V$SQL_MONITOR view for at least one minute. The entry will eventually be deleted so its space can be reclaimed as new statements are monitored.

The interesting point which a DBA needs to know is that there is one entry in V$SQL_MONITOR and it is dedicated to a single execution of a SQL statement. If two executions of the same SQL statement are being monitored, each of these executions will have a separate entry in V$SQL_MONITOR.

To uniquely identify two executions of the same SQL statement, a composite key called an execution key is generated. This execution key is composed of three attributes, each corresponding to a column in V$SQL_MONITOR:

# SQL identifier to identify the SQL statement (SQL_ID)

# Start execution timestamp (SQL_EXEC_START)

# An internally generated identifier to ensure that this primary key is truly unique (SQL_EXEC_ID)

To get the report

variable karans_report CLOB;
BEGIN
  :karans_report :=DBMS_SQLTUNE.REPORT_SQL_MONITOR();
END;
/

print :karans_report

By default, a text report is generated for the last execution that was monitored if no parameters are specified as shown in the example, you can also specify HTML and XML for the report type.

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET FEEDBACK OFF

I just did a small demo on my test 11G R2 database running on redhat5 VM.

SPOOL /u01/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '1031bccm5nygh4',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

Well I always use ALL for report_level as im keen to look all relevant information. If you have ever played with DBMS_XPLAN package you should better know that TYPICAL,SERIAL,BASIC are also other options. In ALL option i also see PROJECTION, ALIAS and information of remote SQL if the operation is distributed. In SERIAL option you wont see parallel information even if the plan is executed in parallel.

Moreover you can use the application context SYS_CONTEXT('USERENV','SID') for the current session by giving it in the session_id argument which will cover the subset of queries for this session.

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. As with V$SQL_MONITOR, statistics exposed in V$SQL_PLAN_MONITOR are generally updated every second when the statement executes. These statistics are recycled on the same basis as V$SQL_MONITOR. V$SQL_PLAN_MONITOR can actually be joined with V$ACTIVE_SESSION_HISTORY to get the sample of each operation performed. Use PLAN_LINE_ID for the join key.

Thats all from my side.. If you get some new thing on this, do post it.. I shall be learning from you.. KARAN