Real-time Sql monitoring is a really exciting new feature that was introduced in oracle database 11gR1. This feature allows you to monitor the performance of sql statements as they are executing. Sql monitoring is automatically started if a sql statement is executing in parallel, or if the sql statement has spend more than 5 seconds of CPU or I/O time during a single execution. Details for this feature can be found in the 11g Performance Tuning guide.

You can run the report from sqlplus (And spool the html output to an o/s file) as follows

sqlplus / as sysdba

set long 1000000000
set pages 0

spool sqlmon.html

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>’255xfn4xrq51a’,session_id=>170,session_serial=>7,
type=>’HTML’) as report
from dual;

spool off
exit;

A sample output can be seen here.

Grid control 10.2.0.5,exposes this functionality from the management console (Until now you could access this from 11g database control or using the API DBMS_SQLTUNE.REPORT_SQL_MONITOR). You can access this by navigating to Targets -> Databases -> Choose your 11g database -> Performance. You can then click on “SQL monitoring” to monitor your sql statements that are currently executing.

This screen gets refreshed every 10 seconds, and it shows the SQL Id, The total execution time up till now, Degree of parallelism in use, The database time, Total I/O count, Start Time (If the sql has completed, the End Time) and the actual sql statement.

You can click on Status to further drill down into the sql statement.

The overview section shows the Duration, Database Time, IO count and buffer gets done by the sql (And the screen refreshes every 10 seconds, showing you the progress.)

The details section has 3 different views . Plan statistics that shows you the execution plan , and time spend, in different steps in the execution plan.

The parallel view, gives you further drill down into each parallel server and shows the Db time, Wait Activity%, IO count and Buffer gets by each parallel server.

The activity view , gives you the color coded activity breakdown within the session CPU, and other wait events.

You can also get to the real-time sql monitoring screen by identifying a sqlid and drilling down into the sql id. Then there is a tab named “sql monitoring” , which gives you this same information.

Really neat, dont you think ?