Creating a SQL Monitor Report plays an important role in database optimization as it helps the user observe other occurrences during the execution of long-running statements.
In this post, we’ll discuss how to create one such report that may help you during query performance tuning.
SQL Monitor Report: Bringing DBAs One Step Closer to Database Query Optimization
To begin with, you need to make sure your database has the tuning and diagnostic pack. Otherwise, Oracle will not authorize the creation of SQL Monitor Reports.
Also, such reports can be made after an adequate amount of time has passed. The wait is to allow query bottlenecks to reveal themselves. This is typically done for seemingly endless queries that run for long periods of time. However, in general, creating SQL monitor reports is recommended for completed queries.
Let’s look at an example: A DBA has a simple plan with a hash join involving two big tables. Suppose one of these tables takes two seconds to undergo a complete table scan, whereas the second one takes nine seconds.
Although only around two seconds out of a total of eleven seconds are sent on the first table, it will appear as though a hundred percent of the query time is being spent on it if you create a SQL Monitor report during the first two seconds.
Creating Reports for Excessively Long Execution Plans
Really long execution plans – those that exceed three hundred lines – don’t have a SQL monitor report generated for them by default. This gets cumbersome because long execution plans are where these reports are needed the most!
In such cases, there are two things you can do to make the database generate a report. These are –
- Prior to issuing the query in question, generate the following in the session operating the query:
alter session set “_sqlmon_max_planlines” = 800;
2. Apply the following hint while executing the query:
/* + monitor */
How to Create an HTML Version of the Monitor Report
The HTML version of a SQL Monitor report offers some more details as compared to its text report. This is why it is often recommended by database professionals, with the help of 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_num => ‘&v_instance.’ )report
from dual;
Not every variable needs to be plugged in – you just require variables sufficient to enable Oracle to recognize the particular SQL\session combination. And if there is only a single session executing the statement on the entire database, only the sql_id is enough.
Creating a Text Monitor Report Instead
In case you’d rather make a text report – whether if it’s due to some problems with an HTML report, or simply preference – here’s how to do it –
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_num => ‘&v_instance.’ )report
from dual;