Real time sql monitoring

What it is

Real-Time SQL Monitoring (SQL Monitor) tracks expensive SQL statements, PL/SQL blocks, and composite operations while they're still running, not just after the fact. Unlike AWR or STATSPACK, which summarize completed workload, SQL Monitor shows what's active right now, including queued operations. It's built into the database itself, exposed through V$SQL_MONITOR, DBMS_SQL_MONITOR, and DBMS_SQLTUNE, and it's the same engine behind the monitoring screens in Cloud Control, SQL Developer, and Performance Hub.

When it fires

A statement gets picked up by SQL Monitor automatically if it meets at least one of these conditions:

• Runs in parallel
• Consumes 5 seconds or more of CPU or I/O time
• Was hinted explicitly with /*+ MONITOR */

If a statement won't cooperate with a hint, you can force monitoring for a specific SQL_ID at the system level:

ALTER SYSTEM SET EVENTS 'sql_monitor [sql:my_sql_id] force=true';

Step by step: pulling a report from the command line

Find the SQL_ID

SELECT DISTINCT s.sql_text, m.sql_id, m.cpu_time
FROM v$sql_monitor m
JOIN v$sql s ON s.sql_id = m.sql_id
ORDER BY m.cpu_time;

Generate the report

DBMS_SQL_MONITOR.REPORT_SQL_MONITOR accepts a type argument: HTML, TEXT, XML, or ACTIVE. The ACTIVE format is the most useful one day to day, since it produces an interactive report with clickable plan steps and hoverable timing bars, and it can be emailed to a colleague as a single file.

set trimspool on
set trim on
set pagesize 0
set linesize 32767
set long 5000000
set longchunksize 5000000

spool sqlmon_active.html

SELECT dbms_sql_monitor.report_sql_monitor(sql_id => 'my_sql_id', type => 'ACTIVE', report_level => 'ALL')
FROM dual;

spool off

Open it in a browser

The spooled .html file is self-contained. Double-click it or open it directly in any browser, no server or extra tooling required.

Historical reports (AWR)

V$SQL_MONITOR only holds recent statements. For anything that has already aged out, pull it from AWR snapshots instead.

SELECT report_id, snap_id
FROM dba_hist_reports
WHERE dbid = &dbid
AND component_name = 'sqlmonitor'
AND key1 = '&sql_id'
AND period_start_time BETWEEN &start_date AND &end_date;

Then feed the resulting report_id into DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL:

set linesize 32767
set trimspool on
set trim on
set long 1000000
set longchunksize 1000000
set pagesize 0

spool sql_mon_hist.html

SELECT dbms_auto_report.report_repository_detail(RID => &report_id, TYPE => 'html')
FROM dual;

spool off

Summary

• SQL Monitor watches expensive statements live, not after the fact
• Auto-triggers on parallel execution, 5+ seconds of CPU/IO, or the MONITOR hint
• ACTIVE reports are the most useful format for sharing and investigating
• Since 19c, regular users can pull their own reports without extra grants (SELECT_CATALOG_ROLE needed to see other users' statements)
• Historical statements that dropped out of V$SQL_MONITOR are still reachable through DBA_HIST_REPORTS and AWR

Based on Real-Time SQL Monitoring: a MUST for SQL Tuning by Ulrike Schwinn, Oracle. Full FAQ and further reading at the source link.