Use SQL Monitor to monitor SQL parallel queries in Oracle

SQL Monitor is use to monitor and diagnose statements running with parallel execution plan.
SQL Monitoring requires:
A Diagnostics and Tuning Pack License
STATISTICS_LEVEL parameter to be set to ‘TYPICAL’ or ‘ALL’
CONTROL_MANAGEMENT_PACK_ACCESS parameter set to ‘DIAGNOSTIC+TUNING’.

Package DBMS_SQLTUNE.report_sql_monitor parameter explain:

SQL_ID – SQL_ID of the query. When NULL the last monitored statement is targeted.
REPORT_LEVEL – amount of information displayed in the report as ‘NONE’, ‘BASIC’, ‘TYPICAL'(Default) or ‘ALL’.
TYPE – Output display of the report (‘TEXT’, ‘HTML’, ‘XML’).
SESSION_ID – Queries belong to the specified SID. Use SYS_CONTEXT(‘USERENV’,’SID’) for the current session.

Generate the Report by executing the Query in Session level:
Note: Monitor hint use for monitor the SQL queries which is going to execute.

  1. Open the Session and execute the statement with HINT /+MONITOR/
SELECT /*+MONITOR*/ ...query;
Example:
SELECT /*+MONITOR*/ * from order;

2. After executing the query run the following command to get the output:

-- TEXT Format: 
SET LINES 2000
SET PAGES 0
SET LONG 100000
SET LONGC 2000
spool E:\monitor.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'TEXT',report_level=>'ALL') AS report FROM dual;
spool off

OR

---HTML Format: 
SET PAGES 0
SET LONG 100000
SET LONGC 2000
spool E:\monitor.html
SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'ACTIVE',report_level=>'ALL') AS report FROM dual;
spool off

Generate Report with SQL ID:

  1. Check the SQL ID of monitored SQL queries with v$sql_monitor view:
SET LINESIZE 300
COLUMN sql_text FORMAT A100
SELECT sql_id, status, sql_text FROM v$sql_monitor;

2. Generate report by using SQL_ID:

-- Text Format:
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT')
AS report FROM dual;

OR 
-- Html format:
SET LONG 1000000
SET FEEDBACK OFF
spool E:\monitor.html
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'1bqgr56guujk68',type=> 'HTML')
AS report FROM dual;
spool off

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.