11g SQL Monitoring: Real Time sql monitoring

All,

I have got a chance to speak about this nice cool feature in an other practical demo session to my colleagues, but few questions on the same remind to post and read something on this.

Typically as usual my post flow, What, How, How to, Important note?

What is SQL Monitoring?

If a query is a “long” query, if it uses more than 5 seconds of CPU of I/O Wait or if it’s a parallel query (from the documentation), the plan execution statistics are kept by the engine and you can follow the query execution in Real Time.

How does it Work?

Once monitoring is initiated, an entry is added to the dynamic performance view 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)

How to make use of it? Different ways?

Default on, when statistics_level set to ALL or Typical

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.

Enable Manually:-

select /*+MONITOR*/ from dual;

Disable:-

select /*+NO_MONITOR*/ from dual;

Criteria to collect the real time statistics of a running query through SQL Monitor?

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.

What is the collection frequency? 

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. For later purposes these will be stored in ASH tables.

What are the dynamic views related to it?

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

How to view the report?

Real-Time SQL Monitoring reports are available from three locations:

• Enterprise Manager - Click the "Performance" tab, then the "SQL Monitoring" link at the bottom-right of the page to display the "Monitored SQL Executions" screen. Click the SQL_ID of interest to display the SQL monitoring report.
• SQL Developer - Available from the "Tools > Monitor SQL" menu.
• DBMS_SQLTUNE package.

Reports can generate in HTML,Text,XML, Flash (ACTIVE XML)

set long 10000000

set longchunksize 10000000

set linesize 200

select dbms_sqltune.report_sql_monitor from dual;

Important Note:- Additional Diagnostic and Change management access pack license is required to use.

Source:-

http://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm

-Thanks

Geek DBA