Oracle Real-Time SQL Monitoring

Oracle real-time SQL Monitoring is one of the very exciting features i have used recently. Its a very effective way to identify problems related to performance in long running queries especillay with the ones with complex execution Plans. It shows Stats at every step in the execution plan with key performance metrics including elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times. It allows us to have a deeper look inside the query plans and pin point the exact pain areas and then develop a startegy for SQL tuning.

The good part is the SQL Monitoring is also available with Oracle Enterprise Manager. Below is the Snapshot from the Enterprise manager 12c

It can also be manually Extracted using the following script.
In the below example I am going to extract a SQL monitoring report for a very complex SQL. The SQL monitoring report extracted in HTML format clearly identify the steps where the most of the time id spent on

SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFF
SPOOL report_sql_monitor.htmSELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '2fj0jatxnstyu', -- pass the SQL id as the argumenttype => 'HTML',report_level => 'ALL') AS reportFROM dual;SPOOL OFF


The step # 32 shows 96% of the CPU time is spent on it.