V$WAITSTAT - Diagnose Buffer Busy Waits by Block Class

V$WAITSTAT

Overview

V$WAITSTAT records the cumulative number of buffer wait events and the total time waited, broken down by the class of block being waited on. When a session needs a block that another session is reading into the buffer cache or modifying, it waits — and that wait is counted here by block class (data block, undo header, undo block, segment header, etc.). DBAs use this view to distinguish between different types of buffer contention: undo header waits indicate UNDO configuration issues, segment header waits indicate freelist contention on non-ASSM objects, and data block waits often indicate hot blocks or undersized buffer caches.

View Type: Dynamic Performance View Available Since: Oracle 7 (one of the oldest V$ views) Required Privileges: SELECT on V_$WAITSTAT or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
CLASS	VARCHAR2(18)	Block class being waited on (e.g., ‘data block’, ‘undo header’, ‘segment header’)
COUNT	NUMBER	Cumulative number of waits for this block class since instance startup
TIME	NUMBER	Cumulative centiseconds waited for this block class since instance startup
CON_ID	NUMBER	Container ID (12c+); 0 = CDB-wide or non-CDB

Essential Queries

Basic Usage

List all block classes with their wait counts and times, excluding zero-count entries:

SELECT
    class,
    count                                   AS wait_count,
    time                                    AS wait_time_cs,
    ROUND(time / 100, 2)                    AS wait_time_sec,
    CASE
        WHEN count = 0 THEN NULL
        ELSE ROUND(time / count, 2)
    END                                     AS avg_wait_cs
FROM
    v$waitstat
WHERE
    count > 0
ORDER BY
    count DESC;

Buffer Busy Waits by Block Class — Primary Analysis Query

The core diagnostic query: shows each block class with its share of total buffer waits, average wait duration, and a quick interpretation of what each class means for tuning:

SELECT
    ws.class,
    ws.count                                            AS wait_count,
    ROUND(ws.time / 100, 2)                             AS total_wait_sec,
    CASE
        WHEN ws.count = 0 THEN NULL
        ELSE ROUND((ws.time / 100.0) / ws.count * 1000, 2)
    END                                                 AS avg_wait_ms,
    ROUND(
        ws.count / NULLIF(SUM(ws.count) OVER (), 0) * 100, 2
    )                                                   AS pct_of_total_waits
FROM
    v$waitstat ws
WHERE
    ws.count > 0
ORDER BY
    ws.count DESC;

Buffer Contention Ratio

Calculate the buffer busy wait ratio: the percentage of buffer gets that resulted in a wait. A ratio above 1% for data blocks warrants investigation; above 5% is a performance problem. This query joins V$WAITSTAT with V$SYSSTAT for buffer get counts:

SELECT
    ws.class,
    ws.count                                            AS buffer_waits,
    st.value                                            AS total_buffer_gets,
    ROUND(ws.count / NULLIF(st.value, 0) * 100, 4)     AS contention_ratio_pct,
    ROUND(ws.time / 100, 2)                             AS total_wait_sec,
    CASE
        WHEN ws.count = 0 THEN NULL
        ELSE ROUND(ws.time / ws.count, 2)
    END                                                 AS avg_wait_cs
FROM
    v$waitstat ws
    CROSS JOIN (
        SELECT value
        FROM   v$sysstat
        WHERE  name = 'session logical reads'
    ) st
WHERE
    ws.count > 0
ORDER BY
    ws.count DESC;

Monitoring Query — Comparing V$WAITSTAT with V$SYSTEM_EVENT

V$WAITSTAT shows buffer waits by block class; V$SYSTEM_EVENT shows them by event name. Joining them gives a complete picture and validates the totals. The count in V$WAITSTAT for all classes should approximately equal the count in V$SYSTEM_EVENT for ‘buffer busy waits’ plus ‘read by other session’:

SELECT
    'V$WAITSTAT total'                      AS source,
    SUM(ws.count)                           AS total_waits,
    ROUND(SUM(ws.time) / 100, 2)            AS total_sec
FROM
    v$waitstat ws

UNION ALL

SELECT
    se.event                                AS source,
    se.total_waits                          AS total_waits,
    ROUND(se.time_waited / 100, 2)          AS total_sec
FROM
    v$system_event se
WHERE
    se.event IN (
        'buffer busy waits',
        'read by other session',
        'gc buffer busy acquire',
        'gc buffer busy release'
    )
ORDER BY
    source;

Combined with Other Views — Hot Block Identification via V$BH and V$SESSION_WAIT

When V$WAITSTAT shows high data block contention, identify the specific hot blocks by correlating current waits with buffer header information. This points directly to the object and block causing contention:

SELECT
    sw.p1                                   AS file#,
    sw.p2                                   AS block#,
    sw.p3                                   AS "class#",
    COUNT(*)                                AS waiting_sessions,
    o.owner,
    o.object_name,
    o.object_type,
    o.subobject_name
FROM
    v$session_wait sw
    LEFT JOIN dba_extents e
        ON  e.file_id  = sw.p1
        AND sw.p2 BETWEEN e.block_id AND e.block_id + e.blocks - 1
    LEFT JOIN dba_objects o
        ON  o.object_id = e.owner_id
        OR  o.object_name = e.segment_name
        AND o.owner = e.owner
WHERE
    sw.event IN ('buffer busy waits', 'read by other session')
GROUP BY
    sw.p1,
    sw.p2,
    sw.p3,
    o.owner,
    o.object_name,
    o.object_type,
    o.subobject_name
ORDER BY
    waiting_sessions DESC
FETCH FIRST 10 ROWS ONLY;

Advanced Analysis — Undo-Related Contention Breakdown

Undo header and undo block waits often indicate an undersized UNDO tablespace, too few undo segments, or long-running transactions. Compare undo wait counts against undo segment count and transaction volume:

SELECT
    ws.class,
    ws.count                                            AS wait_count,
    ROUND(ws.time / 100, 2)                             AS wait_sec,
    CASE
        WHEN ws.count = 0 THEN NULL
        ELSE ROUND(ws.time / ws.count, 2)
    END                                                 AS avg_wait_cs,
    us.online_count                                     AS online_undo_segments,
    ROUND(ws.count / NULLIF(us.online_count, 0), 0)    AS waits_per_undo_seg
FROM
    v$waitstat ws
    CROSS JOIN (
        SELECT COUNT(*) AS online_count
        FROM   v$rollstat
        WHERE  status = 'ONLINE'
    ) us
WHERE
    ws.class IN ('undo header', 'undo block', 'system undo header', 'system undo block')
ORDER BY
    ws.count DESC;

Historical Trending with AWR

Track buffer wait trends over time using AWR. A rising trend in data block waits often precedes a significant performance incident:

SELECT
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI')    AS snap_time,
    e.class,
    e.count - LAG(e.count) OVER (
        PARTITION BY e.class
        ORDER BY     s.snap_id
    )                                                       AS waits_in_interval,
    e.time - LAG(e.time) OVER (
        PARTITION BY e.class
        ORDER BY     s.snap_id
    )                                                       AS wait_cs_in_interval
FROM
    dba_hist_waitstat e
    JOIN dba_hist_snapshot s
        ON  s.snap_id         = e.snap_id
        AND s.dbid            = e.dbid
        AND s.instance_number = e.instance_number
WHERE
    e.dbid            = (SELECT dbid FROM v$database)
    AND e.instance_number = (SELECT instance_number FROM v$instance)
    AND s.begin_interval_time >= SYSDATE - 7
    AND e.class IN ('data block', 'undo header', 'undo block', 'segment header')
ORDER BY
    e.class,
    s.snap_id;

Common Use Cases

• Buffer cache sizing — Persistent high data block wait counts indicate the buffer cache is too small for the working set. Increase DB_CACHE_SIZE or add DB_KEEP_CACHE_SIZE for frequently accessed objects.
• UNDO configuration — High undo header wait counts with many concurrent transactions suggest increasing UNDO_RETENTION or switching to automatic undo management with a larger UNDO tablespace. High undo block waits indicate long-running transactions holding undo too long.
• Freelist contention (pre-ASSM) — High segment header waits on tables using manual segment space management (MSSM with freelists) are resolved by converting to ASSM tablespaces or increasing FREELISTS on the segment.
• Hot block identification — When data block waits are high, use the V$SESSION_WAIT join query above to identify the specific file/block, then DBA_EXTENTS to map it to an object. Solutions include reverse-key indexes, partitioning, or application-level load distribution.
• RAC buffer contention — In RAC environments, add gc buffer busy acquire and gc buffer busy release from V$SYSTEM_EVENT alongside V$WAITSTAT data to distinguish local buffer contention from global cache transfer contention.
• Baseline monitoring — Snapshot V$WAITSTAT at regular intervals and store the deltas. A sudden increase in any class during a stable period indicates a new hot object or configuration change.

Block Class Reference

Class	Typical Cause	Resolution
data block	Hot frequently-read data blocks; cache too small	Increase buffer cache; partition hot tables; use multiple buffer pools
undo header	Concurrent transactions competing for undo segment headers	More UNDO segments; larger UNDO tablespace; UNDO_RETENTION tuning
undo block	Long-running transactions; concurrent updates to same undo block	Reduce transaction duration; avoid unnecessarily long open transactions
segment header	Freelist contention on MSSM segments (pre-ASSM)	Convert to ASSM tablespace or increase FREELISTS parameter
file header block	Concurrent tablespace space allocation	Convert to locally managed tablespaces (LMT)
free list	Extent allocation contention	Use LMT with AUTOALLOCATE; avoid manual segment space management
system undo header	Contention on SYSTEM rollback segment	Rare; ensure AUM is configured and no transactions use SYSTEM rollback
sort block	Temporary sort extent allocation	Increase SORT_AREA_SIZE or PGA_AGGREGATE_TARGET

Related Views

• V$SYSTEM_EVENT — System-wide wait event totals; buffer busy waits and read by other session are the event-level view of the same contention V$WAITSTAT classifies by block type.
• V$SESSION_WAIT — Current wait per session; P1=file#, P2=block#, P3=class# for buffer waits, enabling hot block identification.
• V$BH — Buffer header information; shows which blocks are currently cached and their state (Current, Consistent Read, Free), useful for confirming buffer cache occupancy.
• V$ROLLSTAT — Undo segment statistics; correlate undo header wait counts with undo segment shrink, wrap, and extends to diagnose UNDO configuration.
• V$SYS_TIME_MODEL — Total DB time breakdown; buffer waits show up in the gap between DB time and DB CPU, helping size the overall impact of buffer contention on workload.

Version Notes

• Oracle 7 / 8: View present from early Oracle versions; one of the original V$ diagnostic views. TIME column was in centiseconds from the beginning.
• Oracle 9i: Block class list expanded; undo header and undo block classes clarified with introduction of Automatic Undo Management (AUM).
• Oracle 10g: read by other session split from buffer busy waits as a separate event in V$SYSTEM_EVENT, but V$WAITSTAT still aggregates both types together in the data block class.
• Oracle 11g: No structural changes; ASSM became the default for new tablespaces, reducing segment header and free list class relevance for modern schemas.
• Oracle 12c R1: CON_ID column added; in a CDB, V$WAITSTAT reflects the container in which the query is executed. DBA_HIST_WAITSTAT persists AWR snapshots.
• Oracle 19c: No structural changes; compatible with all existing queries.
• Oracle 23ai: Compatible; no new block classes added. In cloud Autonomous Database configurations, V$WAITSTAT is accessible but some classes may show near-zero counts due to automatic storage management.