V$SESSION_WAIT - Monitor Current Session Wait Events

V$SESSION_WAIT

Overview

V$SESSION_WAIT shows the current (or most recent) wait event for every session in the database, one row per session. It is Oracle’s real-time window into where database time is being spent at the session level: whether a session is waiting for a lock, performing I/O, waiting on a latch, experiencing network latency, or is actually running on CPU. This view is the foundational tool for wait-based performance analysis, the methodology pioneered by Cary Millsap and described in “Optimizing Oracle Performance.”

In modern Oracle versions (10g+), V$SESSION also contains wait event columns (EVENT, WAIT_CLASS, SECONDS_IN_WAIT, STATE), making V$SESSION_WAIT somewhat redundant for simple queries. However, V$SESSION_WAIT provides slightly lower overhead for wait-event-focused queries and includes the three P1, P2, P3 parameter columns that decode into specific resource identifiers (file numbers, block numbers, lock types), which are critical for detailed wait event interpretation.

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

Key Columns

Column	Datatype	Description
SID	NUMBER	Session identifier; joins to V$SESSION.SID
SEQ#	NUMBER	Sequence number that increments each time the session begins a new wait; useful for detecting that a wait has changed between samples
EVENT	VARCHAR2(64)	Name of the current wait event, or the last wait event if the session is on CPU
P1TEXT	VARCHAR2(64)	Description of the first wait event parameter (e.g., “file#”, “name
P1	NUMBER	Raw numeric value of the first wait event parameter
P1RAW	RAW(8)	Raw hex value of P1; some lock and latch decoding requires the hex form
P2TEXT	VARCHAR2(64)	Description of the second wait event parameter
P2	NUMBER	Raw numeric value of the second wait event parameter
P2RAW	RAW(8)	Raw hex value of P2
P3TEXT	VARCHAR2(64)	Description of the third wait event parameter
P3	NUMBER	Raw numeric value of the third wait event parameter
P3RAW	RAW(8)	Raw hex value of P3
WAIT_CLASS_ID	NUMBER	Numeric ID of the wait class
WAIT_CLASS#	NUMBER	Numeric sequence of the wait class
WAIT_CLASS	VARCHAR2(64)	Wait class name: User I/O, System I/O, Concurrency, Cluster, Network, Commit, Configuration, Administrative, Application, Scheduler, or Idle
WAIT_TIME	NUMBER	If the session is currently waiting: 0. If the session last waited: the duration in hundredths of a second (negative = currently on CPU)
SECONDS_IN_WAIT	NUMBER	Number of seconds the session has been in the current wait state
STATE	VARCHAR2(19)	WAITING (actively waiting), WAITED SHORT TIME, WAITED KNOWN TIME, or WAITED UNKNOWN TIME
WAIT_TIME_MICRO	NUMBER	Wait time in microseconds; more precise than WAIT_TIME for short waits
TIME_SINCE_LAST_WAIT_MICRO	NUMBER	Microseconds elapsed since the previous wait completed

Essential Queries

Basic Usage

Show all non-idle sessions with their current wait event and how long they have been waiting:

SELECT
    w.sid,
    s.username,
    s.status,
    w.event,
    w.wait_class,
    w.state,
    w.seconds_in_wait       AS secs_waiting,
    w.p1text,
    w.p1,
    w.p2text,
    w.p2,
    w.p3text,
    w.p3
FROM
    v$session_wait  w
    JOIN v$session  s ON s.sid = w.sid
WHERE
    w.wait_class != 'Idle'
    AND s.username IS NOT NULL
ORDER BY
    w.seconds_in_wait DESC;

Monitoring Query

Snapshot the wait event distribution across all active sessions, grouped by wait class, to immediately characterise the nature of any performance problem:

SELECT
    wait_class,
    event,
    COUNT(*)                                    AS session_count,
    MAX(seconds_in_wait)                        AS max_secs,
    ROUND(AVG(seconds_in_wait), 2)              AS avg_secs,
    SUM(seconds_in_wait)                        AS total_secs
FROM
    v$session_wait
WHERE
    wait_class != 'Idle'
GROUP BY
    wait_class,
    event
ORDER BY
    session_count DESC,
    total_secs DESC;

Combined with Other Views

Join V$SESSION_WAIT with V$SESSION and V$SQL to see the current wait event alongside the SQL being executed and the blocking session:

SELECT
    w.sid,
    s.serial#,
    s.username,
    s.blocking_session              AS blocked_by,
    w.event,
    w.wait_class,
    w.seconds_in_wait               AS secs_waiting,
    w.p1text || '=' || w.p1
        || ', ' || w.p2text || '=' || w.p2
        || ', ' || w.p3text || '=' || w.p3   AS wait_parameters,
    s.sql_id,
    SUBSTR(q.sql_text, 1, 100)      AS current_sql
FROM
    v$session_wait  w
    JOIN v$session  s ON s.sid = w.sid
    LEFT JOIN v$sql q ON q.sql_id      = s.sql_id
                      AND q.child_number = s.sql_child_number
WHERE
    w.wait_class != 'Idle'
    AND s.username IS NOT NULL
ORDER BY
    w.seconds_in_wait DESC;

I/O Wait Analysis

Decode “db file sequential read” and “db file scattered read” wait events — the most common I/O waits — to identify the specific file and block being read:

SELECT
    w.sid,
    s.username,
    w.event,
    w.seconds_in_wait               AS secs,
    w.p1                            AS file_number,
    f.file_name,
    f.tablespace_name,
    w.p2                            AS block_number,
    w.p3                            AS blocks_count,
    s.sql_id
FROM
    v$session_wait  w
    JOIN v$session  s ON s.sid   = w.sid
    JOIN dba_data_files f ON f.file_id = w.p1
WHERE
    w.event IN ('db file sequential read', 'db file scattered read')
    AND s.username IS NOT NULL
ORDER BY
    w.seconds_in_wait DESC;

Lock Wait Decoding

Identify sessions waiting on enqueue (lock) events and decode the lock type and mode from the P1 parameter:

SELECT
    w.sid,
    s.username,
    s.blocking_session,
    w.event,
    w.seconds_in_wait                           AS secs_waiting,
    CHR(BITAND(w.p1, -16777216) / 16777215)
     || CHR(BITAND(w.p1,  16711680) / 65535)   AS lock_type,
    DECODE(
        BITAND(w.p1, 65535),
        1, 'NULL', 2, 'SS', 3, 'SX', 4, 'S',
        5, 'SSX', 6, 'X', 'UNKNOWN'
    )                                           AS lock_mode,
    w.p2                                        AS lock_id1,
    w.p3                                        AS lock_id2,
    s.sql_id
FROM
    v$session_wait  w
    JOIN v$session  s ON s.sid = w.sid
WHERE
    w.event LIKE 'enq: %'
    AND s.username IS NOT NULL
ORDER BY
    w.seconds_in_wait DESC;

Common Use Cases

• Real-time wait event triage — When users report slowness, query V$SESSION_WAIT immediately to identify whether sessions are blocked on I/O, locks, latches, or network, narrowing the investigation before consulting AWR
• Lock wait identification — Filter on EVENT LIKE ‘enq: %’ and decode P1 to identify the lock type (TX, TM, etc.), then join to V$SESSION.BLOCKING_SESSION to find the root holder
• I/O hotspot detection — Filter on “db file sequential read” and decode P1 as FILE_NUMBER to find which datafiles are experiencing the most wait pressure
• Latch contention diagnosis — Filter on “latch: …” or “latch free” events and decode P2 as the latch address to identify the specific latch under contention
• Wait event polling — Sample V$SESSION_WAIT every second in a loop to build a lightweight real-time ASH equivalent for environments where the AWR Diagnostics Pack licence is unavailable
• Network wait investigation — Filter WAIT_CLASS = ‘Network’ to find sessions experiencing SQL*Net message delays, which can indicate network latency or application-side fetch stalling

Related Views

• V$SESSION — Contains wait event columns alongside full session metadata; use for combined session + wait queries
• V$SYSTEM_EVENT — System-wide cumulative wait event totals since instance startup; compare against V$SESSION_WAIT for instance-level context
• V$ACTIVE_SESSION_HISTORY — One-second sampled history of session wait events; use when the problem has already passed and V$SESSION_WAIT no longer shows it
• V$SQL — Retrieve the full SQL text and execution plan for the SQL_ID found in V$SESSION for the waiting session
• V$EVENT_NAME — Describes every known wait event, its wait class, and parameter meanings; join on EVENT to decode P1TEXT, P2TEXT, P3TEXT programmatically

Version Notes

• Oracle 7 / 8: V$SESSION_WAIT introduced as a standalone view separate from V$SESSION; the P1/P2/P3 parameter model established as the decoding convention for all wait events
• Oracle 9i: Wait class concept formalised; WAIT_CLASS and WAIT_CLASS_ID columns added, grouping events into categories for higher-level analysis
• Oracle 10g: V$SESSION extended to include wait event columns (EVENT, WAIT_CLASS, SECONDS_IN_WAIT, STATE), making V$SESSION_WAIT partially redundant for session-level queries; WAIT_TIME_MICRO added for microsecond precision
• Oracle 11g: TIME_SINCE_LAST_WAIT_MICRO added; V$SESSION_WAIT remains the lowest-overhead view for wait-only queries due to its narrower projection
• Oracle 12c (Multitenant): CON_ID column added; in a CDB, V$SESSION_WAIT from the root includes waits from all PDBs; PDB-local queries show only that container’s sessions
• Oracle 19c / 23ai: No structural changes; wait event names and parameter semantics occasionally updated in new releases — consult V$EVENT_NAME for the current parameter descriptions in your specific version