Why Oracle Buffer Busy Waits Happen and How to Fix Them

Understanding Buffer Busy Waits in Oracle

When a process requests a buffer from the SGA, it first obtains the cache buffers chains latch and traverses the buffer chain to locate the required buffer header. Depending on the operation (read or write), it then tries to acquire a shared or exclusive buffer pin or buffer lock on that header.

If the pin is obtained, the latch is released and the process works on the buffer block. If the pin cannot be obtained, the process enters a buffer busy waits event and waits.

Typical Situations That Trigger Buffer Busy Waits

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

The block needed by the session is not coordinated with other sessions’ queries, causing contention.

Hot blocks: multiple sessions simultaneously operate on the same block, making the block heavily contested.

Because Oracle stores data in blocks, a block that contains many rows increases the chance that several sessions will target the same block, leading to hot‑block contention.

Freelist, PCTFREE and PCTUSED Parameters

Oracle decides whether a block can accept new rows based on its free space, controlled by pctfree and pctused. For example, with pctfree=10 and pctused=40, a block becomes read‑only for inserts when its space usage reaches 90 % (100 - pctfree). The remaining 10 % is reserved for updates. When a block’s usage exceeds pctused and it resides on the freelist header, the block is removed from the freelist (UNLINK). After enough rows are deleted so that usage falls below pctused, the block is re‑added to the freelist.

The freelist is essentially a list of blocks eligible for inserts; blocks removed from it cannot receive new rows until they become eligible again. Reducing pctused or increasing the number of freelists spreads rows across more blocks, lowering the probability of hot‑block formation and improving concurrency.

Common Wait Event Codes

Buffer busy waits are often associated with wait codes 130 and 220 . Codes below 200 generally indicate I/O‑related waits.

Contention Types and Mitigation Strategies

Code 130 – Block‑level contention Multiple sessions request the same block that is not currently cached, forcing a disk read. Oracle optimizes by allowing only one session to perform the actual I/O while the others wait on buffer busy waits . Check v$session for matching file and block numbers in db file sequential read or db file scattered read events. Solution: Tune SQL to reduce logical and physical reads.

Code 220 – DML contention on the same block Multiple sessions modify different rows within the same block, especially when the block size is large (≥16K). Solutions: Reduce concurrency. Decrease the number of rows per block. Rebuild the object in a tablespace with a smaller block size. Use larger PCTFREE and smaller PCTUSED when rebuilding tables or indexes. Execute alter table <table_name> minimize records_pre_block to minimize rows per block. From Oracle 9i onward, move or rebuild objects in a tablespace with a smaller block size. Note: These changes may increase full‑table scan time and consume more disk space.

Data Segment Header Contention (Class #4) High activity on a table or index segment header (not UNDO) indicates frequent updates to freelist information or high‑water‑mark (HWM) extensions. Mitigation: Increase FREELISTS and FREELIST GROUPS for tables using free‑list segment management. Ensure a reasonable gap between PCTFREE and PCTUSED to minimize block cycling. Avoid overly small extents; consider moving objects to locally managed tablespaces with uniform extent sizes.

Undo Segment Header Contention (Class #17) Insufficient or too‑small undo segments cause frequent updates to the undo segment header. Solution: Add more undo segments. In manual management mode, increase the rollback_segments initialization parameter; in automatic mode, lower transactions_per_rollback_segment to let Oracle create more undo segments automatically.

segment，若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量

Undo Block Contention (Class #18) Multiple concurrent sessions read the same undo block to maintain read consistency. Solution: Stagger application write operations and heavy query workloads to reduce simultaneous access.

解决办法是错开应用程序修改数据和大量查询数据的时间.

While these techniques can significantly reduce buffer busy waits, they may introduce trade‑offs such as increased I/O, larger storage consumption, or longer full‑table scans.