V$SQL_MONITOR - Real-Time SQL Monitoring for Long-Running Queries

V$SQL_MONITOR

Overview

V$SQL_MONITOR provides real-time execution statistics for SQL statements that Oracle has automatically selected for monitoring. A statement is automatically monitored when it runs in parallel, or when it consumes more than 5 seconds of CPU or elapsed time in a single execution. Monitoring can also be forced with the /*+ MONITOR */ hint. Each row represents a single SQL execution and is updated approximately once per second while the statement is running.

View Type: Dynamic Performance View Available Since: Oracle 11g Required Privileges: SELECT on V_$SQL_MONITOR or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SQL_ID	VARCHAR2(13)	SQL identifier
SQL_EXEC_ID	NUMBER	Unique execution ID — distinguishes runs of the same SQL_ID
SQL_EXEC_START	DATE	When this execution began
STATUS	VARCHAR2(19)	EXECUTING, DONE, DONE (ERROR), DONE (FIRST N ROWS)
USERNAME	VARCHAR2(128)	Oracle user running the statement
SID	NUMBER	Session ID
SESSION_SERIAL#	NUMBER	Session serial number
ELAPSED_TIME	NUMBER	Total elapsed time in microseconds
CPU_TIME	NUMBER	CPU time consumed in microseconds
QUEUING_TIME	NUMBER	Time spent in parallel query queue (microseconds)
BUFFER_GETS	NUMBER	Logical reads (buffer cache hits + disk reads)
DISK_READS	NUMBER	Physical disk reads
DIRECT_WRITES	NUMBER	Direct path writes (e.g., parallel DML, sorts to disk)
PHYSICAL_READ_BYTES	NUMBER	Bytes read from disk
PHYSICAL_WRITE_BYTES	NUMBER	Bytes written to disk
FETCHES	NUMBER	Number of rows fetched by the client
ROWS_PROCESSED	NUMBER	Total rows processed so far
SQL_TEXT	CLOB	First 200 characters of the SQL statement
SQL_FULLTEXT	CLOB	Full SQL text
PROCESS_NAME	VARCHAR2(5)	Process name (e.g., p000 for parallel slave)
PX_SERVERS_REQUESTED	NUMBER	Number of parallel servers requested
PX_SERVERS_ALLOCATED	NUMBER	Number of parallel servers actually allocated
ERROR_NUMBER	NUMBER	ORA error number if STATUS = DONE (ERROR)
ERROR_MESSAGE	VARCHAR2(4000)	Full error message if the statement failed
MODULE	VARCHAR2(64)	Application module name
ACTION	VARCHAR2(64)	Application action name
PROGRAM	VARCHAR2(48)	Client program
SERVICE_NAME	VARCHAR2(64)	Database service name

Essential Queries

Basic Usage

-- Currently executing statements being monitored
SELECT
    m.sql_id,
    m.sid,
    m.username,
    m.status,
    ROUND(m.elapsed_time  / 1e6, 1)                    AS elapsed_sec,
    ROUND(m.cpu_time      / 1e6, 1)                    AS cpu_sec,
    m.buffer_gets,
    m.disk_reads,
    m.rows_processed,
    m.px_servers_allocated                             AS px_servers,
    SUBSTR(m.sql_text, 1, 100)                         AS sql_text
FROM
    v$sql_monitor m
WHERE
    m.status = 'EXECUTING'
ORDER BY
    m.elapsed_time DESC;

Monitoring Query

-- Long-running and recently completed statements (last 4 hours), sorted by elapsed time
SELECT
    m.sql_id,
    m.sql_exec_id,
    m.username,
    m.status,
    TO_CHAR(m.sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
    ROUND(m.elapsed_time       / 1e6, 1)               AS elapsed_sec,
    ROUND(m.cpu_time           / 1e6, 1)               AS cpu_sec,
    ROUND(m.queuing_time       / 1e6, 1)               AS queue_sec,
    m.buffer_gets,
    m.disk_reads,
    ROUND(m.physical_read_bytes  / 1048576, 1)         AS phys_read_mb,
    ROUND(m.physical_write_bytes / 1048576, 1)         AS phys_write_mb,
    m.rows_processed,
    m.fetches,
    m.px_servers_allocated                             AS px_svrs,
    SUBSTR(m.sql_text, 1, 80)                          AS sql_text
FROM
    v$sql_monitor m
WHERE
    m.sql_exec_start >= SYSDATE - (4/24)
ORDER BY
    m.elapsed_time DESC
FETCH FIRST 30 ROWS ONLY;

Combined with Other Views

-- Monitored SQL with current session and blocking details
SELECT
    m.sql_id,
    m.sid,
    m.username,
    m.status,
    ROUND(m.elapsed_time / 1e6, 1)                     AS elapsed_sec,
    s.wait_class,
    s.event,
    s.seconds_in_wait,
    s.blocking_session                                 AS blocked_by,
    s.machine,
    s.program,
    SUBSTR(m.sql_text, 1, 80)                          AS sql_text
FROM
    v$sql_monitor m
    JOIN v$session s ON s.sid     = m.sid
                    AND s.serial# = m.session_serial#
WHERE
    m.status = 'EXECUTING'
ORDER BY
    m.elapsed_time DESC;

Advanced Analysis

-- Parallel query monitoring — identify DOP degradation
SELECT
    m.sql_id,
    m.sql_exec_id,
    m.username,
    TO_CHAR(m.sql_exec_start, 'HH24:MI:SS')            AS start_time,
    ROUND(m.elapsed_time / 1e6, 1)                     AS elapsed_sec,
    m.px_servers_requested,
    m.px_servers_allocated,
    CASE
        WHEN m.px_servers_requested > 0
        THEN ROUND(m.px_servers_allocated / m.px_servers_requested * 100, 0)
        ELSE NULL
    END                                                AS dop_pct_fulfilled,
    ROUND(m.cpu_time      / 1e6, 1)                    AS cpu_sec,
    m.buffer_gets,
    ROUND(m.physical_read_bytes / 1048576, 1)          AS phys_read_mb,
    m.rows_processed,
    SUBSTR(m.sql_text, 1, 80)                          AS sql_text
FROM
    v$sql_monitor m
WHERE
    m.sql_exec_start       >= SYSDATE - (2/24)
    AND m.px_servers_requested > 0
ORDER BY
    m.elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Resource-intensive statements — identify top I/O and CPU consumers today
SELECT
    m.sql_id,
    COUNT(DISTINCT m.sql_exec_id)                           AS executions,
    ROUND(SUM(m.elapsed_time)       / 1e6, 0)              AS total_elapsed_sec,
    ROUND(SUM(m.cpu_time)           / 1e6, 0)              AS total_cpu_sec,
    SUM(m.buffer_gets)                                     AS total_buffer_gets,
    SUM(m.disk_reads)                                      AS total_disk_reads,
    ROUND(SUM(m.physical_read_bytes)  / 1073741824, 2)     AS total_phys_read_gb,
    ROUND(SUM(m.physical_write_bytes) / 1073741824, 2)     AS total_phys_write_gb,
    SUM(m.rows_processed)                                  AS total_rows,
    SUBSTR(MAX(m.sql_text), 1, 80)                         AS sql_text
FROM
    v$sql_monitor m
WHERE
    m.sql_exec_start >= TRUNC(SYSDATE)
GROUP BY
    m.sql_id
ORDER BY
    total_elapsed_sec DESC
FETCH FIRST 20 ROWS ONLY;

Common Use Cases

• Real-time progress tracking — Monitor a batch job or data migration that will run for minutes or hours. ROWS_PROCESSED and ELAPSED_TIME give a live completion estimate.
• Parallel query DOP analysis — PX_SERVERS_REQUESTED vs PX_SERVERS_ALLOCATED reveals when the parallel server pool is exhausted or when the optimizer’s DOP is being downgraded.
• Failed SQL forensics — STATUS = ‘DONE (ERROR)’ rows retain their ERROR_NUMBER and resource consumption, allowing post-mortem analysis of statements that aborted.
• I/O-heavy statement identification — PHYSICAL_READ_BYTES and PHYSICAL_WRITE_BYTES quickly identify statements driving excessive storage I/O, even during an active incident.
• Application module attribution — MODULE and ACTION columns (set via DBMS_APPLICATION_INFO) map monitored statements back to specific application components for capacity planning.
• SLA breach alerting — Query V$SQL_MONITOR on a schedule to alert when any EXECUTING statement has ELAPSED_TIME exceeding a threshold (e.g., 300 seconds).

Related Views

• V$ACTIVE_SESSION_HISTORY — Join on SQL_EXEC_ID to get per-second wait event detail during the monitored execution
• V$SQL — Join on SQL_ID for cumulative execution statistics across all executions, not just monitored ones
• V$SESSION — Join on SID / SESSION_SERIAL# for current session state, blocking info, and client details
• V$PROCESS — Join through V$SESSION for OS-level PID and PGA detail of the executing process
• V$SQL_PLAN — Join on SQL_ID and SQL_PLAN_HASH_VALUE to retrieve the execution plan for a monitored statement

Version Notes

• Oracle 11g: View introduced along with the Real-Time SQL Monitoring feature. Requires the Diagnostics and Tuning Pack license for production use.
• Oracle 11g R2: DBMS_SQLTUNE.REPORT_SQL_MONITOR() added for generating HTML/TEXT/XML monitoring reports from this view’s data.
• Oracle 12c: Multitenant support added — CON_ID column identifies the PDB. SQL monitoring now also covers PL/SQL blocks.
• Oracle 12c R2: QUEUING_TIME column added to distinguish parallel query queue wait from actual execution time.
• Oracle 19c: Automatic indexing activity is tracked in V$SQL_MONITOR. Adaptive parallel DOP changes are reflected in PX_SERVERS_ALLOCATED updates.
• Oracle 21c / 23ai: Real-Time SQL Monitoring extended to cover SQL macros and certain JSON/graph query patterns. In 23ai, monitoring data feeds into the SQL Analysis Report available in Database Actions.