Get a SQL Monitor report (running or completed long SQL)

Command

-- List monitored SQL (longer than 5 s, parallel, or /*+ MONITOR */):
SELECT sql_id, status, username, sql_exec_id,
       TO_CHAR(sql_exec_start,'YYYY-MM-DD HH24:MI:SS') AS exec_start,
       ROUND(elapsed_time/1e6,1) AS elapsed_s,
       ROUND(cpu_time/1e6,1)     AS cpu_s,
       buffer_gets, px_servers_allocated
FROM   v$sql_monitor
ORDER  BY sql_exec_start DESC
FETCH FIRST 20 ROWS ONLY;

-- TEXT report (actual rows, time and activity per plan line):
SET LONG 10000000 LONGCHUNKSIZE 10000000 LINESIZE 300 PAGESIZE 0 TRIMSPOOL ON
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id       => '<sql_id>',
         type         => 'TEXT',
         report_level => 'ALL') AS report
FROM   dual;

-- Shareable HTML (interactive in a browser):
SPOOL /tmp/sqlmon_<sql_id>.html
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id => '<sql_id>', type => 'ACTIVE', report_level => 'ALL')
FROM   dual;
SPOOL OFF

What it does

Real-Time SQL Monitoring shows, for every step of a long-running or parallel SQL's execution plan, the actual rows, elapsed time, memory/temp usage and ASH activity; you can watch progress while the SQL is still running.

When to use it

When asking whether a batch/report query is stuck and how much is left, for parallel query distribution (PX skew) analysis, and to find gaps between estimated (E-Rows) and actual rows.

Caution

Requires the Tuning Pack license (Diagnostics Pack is a prerequisite). By default only SQL consuming 5 s of CPU/I-O or running in parallel is monitored; use the /*+ MONITOR */ hint for others. Very large plans (_sqlmon_max_planlines, default 300) are not monitored. Entries stay in memory for a limited time; in 12c+ older reports are also available from DBA_HIST_REPORTS.