DBMS SQL MONITOR

Introduction

By default only queries that last longer than 5 seconds or queries that run into parallel show up in the SQL Monitor. However, if you use the /*+ MONITOR */ hint, that query will also show up, even if it runs less than 5 seconds.

• You can use the first query to generate an execution plan. You will get more output, but the execution plan is of interest here, because it also contains all the actual rows and actual timings and costs. The best is to copy the relevant explain plan portion to a text file and open it with Notepad++
• The second query generates a complete report. It also includes the execution plan (however, I still also like the text based execution plan) as well as the plan tree.

1 Generate an execution plan with SQL Monitor

set linesize 250 pagesize 0 trims on tab off long 1000000
column report format a220

select 
   DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
        (sql_id=>'&sql_id',report_level=>'ALL') report
from dual;

2 Generate a SQL Monitoring report for a single SQL statement

The statement below will ask for the SQL_ID as input parameter,

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.html
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '&sql_id',
  type         => 'ACTIVE',
  report_level => 'ALL') AS report
FROM dual;