DBMS SQL MONITOR
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;