buffer busy waits - Diagnose Oracle Buffer Contention

buffer busy waits

Overview

Wait Event Class: Concurrency

Parameters: file#, block#, class# (block class — identifies the type of block being waited on)

buffer busy waits occurs when a session needs to access a specific block in the buffer cache but cannot because another session is currently performing an incompatible operation on that same buffer. The waiting session must wait until the current holder completes its operation on the buffer and releases it.

What Triggers This Wait

A session encounters buffer busy waits in these situations:

1. Another session is reading the block from disk — the first session to request a block not in cache pins the buffer while performing the physical read; all subsequent sessions needing the same block must wait for that read to complete
2. Another session is modifying the block — a session is in the middle of a block modification (writing, updating, or applying undo) and has the buffer pinned exclusively
3. Block cleanout in progress — delayed block cleanout requires temporary exclusive access to the block

The wait is essentially a buffer-level mutual exclusion mechanism. Unlike enq: TX - row lock contention (which is about row-level locking), buffer busy waits is about the physical buffer slot itself.

Buffer Classes (V$WAITSTAT)

The class# parameter identifies the type of block causing contention:

Class	Block Type	Common Cause
1	Data block	Hot table rows
4	Segment header	INSERT contention on non-ASSM segment
5	Segment header	Freelists exhausted
6	Undo header	Undo segment header contention
7	Undo block	Undo block reads for read consistency
8	1st level BMB	Bitmap block contention
9	2nd level BMB	Bitmap block contention

---

When This Wait Is a Problem

Normal vs. Concerning

Scenario	Assessment
Rare, sub-millisecond occurrences	Normal — incidental block sharing
Consistent appearance in AWR Top 5	Investigate — a hot block or contention point exists
Specific file/block appearing repeatedly	Hot block — requires targeted remediation
Segment header class (4, 8, 9) dominant	Segment design issue — move to ASSM
Undo block class (6, 7) dominant	Undo contention — check undo segments

Thresholds

Buffer busy waits should not contribute more than 1–2% of total DB time in a well-tuned OLTP system. When this event is consistently in the AWR Top 5 wait events and its average wait exceeds a few milliseconds, the underlying hot block pattern requires investigation and resolution.

---

Diagnostic Queries

1. System-Level Buffer Busy Statistics

-- Overall buffer busy waits statistics
SELECT
    event,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2)           AS total_secs,
    ROUND(average_wait * 10, 2)           AS avg_wait_ms,
    ROUND(time_waited * 100.0 /
          NULLIF((SELECT SUM(time_waited)
                  FROM v$system_event
                  WHERE wait_class NOT IN ('Idle')), 0), 2) AS pct_db_time
FROM v$system_event
WHERE event IN ('buffer busy waits', 'read by other session')
ORDER BY time_waited DESC;

-- V$WAITSTAT shows wait counts by block class
-- This is the key query for understanding WHAT is contended
SELECT
    class,
    count,
    time,
    ROUND(time / NULLIF(count, 0), 4) AS avg_wait_cs
FROM v$waitstat
WHERE count > 0
ORDER BY count DESC;

2. Currently Waiting Sessions with Block Details

-- Sessions currently experiencing buffer busy waits
SELECT
    sw.sid,
    sw.serial#,
    s.username,
    s.program,
    s.sql_id,
    sw.p1                                          AS file_number,
    sw.p2                                          AS block_number,
    sw.p3                                          AS class_number,
    sw.seconds_in_wait,
    f.name                                         AS data_file,
    ts.name                                        AS tablespace_name
FROM v$session_wait  sw
JOIN v$session       s  ON sw.sid  = s.sid
JOIN v$datafile      f  ON sw.p1   = f.file#
JOIN v$tablespace    ts ON f.ts#   = ts.ts#
WHERE sw.event IN ('buffer busy waits', 'read by other session')
ORDER BY sw.seconds_in_wait DESC;

3. Identify the Hot Block and Its Object

-- Translate file# and block# to an object name
-- Run this during or after buffer busy waits are observed
SELECT
    owner,
    segment_name,
    segment_type,
    tablespace_name,
    block_id,
    blocks
FROM dba_extents
WHERE file_id = &file_number
  AND &block_number BETWEEN block_id AND block_id + blocks - 1;

-- More precise: dump block to identify exact row/object
-- (Run as SYS)
SELECT dbms_rowid.rowid_create(
    1,  -- rowid type
    (SELECT data_object_id FROM dba_objects
     WHERE object_name = 'YOUR_TABLE' AND owner = 'YOUR_SCHEMA'),
    &file_number,
    &block_number,
    1  -- row slot (approximate)
) AS sample_rowid
FROM dual;

4. ASH Analysis — Hot Blocks Over Time

-- ASH: Identify hot file/block combinations (last hour)
-- Requires Diagnostics Pack license
SELECT
    ash.current_file#,
    ash.current_block#,
    ash.current_obj#,
    o.object_name,
    o.object_type,
    o.owner,
    COUNT(*)                              AS ash_samples,
    COUNT(*) * 10                         AS est_wait_secs,
    ROUND(COUNT(*) * 100.0 /
          SUM(COUNT(*)) OVER (), 2)       AS pct_of_buffer_busy_waits
FROM v$active_session_history  ash
LEFT JOIN dba_objects           o ON ash.current_obj# = o.object_id
WHERE ash.event        IN ('buffer busy waits', 'read by other session')
  AND ash.sample_time  > SYSDATE - 1/24
GROUP BY ash.current_file#, ash.current_block#, ash.current_obj#,
         o.object_name, o.object_type, o.owner
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

5. Segment Header Contention Check

-- Check if hot tables are using ASSM or manual freelists
SELECT
    t.owner,
    t.table_name,
    t.freelists,
    t.freelist_groups,
    t.pct_free,
    t.pct_used,
    ts.segment_space_management
FROM dba_tables    t
JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name
WHERE t.owner       = 'YOUR_SCHEMA'
  AND t.table_name  = 'YOUR_TABLE';

---

Root Causes

1. Hot Data Blocks — Frequently Accessed Rows

A small number of table or index blocks are accessed by many concurrent sessions. Common patterns:

• Lookup/reference tables: A STATUS_CODES or COUNTRIES table accessed by every transaction — the header block is read by everyone
• Recently inserted rows: OLTP inserts cluster into the “right edge” of a table’s last-used extent, causing the same few blocks to be written simultaneously
• Popular index leaf blocks: The highest-valued leaf block of a sequence-based index is always the insertion target; all concurrent inserts contend on it

2. Segment Header Contention (Freelist Management)

When a table uses manual segment space management (MSSM / freelist-based), the segment header block contains the freelist that Oracle consults to find blocks with free space for inserts. Every insert modifies this freelist, serializing all concurrent inserts through the segment header block.

This is the classic buffer busy waits on class 4 (segment header). The fix is always to migrate to ASSM tablespaces.

3. Undo Header and Undo Block Contention

Heavy concurrent DML generates many undo writes and reads. When multiple sessions contend for the same undo segment header (class 6) or undo blocks (class 7), buffer busy waits appear on the undo tablespace. In older Oracle versions, insufficient undo segments caused header contention. With AUM (Automatic Undo Management), Oracle manages this, but very high DML rates can still cause undo block contention.

4. Read by Other Session (Related Event)

In Oracle 10g+, when the first session to request an uncached block is reading it from disk, other sessions waiting for that same block wait on read by other session rather than buffer busy waits. These events have the same root cause — a block that many sessions need simultaneously — and the same resolution strategies apply.

5. Index Block Contention — Right-Hand Insert Pattern

Monotonically increasing primary keys (sequences, timestamps) cause all concurrent inserts to target the rightmost leaf block of the index. This block becomes a hot buffer. Resolution involves reverse-key indexes (scatters inserts across index blocks) or hash partitioning.

---

Resolution Steps

Resolution for Hot Data Blocks — Partition the Table

-- Partition a hot table to distribute I/O across multiple segments
-- Each partition has its own segment header and block range

-- Range partition by date (most common for reducing right-edge insert contention)
CREATE TABLE orders_new
    PARTITION BY RANGE (order_date)
    INTERVAL (INTERVAL '1' MONTH)
    (
        PARTITION p_initial VALUES LESS THAN (DATE '2024-01-01')
    )
AS SELECT * FROM orders;
-- Interval partitioning auto-creates new partitions — inserts are spread across
-- the current month's partition only

Resolution for Segment Header Contention — Migrate to ASSM

-- Step 1: Create a new ASSM tablespace
CREATE TABLESPACE app_data_assm
    DATAFILE '/data/app_data_assm01.dbf' SIZE 10G AUTOEXTEND ON
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;  -- ASSM eliminates freelist contention

-- Step 2: Move the hot table to the ASSM tablespace
ALTER TABLE hot_table MOVE TABLESPACE app_data_assm ONLINE;

-- Step 3: Rebuild indexes (required after table move)
ALTER INDEX idx_hot_table_pk REBUILD TABLESPACE indexes_assm ONLINE;

Resolution for Index Right-Hand Insert Contention

-- Option 1: Reverse-key index (scatters inserts across index tree)
-- Caution: eliminates range scan capability on this index
CREATE INDEX idx_orders_id_rv
    ON orders(order_id) REVERSE
    TABLESPACE indexes;

-- Option 2: Hash-partition the index
CREATE INDEX idx_orders_id_hash
    ON orders(order_id)
    GLOBAL PARTITION BY HASH (order_id) PARTITIONS 8
    TABLESPACE indexes;

-- Option 3: Use a sequence with a high CACHE value (reduces header contention)
ALTER SEQUENCE orders_seq CACHE 1000;
-- Larger cache = fewer redo writes + fewer sequence header blocks accessed

Resolution for Undo Contention

-- Check number of active undo segments
SELECT
    usn,
    name,
    status,
    xacts  AS active_transactions
FROM v$rollstat  rs
JOIN v$rollname  rn ON rs.usn = rn.usn
ORDER BY xacts DESC;

-- If using AUM, verify undo_retention and undo tablespace size are adequate
SHOW PARAMETER undo;

SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS size_gb
FROM dba_data_files
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
GROUP BY tablespace_name;

-- Increase undo retention if undo blocks are being stolen (reused before read consistency needs them)
ALTER SYSTEM SET undo_retention = 900 SCOPE=BOTH;  -- 15 minutes

Increase INITRANS for High-Concurrency Tables

-- INITRANS controls how many concurrent transactions can modify a block simultaneously
-- Default is 2 for tables, 2 for indexes
-- ITL exhaustion causes TX mode 4 waits (a form of enq: TX - row lock contention)
-- But increasing INITRANS also reduces buffer busy waits on blocks being written concurrently

-- Check current setting
SELECT ini_trans, max_trans FROM dba_tables
WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

-- Increase INITRANS (affects new blocks immediately; rebuild for existing blocks)
ALTER TABLE your_schema.your_table INITRANS 10;
ALTER TABLE your_schema.your_table MOVE ONLINE;

-- Verify change applied
SELECT ini_trans FROM dba_tables
WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

---

Prevention & Tuning

1. Always Use ASSM Tablespaces

Create all application tablespaces with SEGMENT SPACE MANAGEMENT AUTO. This eliminates the segment header freelist contention that was common with older Oracle versions. Manual segment space management has no place in modern Oracle databases.

2. Use Hash or Interval Partitioning for High-Insert Tables

Tables that receive constant high-rate inserts benefit enormously from interval partitioning (which distributes inserts across monthly/weekly/daily partitions). This reduces block-level contention by spreading inserts across many segment headers and insertion zones.

3. Monitor V$WAITSTAT Regularly

Include V$WAITSTAT in weekly DBA reviews. A sudden spike in data block or segment header counts indicates a new hot object has emerged. Catching it early allows proactive tuning before it impacts application response time.

-- Compare V$WAITSTAT snapshots
-- Take a baseline, then compare after a peak period
SELECT
    w1.class,
    w2.count - w1.count AS delta_count,
    w2.time  - w1.time  AS delta_time
FROM (SELECT class, count, time FROM v$waitstat) w1
CROSS JOIN (SELECT class, count, time FROM v$waitstat) w2
WHERE w1.class = w2.class
  AND (w2.count - w1.count) > 0
ORDER BY delta_count DESC;
-- Note: take snapshots at T1 and T2 separately and compare manually

4. Cache Small Hot Tables in the Keep Pool

-- Cache a small frequently-accessed reference table in the Keep buffer pool
-- to prevent its blocks from being evicted and re-read
ALTER TABLE status_codes STORAGE (BUFFER_POOL KEEP);
EXEC DBMS_BUFFER.FLUSH_BUFFER;  -- Force cache population on next access

-- Configure the Keep pool size
ALTER SYSTEM SET db_keep_cache_size = 256M SCOPE=BOTH;

---

Related Wait Events

• db file sequential read — Physical read to bring a block into cache; completes before buffer busy wait ends
• enq: TX - row lock contention — Row-level locking; TX mode 4 waits are actually ITL contention within a block
• latch: cache buffers chains — Latch protecting the buffer hash chain; related to hot block access patterns
• gc buffer busy — RAC equivalent: global cache contention on shared blocks across instances