SQL Monitoring Report

by Granville Bonyata on October 20, 2017

One of the most useful tuning tools available is Oracle’s SQL Monitor Report with a rich amount of detail about a single execution of a query.

There are a number of ways to get this. Here is a simple and direct way:
1) Add this hint to your query: /*+ MONITOR */ [Note: don’t include this in your production migration]
2) Set the session’s action by running: exec dbms_application_info.set_action(‘TEST1’) [–this makes it easier to find your query in step 5]
3) Run your query
4) Change the session’s action by running: exec dbms_application_info.set_action(‘DONE’)
5) Generate the SQL Monitor report:

SELECT dbms_sqltune.REPORT_SQL_MONITOR(sql_id => sm.sql_id, TYPE=> 'HTML', sql_exec_id => sm.sql_exec_id, inst_id => sm.inst_id)
  FROM gv$sql_monitor sm
WHERE username = USER
  AND action LIKE 'TEST1'
order by sm.last_refresh_time desc

6) Grab the monitor text (it will be an HTML CLOB), put it into a text file, save it as HTML and open it in your browser.

Some of the most useful details it has:

  • Rows: How many rows were returned (not just the optimizer estimate) for that line of the plan.
  • Execs: How many times was that line executed. A very high number here is something to investigate.
  • IO Requests: The I/O requests for each line of the plan. This fluctuates depending on how much data is cached.

Here is an example of how to set it up to see the activity when querying dba_objects:


EXEC dbms_application_info.set_action('TEST_Q')
 
select /*+ MONITOR */ count(distinct object_name)
from dba_objects
/
EXEC dbms_application_info.set_action('DONE')
 
SELECT dbms_sqltune.REPORT_SQL_MONITOR(sql_id => sm.sql_id, TYPE=> 'HTML', sql_exec_id => sm.sql_exec_id, inst_id => sm.inst_id)
  FROM gv$sql_monitor sm
WHERE username = USER
  AND action LIKE 'TEST_Q'
order by sm.last_refresh_time desc
/

Here is the documention for DBMS_SQLTUNE.

Previous post:

Next post: