Oracle SQL Monitor: Real-Time Execution Diagnostics for Long-Running Queries

Oracle SQL Monitor is the most powerful single-query diagnostic tool in the Oracle performance toolkit. Unlike DBMS_XPLAN.DISPLAY_CURSOR, which gives you a static execution plan, SQL Monitor captures real-time runtime statistics at every step of the execution plan — rows processed, memory used, temporary I/O, CPU time, and elapsed time per operation. For parallel queries, it shows the workload distribution across parallel server processes.

When SQL Monitor Activates Automatically

SQL Monitor begins monitoring a SQL statement automatically when either:

• The statement runs for more than 5 seconds of CPU or elapsed time (cumulative), or
• The statement uses parallel execution.

No configuration is required. Monitored statements appear in V$SQL_MONITOR and persist in V$SQL_PLAN_MONITOR for the duration of the SQL_MONITOR retention (controlled by _sqlmon_recycle_time).

-- List recently monitored statements
SELECT sql_id,
       sql_text,
       status,
       elapsed_time / 1000000  AS elapsed_secs,
       cpu_time     / 1000000  AS cpu_secs,
       buffer_gets,
       disk_reads,
       fetches,
       username,
       program
FROM v$sql_monitor
WHERE last_refresh_time >= SYSDATE - 1
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Generating the SQL Monitor Active Report

The richest output is the HTML Active Report — a self-contained interactive web page showing the plan tree, per-operation statistics, and timeline:

-- Generate HTML Active Report (most recent execution)
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id     => '&sql_id',
  type       => 'ACTIVE',   -- 'TEXT', 'HTML', or 'ACTIVE'
  report_level => 'ALL'
) AS report
FROM DUAL;

Save the output as a .html file and open it in a browser. The interactive report includes:

• Gantt-style execution timeline showing when each operation started and finished.
• Per-operation row counts, actual vs. estimated.
• Per-operation I/O, memory, and CPU.
• Parallel distribution map (for parallel queries).

Text Report (for quick terminal analysis)

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id       => '&sql_id',
  type         => 'TEXT',
  report_level => 'ALL'
) AS report
FROM DUAL;

Example text output:

SQL Monitoring Report

SQL Text
--------
SELECT /* analytics */ ...

Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  SID 145 / Serial# 12984
 SQL ID              :  05tk7cd18wbwu
 SQL Execution ID    :  16777218
 Execution Started   :  03/01/2026 09:14:22
 First Refresh Time  :  03/01/2026 09:14:23
 Last Refresh Time   :  03/01/2026 09:17:45
 Duration            :  203 sec
 Module/Action       :  JDBC Thin Client / analytics_batch

Global Stats
============
| Elapsed | Cpu  | IO    | Fetch  | Buffer | Read  | Read  |
| Time(s) | Time | Waits | Calls  | Gets   | Reqs  | Bytes |
|   203   |  12  | 191   |   1    | 1,240  | 8,902 | 71GB  |

The dramatic gap between CPU time (12 seconds) and elapsed time (203 seconds) with 71 GB of reads immediately points to an I/O-bound full table scan.

Interpreting Per-Operation Statistics

Each plan operation in SQL Monitor shows:

Key Diagnostic Patterns

Cardinality explosion: E-rows 100, A-rows 10 million. The optimizer did not expect this many rows — downstream operations (hash joins, sorts) were sized incorrectly, causing memory spills.

-- Find cardinality misestimates from SQL Monitor
SELECT plan_line_id,
       plan_operation || ' ' || plan_options AS operation,
       plan_object_name,
       output_rows AS actual_rows,
       plan_cardinality AS estimated_rows,
       CASE WHEN plan_cardinality > 0
            THEN ROUND(output_rows / plan_cardinality, 1)
            ELSE NULL END AS ratio
FROM v$sql_plan_monitor
WHERE sql_id = '&sql_id'
  AND sql_exec_id = (SELECT MAX(sql_exec_id) FROM v$sql_monitor WHERE sql_id = '&sql_id')
ORDER BY ABS(output_rows - NVL(plan_cardinality,0)) DESC
FETCH FIRST 10 ROWS ONLY;

Memory spill to temp: Temp Space > 0 for a HASH JOIN or SORT ORDER BY operation means the workarea spilled to disk. Fix: increase PGA_AGGREGATE_LIMIT, or investigate why cardinality explosion caused an oversized hash build.

Step duration outlier: Most operations complete in milliseconds but one step takes 3 minutes. This is the bottleneck step — focus analysis there.

SQL Monitor for Parallel Query Diagnosis

SQL Monitor’s parallel breakdown is unique — no other tool provides this visibility.

-- Get parallel slave activity for a specific execution
SELECT plan_line_id,
       process_name,
       output_rows,
       read_bytes / 1024 / 1024 AS read_mb,
       cpu_time   / 1000000     AS cpu_secs,
       elapsed_time / 1000000   AS elapsed_secs
FROM v$sql_plan_monitor
WHERE sql_id    = '&sql_id'
  AND sql_exec_id = '&sql_exec_id'
  AND process_name LIKE 'P%'   -- parallel slaves
ORDER BY plan_line_id, process_name;

Diagnosing Parallel Skew

Parallel skew occurs when one parallel slave processes far more data than others, causing the overall query to wait for the slowest slave. In the SQL Monitor Active Report, this appears as an uneven distribution bar in the parallel section.

Common causes:

• Hash distribution of partitioned table by a low-cardinality column (all rows with one value go to one slave).
• Data is heavily skewed (one partition has 80% of the rows; that slave finishes last).

Fix: change the parallel distribution method with a hint or redesign the partitioning key.

-- Force round-robin distribution to avoid skew (at cost of additional data movement)
SELECT /*+ PQ_DISTRIBUTE(t NONE RANDOM) */ *
FROM large_table t
WHERE ...

Forcing SQL Monitor for Short Queries

For short queries that do not automatically trigger SQL Monitor, force it with a hint:

SELECT /*+ MONITOR */ customer_id, SUM(amount)
FROM orders
WHERE order_date >= TRUNC(SYSDATE) - 7
GROUP BY customer_id;

This is useful when reproducing a query in a test environment where the execution time is shorter than the 5-second threshold.

Retaining SQL Monitor History

By default, SQL Monitor reports are retained in memory and cycle out. For post-incident analysis, write the report to a table:

-- Create retention table
CREATE TABLE sql_monitor_archive (
  captured_at DATE DEFAULT SYSDATE,
  sql_id      VARCHAR2(13),
  report      CLOB
);

-- Archive a specific execution
INSERT INTO sql_monitor_archive (sql_id, report)
SELECT '&sql_id',
       DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id       => '&sql_id',
         type         => 'ACTIVE',
         report_level => 'ALL'
       )
FROM DUAL;

COMMIT;

Automate this via a job that archives SQL Monitor reports for any execution exceeding a threshold:

-- Archive all long-running SQL from the last 30 minutes
INSERT INTO sql_monitor_archive (sql_id, report)
SELECT sql_id,
       DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => sql_id, type => 'ACTIVE')
FROM v$sql_monitor
WHERE elapsed_time > 60000000   -- > 60 seconds
  AND last_refresh_time >= SYSDATE - 30/1440;

COMMIT;

When to Use SQL Monitor vs. DBMS_XPLAN

SQL Monitor and DBMS_XPLAN are complementary. For any query consuming > 60 seconds, always start with SQL Monitor — the per-operation runtime statistics immediately narrow the search space from the entire plan to the one or two operations responsible for the majority of elapsed time.