DBMS_SQLTUNE.REPORT_SQL_MONITOR provide very convenient way to analyze the performance of Parallel Query.

Until 10g, analyzing the performance of parallel query isn’t easy work. For monitoring PQ, we usually use v$px_session, v$px_sesstat, v$session and so on. But, that views support only real time statistics. So, when we need to analyze the whole sql statstiics, we have to use 10046 trace. But, if we used 10046 trace, there will be many trace files on the dump destination. So, we could hardly analyze them. Recently my co-worker suggested dbms_sqltune package to analyze the PQ in 11g. That’s very amazing. We can analyze the PQ with GUI. The usage is very simple.

(1) Find SID or SQL_ID for running sessions

(2) We can make report using dbms_sqltune.report_sql_monitor.
‘A’ option is for ‘Active-X’ type output. ‘H’ option is for ‘HTML’ type output.

 
 set trim on
 set pagesize 0
 set linesize 1000
 set long 1000000
 set longchunksize 10000000
 set serveroutput off
 set heading off
 set feedback off
 set echo off
 SET TRIMSPOOL off
 set verify off
 set timing off
 column file_str new_value file_str;
 column rtype new_value rtype; 

 select decode(upper('&2'), 'T', 'TEXT', 'H', 'HTML', 'A', 'ACTIVE', 'TEXT') rtype from dual; 
 
 select to_char(sysdate, 'yyyymmdd')||'_'|| '&1' || decode(upper('&2'), 'T', '_T.out', 'H', '_H.html', 'A', '_A.html', '.out')file_str from dual;
 spool &file_str

 --for SQL
 --select dbms_sqltune.report_sql_monitor( sql_id => '&1', report_level=>'ALL', type=> '&rtype' ) from dual;
 
 --for SESSION
 select dbms_sqltune.report_sql_monitor( session_id => '&1', report_level=>'ALL', type=> '&rtype' ) from dual;

 spool end
 set heading on
 set feedback on
 set verify on
 set timing on 

Bellow Pictures are the output of dbms_sqltune.report_sql_monitor. It’s beautiful. The first picture is the output of HTML type. Others are the output of ACTIVE-X type.