https://oracle-base.com/articles/11g/report_sql_monitor_text.txt
SQL HINT
/*+ MONITOR */
/*+ MONITOR */
The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.
SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname;
If you have long running statements you don't want to monitor, use the NO_MONITOR hint to prevent them being monitored.
-- 11gR2
SET LINESIZE 200
COLUMN sql_text FORMAT A80
SELECT sql_id, status, sql_text
FROM v$sql_monitor
WHERE username = 'SCOTT';
SQL_ID STATUS SQL_TEXT
------------- ------------------- --------------------------------------------------------------------------------
526mvccm5nfy4 DONE (ALL ROWS) SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname
Once the SQL_ID is identified, we can generate a report using the REPORT_SQL_MONITOR function.
*/
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 /host/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '526mvccm5nfy4',
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SCOTT (53:1261)
SQL ID : 526mvccm5nfy4
SQL Execution ID : 16777217
Execution Started : 03/19/2011 12:55:13
First Refresh Time : 03/19/2011 12:55:13
Last Refresh Time : 03/19/2011 12:55:13
Duration : .014344s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@oel5-11gR2-demo.localdomain (TNS V1-V3)
Fetch Calls : 4
Global Stats
======================================================================
| Elapsed | Cpu | IO | PL/SQL | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls | Gets |
======================================================================
| 0.01 | 0.01 | 0.00 | 0.00 | 0.00 | 4 | 41 |
======================================================================
SQL Plan Monitoring Details (Plan Hash Value=2970111170)
================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 3 | | | |
| 1 | SORT GROUP BY | | 4 | 7 | 1 | +0 | 1 | 3 | 2048 | | |
| 2 | MERGE JOIN | | 14 | 6 | 1 | +0 | 1 | 14 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 2 | 1 | +0 | 1 | 4 | | | |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | 1 | 1 | +0 | 1 | 4 | | | |
| 5 | SORT JOIN | | 14 | 4 | 1 | +0 | 4 | 14 | 2048 | | |
| 6 | TABLE ACCESS FULL | EMP | 14 | 3 | 1 | +0 | 1 | 14 | | | |
================================================================================================================================================
