Performance Tuning : SQL Monitor Report

 Performance Tuning : SQL Monitor Report

The main advantage of this is that it gives a good view of how parallel SQL/DML performs across stages of the plan and parallel child (secondary) processes.

It can also give a good idea of the actual executions and row counts for each execution plan line even if "statistics_level" initialization parameter is not set to ALL ( or "_rowsource_execution_statistics" is not set to TRUE) at the time the SQL is executed.

The SQL Monitor report will only show the Active Period, CPU Activity and Wait Activity for execution plan lines if the "statistics_level" initialization parameter is set to ALL ( or "_rowsource_execution_statistics" is set to TRUE) at the time the SQL is executed.

This report uses data from several fixed views (e.g. GV$SQL_MONITOR, GV$SQL_PLAN_MONITOR, GV$SQL_PLAN, GV$ACTIVE_SESSION_HISTORY, GV$SESSION_LONGOPS, GV$SQL), so the database user must have the privilege to select from these (e.g. SELECT_CATALOG role).

It can be produced by running the dbms_sqltune.report_sql_monitor function with report_level = ALL.

The SQL ID will be required to be able to run the Display Cursor report.

Obtaining SQLID - 

If querying v$SQL, use the following query:

SELECT sql_id, hash_value, SUBSTR(sql_text,1, 80)
FROM v$sql  
WHERE sql_fulltext LIKE '%<part of SQL text>%';

The report can be produced by running the following. The HTML format is preferred:

set trimspool on
set trim on
set pages 0
set long 10000000
set longchunksize 10000000
set linesize 200
set termout off

spool sql_monitor_for_<sql_id>.htm 

variable my_rept CLOB;

BEGIN

   :my_rept := dbms_sqltune.report_sql_monitor(sql_id => '<sql_id>', report_level => 'ALL', type => 'HTML'); 

END;
/ 

print :my_rept

spool off; 

set termout on

SQL Monitor reports are also available from Oracle Enterprise Manager.
Here is an example of the output from a SQL Monitor report:





The Estimated Rows and Cost columns in the Monitoring Details section contain estimates, the remaining columns contain actual statistics.
Note that pointing to any of the colored bars on the HTML report will display more information about that statistic. e.g. For Wait Activity it displays the name of the wait events.
Real-Time SQL Monitoring requires the Oracle Tuning Pack. The Oracle Diagnostics Pack is a prerequisite for the Oracle Tuning Pack.
So Oracle Tuning Pack and Oracle Diagnostics must be licensed in order to use Real-Time SQL Monitoring. They can only be purchased with the Enterprise Edition of the Oracle Database.