Oracle 'buffer busy waits': hot blocks, sequence headers, and index leaf splits

buffer busy waits fires when a session needs a buffer that another session currently has pinned in the buffer cache. It is a contention signal, not an I/O signal. The waiting session is blocked by a session holding the buffer, not by storage latency.

Since Oracle 10.1 this event has been distinct from read by other session, which fires when a session waits for another session to finish reading a block from disk into cache. Before 10.1 both conditions collapsed into buffer busy waits. Modern Oracle (19c, 23ai) keeps the four-event split: buffer busy waits, read by other session, gc buffer busy acquire, and gc buffer busy release. The last two are RAC-only.

The classic causes are hot-block patterns: right-hand index leaf blocks fed by monotonic sequences, segment headers on manual-freelist tablespaces, undo segment headers, and sequence header blocks in seq$. These are design issues, not capacity issues. Adding memory rarely helps.

What this means

When Oracle reads or modifies a block, it must first pin that buffer in the cache. A pin serializes access. If session B needs the same buffer that session A is modifying or has pinned for a read-into-cache, session B waits on buffer busy waits until A releases the pin. Readers using consistent read (CR) mode do not block writers, and writers do not block readers, so pure SELECT load rarely produces this wait. The contention comes from concurrent DML touching the same block, or from sessions racing to read the same cold block into cache.

V$WAITSTAT is the key diagnostic view. It breaks buffer busy waits down by block class: data block, segment header, undo header, undo block, and others. The dominant class tells you which contention pattern you are dealing with.

V$SESSION exposes the first three wait parameters as P1, P2, and P3; for this event, P1/P2 identify the file and block, while P3 carries the event-specific reason parameter. During active contention you can map file#/block# to a segment via DBA_EXTENTS.

flowchart TD
  A[buffer busy waits rising] --> B[V$WAITSTAT: dominant block class]
  B -->|data/index leaf| C[Right-hand index leaf contention]
  B -->|segment header| D[Non-ASSM freelist contention]
  B -->|undo header| E[Undo segment header contention]
  C --> F[V$SEGMENT_STATISTICS identifies segment]
  F --> G{Monotonic insert key?}
  G -->|yes| H[Reverse-key or hash-partitioned index]
  D --> I[Migrate tablespace to ASSM]

Common causes

Quick checks

All of these are read-only and safe to run during contention.

-- Confirm the wait is significant
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO
FROM V$SYSTEM_EVENT WHERE EVENT = 'buffer busy waits';

-- Break down by block class
SELECT CLASS, COUNT, TIME FROM V$WAITSTAT ORDER BY COUNT DESC;

-- Live waiters: file#, block#, event-specific P3
SELECT SID, EVENT, P1 AS FILE#, P2 AS BLOCK#, P3
FROM V$SESSION
WHERE EVENT = 'buffer busy waits' AND STATE = 'WAITING';

-- Resolve a contended file#/block# to owner.segment
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_EXTENTS
WHERE FILE_ID = :file_id
  AND :block_id BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

-- Per-segment buffer busy waits
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, VALUE
FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'buffer busy waits'
ORDER BY VALUE DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+; for 11g wrap in subquery with ROWNUM <= 20

-- ASSM vs manual freelists
SELECT TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLESPACES;

-- Sequences with small cache under concurrent load
SELECT SEQUENCE_OWNER, SEQUENCE_NAME, CACHE_SIZE
FROM DBA_SEQUENCES WHERE CACHE_SIZE < 1000
ORDER BY CACHE_SIZE;

How to diagnose it

1. Confirm buffer busy waits is more than noise. Compare TIME_WAITED for the event against total DB time. The playbook threshold for a TICKET is more than 5% of total DB time.
2. Query V$WAITSTAT. The dominant CLASS tells you the pattern: data block points at row or index leaf contention; segment header points at freelist or ASSM issues; undo header points at undo segment contention.
3. While contention is live, sample V$SESSION for current waiters and capture P1, P2, and the event-specific P3. Repeat every few seconds to find the persistently hot block.
4. Resolve the hot file#/block# to a segment through DBA_EXTENTS. This identifies whether the contended object is an index, a table, or an undo segment.
5. Cross-check V$SEGMENT_STATISTICS for buffer busy waits ranked per segment. The top segment usually matches the file#/block# you found.
6. If the contended object is an index on a monotonic key (sequence, timestamp, identity column), expect right-hand leaf block contention. Confirm by checking whether inserts dominate the workload on that table.
7. If the contended class is segment header, check DBA_TABLESPACES.SEGMENT_SPACE_MANAGEMENT. MANUAL means old freelist space management; AUTO (ASSM) removes most segment header contention.
8. If undo header dominates, confirm the instance is using automatic undo management and that the undo tablespace is adequate.

Metrics and signals to monitor

Fixes

Right-hand index leaf splits

The right-most leaf block of a B-tree index on a monotonically increasing key (sequence, SYSDATE, identity column) is the only block receiving inserts. Every concurrent insert races for that one block, and every split serializes further.

Options, roughly in order of preference:

• Hash-partition the index. Spreads inserts across N leaf blocks. The most effective fix for very high insert concurrency. Requires the Partitioning option on Enterprise Edition.
• Reverse-key index (REVERSE). Reverses the byte order of the key so inserts land across many leaf blocks instead of the right edge. Tradeoff: the index can no longer support range scans, and the working set expands from the hot right edge to the full index. If the index does not fit in cache, you may trade buffer busy waits for physical I/O waits that are worse.
• Reduce insert rate on that table (application change). Rarely practical.

enq: TX - index contention often appears together with buffer busy waits during splits. Fixing the index layout addresses both.

Sequence header contention

Sequences with the default CACHE (20) force frequent updates to the seq$ row. Under high concurrency on NEXTVAL, sessions contend on the seq$ block, and you may also see enq: SQ - contention or row cache lock on DC_SEQUENCES.

Fix: raise the CACHE size. CACHE 1000 or higher is common for hot sequences. The cost is larger gaps in sequence values after instance failure, since lost cached values are never reused by design. On RAC, NOORDER avoids cross-instance coordination when gaps are acceptable; ORDER guarantees ordering across instances but adds coordination cost.

Segment header contention (non-ASSM)

Segment header contention is a legacy pattern. Manual freelist space management (SEGMENT_SPACE_MANAGEMENT = MANUAL) serializes free-block discovery through the segment header. Multiple freelist groups can reduce contention, but this is legacy tuning.

The real fix is to move the segment to an ASSM tablespace (SEGMENT_SPACE_MANAGEMENT = AUTO), which uses bitmap blocks instead of freelists. ASSM has been the default for locally managed tablespaces since 9i. If you are still on MANUAL freelists in production, that is itself worth fixing.

One caveat: under extreme concurrent insert load, ASSM bitmap blocks (BMBs) can themselves become hot, shifting contention from segment header to BMB class in V$WAITSTAT. This is rare but worth knowing when ASSM does not fully resolve the problem.

Undo segment header contention

Undo header contention means concurrent transactions are competing for undo segment headers. On automatic undo management (UNDO_MANAGEMENT = AUTO, introduced in 9i and the default for 11g+ newly installed databases), Oracle manages undo segments dynamically and this contention is uncommon. If you see it persistently, verify AUM is enabled and that the undo tablespace is not undersized.

Prevention

• ASSM tablespaces for all user segments. Manual freelists are legacy and a known source of segment header contention.
• Size sequence caches for the workload. The default CACHE of 20 is wrong for any sequence driving concurrent inserts.
• Design indexes for spread at the start. Hash-partitioned or reverse-key on high-concurrency insert tables with monotonic keys. Retrofitting under load is harder.
• Trend V$WAITSTAT. buffer busy waits only during cache warmup after restart is normal; persistent growth during steady state is not.
• Partition RAC workload by service. Without service affinity the same hot blocks are touched from every instance, turning local contention into gc buffer busy waits.

How Netdata helps

• Per-second collection of buffer busy waits and other V$SYSTEM_EVENT events shows contention onset immediately, not at the next AWR snapshot boundary.
• Correlate buffer busy waits with TPS, redo generation rate, and active session count to determine whether the wait is throttling throughput or is background noise.
• The V$WAITSTAT block-class breakdown appears as separate dimensions, so you can distinguish data block contention from segment header or undo header contention without ad-hoc SQL during the incident.
• Alert on buffer busy waits as a percentage of DB time against the 5% threshold, rather than treating it as a reactive debug task.
• ML anomaly detection flags spikes in buffer busy waits or enq: TX - index contention even when absolute values are low.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle ‘cursor: pin S wait on X’: mutex contention on hot cursors
• Oracle ‘db file scattered read’: multiblock reads, full scans, and plan regressions
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle hard parse storm: literal SQL, bind variables, and shared pool churn