How To Create A SQL Monitor Report

First of all, you want to ensure that you have the tuning and diagnostic pack for your database–if you do not have the tuning and diagnostic pack, Oracle does not authorize you to create SQL Monitor Reports.

Important Information About Creating SQL Monitor Reports

When you make a SQL monitor report, you will want to ensure that sufficient time has elapsed for the query’s bottlenecks to have been revealed. The most *ideal* situation is to have a SQL Monitor report for a query that is completed; but sometimes this is not available because a query might not ever finish. In these cases, you just want to ensure that you’ve allowed ample time for a query’s bottlenecks to be reveals.

An example: Let’s say you a simple plan–it only has a hash join that involves 2 large tables. One table takes 3 seconds to do a full table scan, the other table takes 10 seconds to do a full table scan. If you kick off the query and create a SQL Monitor report within the first 3 seconds of the query starting, it’s going to look like 100% of the time is being spent on the first table; when in reality, only about 3 out of 13 seconds of the actual query time is being spent on the first table (about 23% of the total query time).

Execution Plans That Are Too Long

If your execution plan is too long (over 300 lines), a SQL monitor report will not generate by default. This is super frustrating, because that’s when you need a SQL Monitor report the most!! You can coax the database into generating a report by doing a couple things (I’ve found that you actually have to do BOTH of these, not just one or the other).

#1: Issue an alter session set “_sqlmon_max_planlines” = 800; in the session that will be running the statement, before issuing the query.

#2: Use a /*+ monitor */ hint when running the query.

HTML Monitor Report (Recommended)

If you want an HTML version (which, I recommend as they contain a little more information than text reports) then you can use the following query:

select dbms_sqltune.report_sql_monitor(
  sql_id => '&v_sql_id.',
  session_id => '&v_session_id.',
  session_serial => '&v_serial.',
  type => 'HTML',
  report_level => 'ALL',
  inst_id => '&v_instance.'
)  report
from dual;

You don’t have to plug in all variables–you only have to plug in enough of the variables to allow Oracle to distinctly recognize the SQL/session combination (if, for example, there’s only one session on the whole database running the query, it’s sufficient to supply just the SQL_ID).

Here’s what an example HTML report looks like.

Text Monitor Report

If, for some reason, you’re having difficulties with an HTML report, you may want to make a text report:

select dbms_sqltune.report_sql_monitor(
  sql_id => '&v_sql_id.',
  session_id => '&v_session_id.',
  session_serial => '&v_serial.',
  type => 'TEXT',
  report_level => 'ALL',
  inst_id => '&v_instance.'
)  report
from dual;

Here’s an example of what a text SQL Monitor report looks like.