Query Wait Events by Session with Oracle V$SESSION_WAIT

calendar Jun 7, 2026 / · 6 min read · V$SESSION_WAIT V$SESSION Wait Events Wait Class Performance Tuning Oracle DBA Scripts  ·
Share on: linkedin copy

Query Wait Events by Session with Oracle V$SESSION_WAIT

Purpose

V$SESSION_WAIT is the real-time view that shows what each Oracle session is waiting on this very second. For every session it exposes the wait event name, how long the session has been in that wait, and three event-specific parameters (P1, P2, P3) that pinpoint the resource — a file and block number, a lock type, a latch address. It is the view to reach for when the database "feels slow right now" and you need the current state, not a post-mortem from AWR.

On its own the view gives you SID and event; joined to V$SESSION it gives you the username, the program, the machine, and the SQL the session is running. That join turns a list of anonymous wait events into a list of named users stuck on named resources — the difference between "there is contention" and "the payroll batch on app-server-3 is blocked on a row lock."

The query below is read-only and built for incident response: run it, read the top rows, and you know where the database's time is going.

Code

 1SET LINESIZE 220
 2COLUMN username   FORMAT A16
 3COLUMN event      FORMAT A34
 4COLUMN wait_class FORMAT A12
 5COLUMN secs       FORMAT 999999
 6
 7SELECT s.sid,
 8       s.serial#,
 9       s.username,
10       w.event,
11       w.wait_class,
12       w.seconds_in_wait        AS secs,
13       w.p1, w.p2, w.p3,
14       s.sql_id
15FROM   v$session_wait w
16       JOIN v$session s
17         ON s.sid = w.sid
18WHERE  s.username IS NOT NULL          -- skip background processes
19AND    w.wait_class <> 'Idle'          -- skip idle waits
20ORDER  BY w.seconds_in_wait DESC;

Code Breakdown

  • v$session_wait w — one row per session, showing its current or most recent wait. The event column names the wait (for example db file sequential read, enq: TX - row lock contention, log file sync).
  • seconds_in_wait — how long the session has been in the current wait. Ordering by it descending surfaces the most stuck sessions first.
  • wait_class — a coarse bucket for the event: User I/O, Concurrency, Application, Commit, Network, and so on. Filtering out Idle removes sessions that are simply waiting for the next client request and would otherwise dominate the output.
  • p1, p2, p3 — the event-specific parameters. Their meaning depends on the event: for db file sequential read they are file number, block number, and block count; for an enqueue wait they encode the lock type and mode. V$EVENT_NAME documents what each parameter means per event.
  • The join to v$session s — adds username, serial#, and sql_id. serial# together with sid identifies the exact session for ALTER SYSTEM KILL SESSION; sql_id links to V$SQL for the statement text.
  • WHERE s.username IS NOT NULL — filters out Oracle background processes (whose username is null), leaving only real user sessions.

Key Points

  • V$SESSION_WAIT shows the current wait. From Oracle 10g onward, the same columns are also exposed directly on V$SESSION (the event, wait_class, and seconds_in_wait columns), so a single scan of V$SESSION can replace the join — but V$SESSION_WAIT remains the canonical, explicit source.
  • A session showing an Idle wait class (for example SQL*Net message from client) is not a problem — it is waiting for the user, not for the database. Always filter Idle out when hunting for contention.
  • seconds_in_wait resets each time the session enters a new wait. A small value on a busy event means the session is cycling through many short waits, not that it just started.
  • The state column (WAITING, WAITED KNOWN TIME, WAITED SHORT TIME) tells you whether the session is in the wait now or has just come out of one. Only WAITING is a live, in-progress wait.
  • For a session blocked by another session, V$SESSION.blocking_session names the holder directly — often faster than decoding P1/P2/P3 by hand.

Insights and Best Practices

Start with wait class, then drill into the event

Grouping the output by wait_class tells you the shape of the problem before you read individual events. A screen full of User I/O points at storage or a bad plan doing too many reads; a screen full of Concurrency points at latches or buffer contention; Application waits are almost always row locks from the application's own logic.

Decode P1/P2/P3 with V$EVENT_NAME

The parameter columns are only useful if you know what they mean for the event in question. Join to V$EVENT_NAME (or look the event up there) to get the parameter1/parameter2/parameter3 labels. For db file sequential read, turning P1 and P2 into a file and block number lets you map the wait back to a specific segment via DBA_EXTENTS.

Use it as a sampler, not a one-shot

A single query is a snapshot. Run it a few times a few seconds apart during an incident, or wrap it in a short polling loop, to see whether sessions are stuck on the same event (a genuine stall) or moving through different events (normal, busy work). This poor-man's sampling mirrors what Active Session History does continuously on Enterprise Edition with the Diagnostics Pack.

Know when to escalate to ASH and AWR

V$SESSION_WAIT is current-state only; it has no history. When you need "what was the system waiting on at 02:00 last night," that is Active Session History (V$ACTIVE_SESSION_HISTORY) and AWR territory. Use the live view to triage now, and the history views to investigate what already happened.

When to Use This Check

  • During a live "the database is slow" incident, to see where time is being spent right now.
  • To find which sessions are blocked and on what, before deciding whether to kill a session.
  • To confirm a suspected I/O problem by counting sessions on db file read events.
  • As a periodic sampler that records the active wait profile for later trend analysis.

Troubleshooting Common Issues

If the query returns only idle waits after filtering, the database genuinely is not contended — look at the application tier or the network instead. If many sessions share one event such as buffer busy waits or latch: cache buffers chains, decode P1/P2 to find the hot block or latch and investigate the object behind it. If a session sits on enq: TX - row lock contention for a long time, follow V$SESSION.blocking_session to the holder and decide whether that session should be committed, rolled back, or killed. Remember that seconds_in_wait is meaningful only when state = 'WAITING'; for a session that has already left the wait, the value reflects the last completed wait, not an ongoing one.

References

Posts in this series