V$SYSTEM_EVENT - Analyze Top Wait Events & I/O Latency

V$SYSTEM_EVENT

Overview

V$SYSTEM_EVENT contains one row per wait event that has ever been waited on since the instance started, accumulating totals across all sessions. It is the highest-level view for wait-based performance analysis — the Oracle methodology introduced by Cary Millsap and others states that database time is either spent on CPU or waiting, and V$SYSTEM_EVENT quantifies every category of waiting on the instance. DBAs use it to identify the dominant bottleneck (I/O, locking, network, latching) and to track whether tuning efforts have reduced specific wait event totals over time. It feeds directly into the “Top 5 Wait Events” section of every AWR and Statspack report.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$SYSTEM_EVENT or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
EVENT	VARCHAR2(64)	Name of the wait event (e.g., “db file sequential read”, “log file sync”)
EVENT#	NUMBER	Internal event number
TOTAL_WAITS	NUMBER	Cumulative number of times this event has been waited on since instance startup
TOTAL_TIMEOUTS	NUMBER	Cumulative number of times a timed wait for this event expired before the resource was obtained
TIME_WAITED	NUMBER	Cumulative time waited in centiseconds (1/100th of a second); divide by 100 for seconds
AVERAGE_WAIT	NUMBER	Average wait time in centiseconds per wait occurrence (TIME_WAITED / TOTAL_WAITS)
TIME_WAITED_MICRO	NUMBER	Cumulative time waited in microseconds (available from Oracle 10g); more precise than TIME_WAITED
MAX_WAIT	NUMBER	Maximum single wait duration observed in centiseconds since instance startup
WAIT_CLASS#	NUMBER	Internal wait class number
WAIT_CLASS	VARCHAR2(64)	Human-readable wait class (e.g., User I/O, System I/O, Concurrency, Network, Other)

Essential Queries

Basic Usage

List all non-idle wait events sorted by total time waited — the direct equivalent of the AWR “Top 5 Timed Events” section:

SELECT
    e.event,
    e.wait_class,
    e.total_waits,
    e.total_timeouts,
    ROUND(e.time_waited / 100, 2)                AS time_waited_secs,
    ROUND(e.average_wait / 100, 4)               AS avg_wait_secs,
    ROUND(e.time_waited_micro / 1e6, 2)          AS time_waited_secs_micro,
    e.max_wait                                   AS max_wait_cs
FROM
    v$system_event e
WHERE
    e.wait_class != 'Idle'
ORDER BY
    e.time_waited DESC
FETCH FIRST 25 ROWS ONLY;

Monitoring Query: Wait Class Breakdown

Summarise wait time by class — gives the 30-second triage view of where the instance is spending its time:

SELECT
    e.wait_class,
    COUNT(*)                                     AS event_count,
    SUM(e.total_waits)                           AS total_waits,
    SUM(e.total_timeouts)                        AS total_timeouts,
    ROUND(SUM(e.time_waited) / 100, 2)           AS total_secs_waited,
    ROUND(AVG(e.average_wait) / 100, 4)          AS avg_wait_secs_per_event,
    ROUND(MAX(e.average_wait) / 100, 4)          AS max_avg_wait_secs
FROM
    v$system_event e
WHERE
    e.wait_class != 'Idle'
GROUP BY
    e.wait_class
ORDER BY
    total_secs_waited DESC;

Combined with Other Views

Join V$SYSTEM_EVENT with V$EVENT_NAME to get the parameter descriptions for each wait event — critical for understanding what P1, P2, P3 mean in V$SESSION for the same event:

SELECT
    se.event,
    se.wait_class,
    se.total_waits,
    ROUND(se.time_waited / 100, 2)               AS time_waited_secs,
    ROUND(se.average_wait / 100, 4)              AS avg_wait_secs,
    en.parameter1                                AS p1_meaning,
    en.parameter2                                AS p2_meaning,
    en.parameter3                                AS p3_meaning
FROM
    v$system_event se
    JOIN v$event_name en ON en.name = se.event
WHERE
    se.wait_class != 'Idle'
    AND se.total_waits > 100
ORDER BY
    se.time_waited DESC
FETCH FIRST 30 ROWS ONLY;

Advanced Analysis: I/O Wait Event Deep Dive

Separately analyse sequential (single-block) vs. scattered (multi-block) reads alongside direct path I/O — the primary I/O wait breakdown for storage sizing and I/O tuning:

SELECT
    e.event,
    e.wait_class,
    e.total_waits,
    ROUND(e.time_waited / 100, 2)                AS time_waited_secs,
    ROUND(e.average_wait / 100, 4)               AS avg_wait_secs,
    ROUND(e.time_waited_micro / e.total_waits / 1000, 3)
                                                 AS avg_wait_ms,
    e.max_wait                                   AS max_wait_cs,
    -- Average I/O latency classification
    CASE
        WHEN e.time_waited_micro / NULLIF(e.total_waits, 0) < 1000  THEN 'Excellent (<1ms)'
        WHEN e.time_waited_micro / NULLIF(e.total_waits, 0) < 5000  THEN 'Good (1-5ms)'
        WHEN e.time_waited_micro / NULLIF(e.total_waits, 0) < 20000 THEN 'Acceptable (5-20ms)'
        ELSE                                                              'Investigate (>20ms)'
    END                                          AS latency_grade
FROM
    v$system_event e
WHERE
    e.event IN (
        'db file sequential read',
        'db file scattered read',
        'direct path read',
        'direct path read temp',
        'direct path write',
        'direct path write temp',
        'db file parallel read',
        'db file parallel write',
        'log file parallel write',
        'log file sync'
    )
ORDER BY
    e.time_waited DESC;

Latch and Concurrency Wait Analysis

Identify latch contention and internal concurrency waits — these signal memory structure pressure:

SELECT
    e.event,
    e.wait_class,
    e.total_waits,
    e.total_timeouts,
    ROUND(e.time_waited / 100, 2)                AS time_waited_secs,
    ROUND(e.average_wait / 100, 4)               AS avg_wait_secs,
    ROUND(
        100 * e.total_timeouts / NULLIF(e.total_waits, 0)
    , 2)                                         AS timeout_pct
FROM
    v$system_event e
WHERE
    e.wait_class IN ('Concurrency', 'Other')
    AND e.total_waits > 0
    AND e.wait_class != 'Idle'
ORDER BY
    e.time_waited DESC
FETCH FIRST 20 ROWS ONLY;

Idle vs. Non-Idle Wait Summary with Database Time

Compute the split between CPU time, non-idle wait time, and idle wait time — the fundamental Oracle time model breakdown available when TIMED_STATISTICS is TRUE:

SELECT
    category,
    ROUND(total_secs, 2)                         AS total_secs,
    ROUND(100 * total_secs / NULLIF(SUM(total_secs) OVER (), 0), 2)
                                                 AS pct_of_total
FROM (
    -- Non-idle wait time from V$SYSTEM_EVENT
    SELECT
        'Non-Idle Wait Time'                     AS category,
        SUM(time_waited) / 100                   AS total_secs
    FROM
        v$system_event
    WHERE
        wait_class != 'Idle'

    UNION ALL

    -- Idle wait time from V$SYSTEM_EVENT
    SELECT
        'Idle Wait Time'                         AS category,
        SUM(time_waited) / 100                   AS total_secs
    FROM
        v$system_event
    WHERE
        wait_class = 'Idle'

    UNION ALL

    -- DB CPU time from V$SYS_TIME_MODEL
    SELECT
        'DB CPU Time'                            AS category,
        value / 1e6                              AS total_secs
    FROM
        v$sys_time_model
    WHERE
        stat_name = 'DB CPU'
)
ORDER BY
    total_secs DESC;

Snapshot-Based Rate Calculation

Calculate per-second wait rates over a measurement interval without AWR, suitable for real-time monitoring scripts:

-- Capture snapshot 1
CREATE GLOBAL TEMPORARY TABLE sys_event_snap1 ON COMMIT PRESERVE ROWS AS
SELECT
    event,
    wait_class,
    total_waits,
    time_waited,
    time_waited_micro,
    SYSDATE AS snap_time
FROM v$system_event;

-- After the interval (e.g., 60 seconds), run the delta report:
SELECT
    s2.event,
    s2.wait_class,
    s2.total_waits          - s1.total_waits     AS delta_waits,
    ROUND((s2.time_waited   - s1.time_waited) / 100, 3)
                                                 AS delta_secs_waited,
    ROUND(
        (s2.time_waited_micro - s1.time_waited_micro) /
        NULLIF(s2.total_waits - s1.total_waits, 0) / 1000, 3
    )                                            AS avg_wait_ms_in_interval,
    ROUND(
        (s2.total_waits - s1.total_waits) /
        (86400 * (SYSDATE - s1.snap_time)), 2
    )                                            AS waits_per_second
FROM
    v$system_event  s2
    JOIN sys_event_snap1 s1 ON s1.event = s2.event
WHERE
    s2.wait_class != 'Idle'
    AND s2.total_waits - s1.total_waits > 0
ORDER BY
    delta_secs_waited DESC
FETCH FIRST 20 ROWS ONLY;

Common Use Cases

• Top wait event identification — Sort by TIME_WAITED to find the single biggest bottleneck in the instance right now; this is always the first step in the Oracle wait-event performance methodology
• Storage I/O latency benchmarking — Calculate average “db file sequential read” latency (TIME_WAITED_MICRO / TOTAL_WAITS) to establish a storage baseline and detect degradation after hardware or configuration changes
• Log file sync tuning — High average “log file sync” wait time indicates I/O latency on the redo log device or excessive small-commit workloads; the target is consistently below 1ms average
• Latch contention diagnosis — High “latch: shared pool” or “latch: library cache” waits signal shared pool pressure, often caused by hard parsing or cursor invalidation storms
• Network bottleneck detection — Growing “SQLNet message from client” or “SQLNet more data from client” waits can indicate network latency, chatty application protocols, or missing array fetch configuration
• Pre/post comparison for changes — Capture V$SYSTEM_EVENT totals before and after a tuning change (index creation, parameter change, storage upgrade) to quantify the improvement in wait time

Related Views

• V$SESSION — Per-session current wait event (EVENT column); V$SESSION_EVENT is the per-session historical equivalent
• V$SYSSTAT — Statistic-side complement to the wait-side data in V$SYSTEM_EVENT; together they represent total database time
• V$SQL — Correlate SQL-level elapsed time with the system-level wait events to find which statements drive specific waits
• V$SESSION_EVENT — Per-session accumulated wait statistics; same structure as V$SYSTEM_EVENT but scoped to individual sessions
• V$EVENT_NAME — Master lookup for all wait events: parameter meanings, wait class assignment, and display name
• V$SYS_TIME_MODEL — Higher-level time model including DB CPU, DB Time, and PL/SQL execution time; complement to V$SYSTEM_EVENT for total database time accounting
• DBA_HIST_SYSTEM_EVENT — AWR snapshot of V$SYSTEM_EVENT; survives instance restarts; used for historical wait trend analysis in AWR reports

Version Notes

• Oracle 8i: TIME_WAITED and AVERAGE_WAIT columns added when TIMED_STATISTICS = TRUE; prior versions only tracked counts, not duration
• Oracle 9i: Wait event infrastructure standardised; V$SESSION_WAIT_HISTORY introduced as a rolling per-session wait history buffer
• Oracle 10g: TIME_WAITED_MICRO column added for microsecond precision; WAIT_CLASS column introduced, grouping ~400 events into ~12 categories; V$ACTIVE_SESSION_HISTORY (ASH) introduced for per-second session-level sampling
• Oracle 11g: MAX_WAIT column added; V$SYS_TIME_MODEL introduced as the complementary CPU-time view; wait class “Cluster” added for RAC-specific events
• Oracle 12c (Multitenant): CON_ID column added; events are scoped per PDB when queried from within a PDB; CDB root aggregates across all containers; new “Scheduler” wait class added
• Oracle 19c: Additional wait events for automated features (auto-index, real-time statistics, SQL plan management); “enq: TX - row lock contention” variants refined
• Oracle 21c / 23ai: New wait events for JSON Binary (OSON) processing, AutoML in-database operations, and vector index operations (23ai AI Vector Search)