Watch Long-Running SQL in Real Time with V$SQL_MONITOR

calendar Sep 21, 2026 / · 12 min read · Oracle DBA SQL Monitoring Real-Time Monitoring Parallel SQL Execution Execution Plans Oracle Performance V$ Dynamic Views v$sql_monitor v$sql_plan_monitor v$active_session_history dbms_sql_monitor dba_hist_reports  ·
Share on: linkedin copy

Watch Long-Running SQL in Real Time with V$SQL_MONITOR

Purpose

An AWR report describes what happened over the last hour. A SQL trace file describes what happened after the statement has already finished writing it. V$SQL_MONITOR answers a different question: what is running right now, and how far along is it. Statistics in the view are refreshed roughly once a second while the statement executes, which makes it the tool for the moment a DBA is staring at a session that has been running for ten minutes and needs to know whether it is almost done or stuck.

Oracle does not monitor every statement — that would be far too much overhead for far too little value. A simple database operation (a single SQL statement or PL/SQL call) is monitored automatically when it runs in parallel, or when it has consumed at least 5 seconds of combined CPU and I/O time in a single execution. Tracking can also be forced regardless of those conditions with the /*+ MONITOR */ hint. A composite database operation — a named span of activity across several statements in one session — is monitored the same way, except tracking starts and stops explicitly through the DBMS_SQL_MONITOR package rather than being inferred from one statement's runtime. Monitoring data does not disappear the instant a statement finishes: Oracle keeps each entry in V$SQL_MONITOR for at least one minute after execution ends, and only removes it later to make room as new statements get monitored. From Oracle Database 12c, that same information is also persisted to the data dictionary, on the same retention cycle as the Automatic Workload Repository.

This post covers reading live progress straight from the view, forcing monitoring onto a statement that would not otherwise qualify, generating a formatted report with DBMS_SQL_MONITOR, pulling plan-line-level progress from V$SQL_PLAN_MONITOR, tracking a multi-statement operation as one unit, and finding a report after the in-memory copy has aged out.

Code

 1-- Query 1: everything currently monitored and still executing
 2SELECT sid, status, sql_id, sql_exec_start,
 3       ROUND(elapsed_time / 1000000, 1) AS elapsed_sec,
 4       px_servers_requested, px_servers_allocated, username, module
 5FROM   v$sql_monitor
 6WHERE  status = 'EXECUTING'
 7ORDER  BY sql_exec_start;
 8
 9-- Query 2: force monitoring on a statement that would not otherwise qualify
10SELECT /*+ MONITOR */ ename, sal, deptno
11FROM   emp
12WHERE  deptno = 10;
13
14-- Query 3: force monitoring for a statement you cannot edit (third-party SQL),
15-- by SQL_ID, at the system level
16ALTER SYSTEM SET EVENTS
17  'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true';
18
19-- Query 4: text report for the last statement this session had monitored,
20-- no arguments needed
21SET LONG 1000000
22SET LONGCHUNKSIZE 1000000
23SET LINESIZE 200
24SET PAGESIZE 0
25SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR() FROM dual;
26
27-- Query 5: a full-detail report for one specific SQL_ID
28SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
29         sql_id       => 'cvn84bcx7xgp3',
30         report_level => 'ALL')
31FROM   dual;
32
33-- Query 6: plan-line progress for that same statement, right now
34SELECT m.plan_line_id, m.plan_operation, m.plan_options, m.plan_object_name,
35       m.starts, m.output_rows, m.workarea_mem, m.workarea_tempseg
36FROM   v$sql_plan_monitor m
37WHERE  m.sql_id = 'cvn84bcx7xgp3'
38AND    m.sql_exec_start = (SELECT MAX(sql_exec_start)
39                            FROM   v$sql_monitor
40                            WHERE  sql_id = 'cvn84bcx7xgp3')
41ORDER  BY m.plan_line_id;
42
43-- Query 7: approximate per-step activity by sampling V$ACTIVE_SESSION_HISTORY
44SELECT m.plan_line_id, m.plan_operation, m.plan_object_name,
45       COUNT(*) AS ash_samples
46FROM   v$sql_plan_monitor m, v$active_session_history h
47WHERE  h.sql_id           = m.sql_id
48AND    h.sql_exec_start   = m.sql_exec_start
49AND    h.sql_exec_id      = m.sql_exec_id
50AND    h.sql_plan_line_id = m.plan_line_id
51AND    m.sql_id = 'cvn84bcx7xgp3'
52GROUP  BY m.plan_line_id, m.plan_operation, m.plan_object_name
53ORDER  BY ash_samples DESC;
54
55-- Query 8: track a batch job that spans several statements as one operation
56DECLARE
57  l_exec_id NUMBER;
58BEGIN
59  l_exec_id := DBMS_SQL_MONITOR.BEGIN_OPERATION(
60                 dbop_name       => 'MONTHLY_CLOSE_LOAD',
61                 forced_tracking => DBMS_SQL_MONITOR.FORCE_TRACKING);
62
63  -- the statements that make up the operation run here
64
65  DBMS_SQL_MONITOR.END_OPERATION(
66    dbop_name => 'MONTHLY_CLOSE_LOAD',
67    dbop_eid  => l_exec_id);
68END;
69/
70
71-- Query 9: find a persisted report once the live entry has aged out of memory
72SELECT report_id
73FROM   dba_hist_reports
74WHERE  component_name = 'sqlmonitor'
75AND    report_name    = 'main'
76AND    key1            = 'cvn84bcx7xgp3'
77AND    period_start_time BETWEEN
78         TO_DATE('27/07/2026 11:00:00', 'DD/MM/YYYY HH24:MI:SS')
79         AND TO_DATE('27/07/2026 11:15:00', 'DD/MM/YYYY HH24:MI:SS');

Code Breakdown

Query 1: the live poll

This is the query to run first — no SQL_ID, no report call, just the current state of everything Oracle is watching. STATUS = 'EXECUTING' filters to statements still running, PX_SERVERS_REQUESTED and PX_SERVERS_ALLOCATED show whether a parallel statement actually got the degree of parallelism it asked for, and ELAPSED_TIME (stored in microseconds) is divided down to seconds for a readable number. A mismatch between requested and allocated parallel servers is the first sign a parallel job is running with fewer resources than it planned for.

Query 2: the MONITOR hint

Adding /*+ MONITOR */ forces Oracle to track a statement even when it would not otherwise meet the parallel or 5-second threshold. This is the tool for watching a specific statement during development or testing, before it has a track record of running long in production.

Query 3: forcing monitoring without touching the SQL

Not every long-running statement can be edited to add a hint — it may come from a packaged application. Setting the sql_monitor event at the system level, with a pipe-separated list of SQL_ID values, forces monitoring for those statements without changing a single character of their text.

Query 4: the zero-argument report

DBMS_SQL_MONITOR.REPORT_SQL_MONITOR() with no parameters targets the last database operation monitored by Oracle — the fastest path from "a statement just ran" to a formatted report, with no lookup step in between. SET LONG and SET LONGCHUNKSIZE need to be large enough for the returned CLOB before it prints cleanly in a SQL*Plus session.

Query 5: a specific, detailed report

Passing sql_id targets one statement by name instead of "whatever ran last," and report_level => 'ALL' requests the most complete report the function can build — everything short of the plan-line histogram detail that 'TYPICAL' (the function's default) omits. dbop_name and dbop_exec_id are the equivalent parameters for a composite operation instead of a single statement.

Query 6: plan-line progress

V$SQL_PLAN_MONITOR has one row per operation in the executing plan, joined back to V$SQL_MONITOR on the shared execution key. STARTS counts how many times that operation has run so far — relevant for a nested-loop join's inner side, which can execute repeatedly — and OUTPUT_ROWS is the cumulative row count that operation has produced since the execution began. WORKAREA_TEMPSEG reports whether a sort or hash-join step has spilled to temp space, which is a direct read on whether PGA_AGGREGATE_TARGET is undersized for that operation.

Query 7: estimating per-step time

V$SQL_PLAN_MONITOR deliberately does not record elapsed time, CPU time, or I/O time for each individual plan operation — collecting that at the per-step level would add overhead to every monitored execution. Instead, joining it to V$ACTIVE_SESSION_HISTORY on SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, and the plan-line identifier produces a sample of activity for each step, and the statement-level time totals already sitting in V$SQL_MONITOR can be broken down in proportion to how many samples landed against each operation. A step with three times the samples of its neighbors is the one actually consuming the time.

Query 8: composite operations

A composite operation groups several statements under one name so their combined statistics can be reviewed as a single unit rather than piece by piece. BEGIN_OPERATION starts it and returns an execution ID; passing DBMS_SQL_MONITOR.FORCE_TRACKING guarantees the operation gets tracked regardless of how long it ultimately takes, rather than only once it crosses the same 5-second threshold a simple statement would need. END_OPERATION closes it out — and if the named operation does not exist (already ended, or never started), the call simply has no effect rather than raising an error.

Query 9: finding a persisted report

DBA_HIST_REPORTS is where a SQL Monitor report lands once it is no longer sitting in the live in-memory view. COMPONENT_NAME = 'sqlmonitor' and REPORT_NAME = 'main' scope the search to SQL Monitor specifically, KEY1 holds the SQL_ID, and PERIOD_START_TIME narrows the search window. The returned REPORT_ID is what a follow-up call (outside DBMS_SQL_MONITOR itself) uses to pull the archived report text.

Key Points

  • Two independent triggers, not one. A simple statement is monitored automatically if it runs parallel OR consumes at least 5 seconds of combined CPU and I/O time in a single execution — either condition alone is enough.
  • STATUS on V$SQL_MONITOR carries six values, not a plain running/finished flag: QUEUED, EXECUTING, DONE (ERROR), DONE (FIRST N ROWS), DONE (ALL ROWS), and DONE for a finished parallel execution.
  • The execution key is three columns together, not one: SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID uniquely identify a single execution, which matters because the same SQL_ID gets a fresh row every time it runs.
  • A parallel statement produces multiple rows in V$SQL_MONITOR — one for the coordinator and one per parallel server — all sharing the same execution key so they can be aggregated back into one picture.
  • V$SQL_PLAN_MONITOR skips timing by design. Elapsed time, CPU time, and I/O time are not recorded per plan operation to keep monitoring overhead low; estimating them requires the join to V$ACTIVE_SESSION_HISTORY.
  • Resource Manager actions show up directly on the monitored row. RM_LAST_ACTION reports whether Resource Manager cancelled, killed, logged, or switched the consumer group of the statement being watched.
  • Retention is two-tiered. The live view keeps an entry for at least a minute after it finishes and ages it out as needed; from 12c, the same report is additionally persisted to DBA_HIST_REPORTS on the AWR retention cycle (8 days by default).

Insights and Best Practices

Treat the live view as the first stop, not a dashboard build

Query 1's shape — STATUS = 'EXECUTING' sorted by start time — is nearly the whole toolkit for "what is running right now and since when." It needs no report call, no SQL_ID lookup, and returns in the time it takes to run a SELECT. Reach for DBMS_SQL_MONITOR.REPORT_SQL_MONITOR once that first look has already pointed at something worth a closer read.

Check the license before building on this

V$SQL_MONITOR-based reporting through Enterprise Manager and SQL Developer's Real-Time SQL Monitor screen is part of the Oracle Tuning Pack. The dynamic views themselves and the DBMS_SQL_MONITOR package are how this post queries the data directly, but a site standardizing on the graphical report should confirm Tuning Pack licensing covers the environment first.

Leave the underscore parameters alone unless a specific plan forces the issue

Three internal parameters control the mechanics behind the defaults described above: the maximum number of statements monitored at once, the maximum number of plan lines a monitored plan can have before it is skipped, and the 5-second threshold itself. All three exist, and all three can be changed — but changing any of them increases the memory V$SQL_MONITOR consumes in the shared pool and can cause monitored data to age out faster. If one genuinely needs adjusting, doing it at the session level rather than system-wide keeps the change scoped to the investigation that needed it.

Use composite operations for anything that is really one job

A nightly load that runs as fifteen separate INSERT statements looks, to V$SQL_MONITOR, like fifteen unrelated rows unless each one happens to individually cross the 5-second threshold. Wrapping the whole sequence in a named BEGIN_OPERATION / END_OPERATION pair turns it back into the single unit it actually is for reporting purposes, and FORCE_TRACKING makes sure it gets tracked even on a night it runs faster than usual.

Don't wait for the one-minute window to close

Because a finished statement's entry is only guaranteed to survive in the live view for about a minute before it becomes eligible to be aged out, a report worth keeping should be pulled and saved close to when the statement finishes — not investigated an hour later on the assumption it will still be sitting there. On 12c and later, DBA_HIST_REPORTS is the fallback once that window has closed; on releases before that, an aged-out report is genuinely gone.

When to Use This

  • Checking whether a session that has been running for several minutes is close to finishing or has stalled, without waiting for an AWR snapshot.
  • Confirming a parallel query actually got the degree of parallelism it requested.
  • Investigating which specific operation in a long plan is consuming the time, once a statement is known to be slow overall.
  • Forcing visibility into a third-party application's SQL that cannot be edited to add a hint.
  • Tracking a multi-statement batch job as a single named unit instead of piecing its statements back together after the fact.
  • Reviewing a report after the statement finished, once it is no longer sitting live in memory.

Troubleshooting Common Issues

A statement that clearly ran long never shows up in V$SQL_MONITOR. Confirm it actually crossed a trigger condition — parallel execution, or 5 seconds of combined CPU and I/O in one execution — rather than 5 seconds of wall-clock time spent mostly waiting on something else outside CPU/I/O. If it should have qualified, check whether the default cap on the number of statements Oracle will monitor simultaneously, or the default 300-line limit on plan size, is the reason it was skipped.

REPORT_SQL_MONITOR() with no arguments returns the wrong statement. The zero-argument call targets the last database operation this session had monitored — if another statement ran afterward in the same session, pass an explicit sql_id instead of relying on the default.

V$SQL_PLAN_MONITOR shows progress but no timing per step. This is expected — per-operation timing is not collected by design. Join to V$ACTIVE_SESSION_HISTORY on the shared execution key and PLAN_LINE_ID to approximate it from activity samples instead.

A report that existed ten minutes ago seems to have disappeared. On releases before persisted SQL Monitor history, an entry is only guaranteed to survive about a minute past completion before becoming eligible for removal. On Oracle Database 12c and later, check DBA_HIST_REPORTS for a persisted copy before assuming it is gone.

SQL_TEXT in V$SQL_MONITOR looks cut off. The column holds only the first 2000 characters of a statement's text; IS_FULL_SQLTEXT on the same row reports whether that is the complete statement or a truncated one.

References

Posts in this series