Why Do Oracle Buffer Busy Waits Occur and How to Resolve Them?

Understanding Buffer Busy Waits

Oracle processes acquire buffers from the SGA by first obtaining the cache buffers chains latch, traversing the buffer chain to locate the desired buffer header, and then acquiring a shared or exclusive pin/lock on that header. If the pin cannot be obtained, a buffer busy wait event is generated.

When Do Buffer Busy Waits Occur?

Another session is reading the same block, so the current session must wait for the read to finish.

Sessions hold conflicting buffers that are not coordinated.

Hot blocks appear when many sessions concurrently operate on the same block, making the block busy.

Because Oracle stores data in blocks, multiple rows per block increase the chance that several sessions will contend for the same block.

Freelist, PCTFREE and PCTUSED

Oracle uses PCTFREE and PCTUSED to control when a block can accept new rows. With PCTFREE=10 and PCTUSED=40, a block stops accepting inserts when its space usage reaches 90 % (100‑PCTFREE). The block is then removed from the freelist. When deletions reduce usage below the PCTUSED threshold (e.g., below 40 %), the block is returned to the freelist.

Increasing the number of freelists and lowering PCTUSED reduces the amount of data stored per block, which can improve concurrency by decreasing hot‑block formation.

Common Wait Event Codes

Two wait‑event codes are most frequently seen:

Code 130 : Multiple sessions request the same block that is not in the buffer cache, causing Oracle to serialize the I/O so only one session performs the disk read while others wait on buffer busy waits .

Code 220 : Multiple sessions execute DML on different rows of the same large block (≥16 KB), intensifying contention.

Typical mitigation includes optimizing SQL to reduce logical and physical reads, rebuilding tables or indexes with larger PCTFREE and smaller PCTUSED, or moving objects to tablespaces with smaller block sizes.

Other Contention Types

Buffer busy waits can also concentrate on segment headers (data segment, undo segment) or undo blocks. Solutions involve increasing the number of freelists, adjusting PCTFREE / PCTUSED gaps, enlarging segment sizes, or adding more undo segments and adjusting related initialization parameters.

Diagnostic Queries

To investigate the root cause, collect the following information:

Identify the wait‑event reason code via the p3 column of the event.

Retrieve the SQL text causing the wait:

select sql_text from v$sql t1, v$session t2, v$session_wait t3 where t1.address = t2.sql_address and t1.hash_value = t2.sql_hash_value and t2.sid = t3.sid and t3.event = 'buffer busy waits';

Determine the block type and segment involved:

select 'Segment Header' class, a.segment_type, a.segment_name, a.partition_name from dba_segments a, v$session_wait b where a.header_file = b.p1 and a.header_block = b.p2 and b.event = 'buffer busy waits' union select 'Freelist Groups' class, a.segment_type, a.segment_name, a.partition_name from dba_segments a, v$session_wait b where a.header_file = b.p1 and b.p2 between a.header_block+1 and (a.header_block + a.freelist_groups) and a.freelist_groups > 1 and b.event = 'buffer busy waits' union select a.segment_type||' block' class, a.segment_type, a.segment_name, a.partition_name from dba_extents a, v$session_wait b where a.file_id = b.p1 and b.p2 between a.block_id and a.block_id + a.blocks - 1 and b.event = 'buffer busy waits' and not exists (select 1 from dba_segments where header_file = b.p1 and header_block = b.p2);

These queries help pinpoint whether the contention is on data blocks, freelist groups, or specific segment headers.

To reduce undo segment contention, increase the number of undo segments or adjust transactions_per_rollback_segment in automatic management mode.