Wait Event: buffer busy waits

Wait Event: buffer busy waits

Possible Causes:

• Buffer busy waits are common in an I/O-bound Oracle system.
• The two main cases where this can occurare:
• Another session is reading the block into the buffer
• Another session holds the buffer in an incompatible mode to our request
• These waits indicate read/read, read/write, or write/write contention.
• The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.
• This wait can be intensified by a large block size as more rows can be contained within the block
• This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy
• It is also often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index or data block)

Actions:

• The main way to reduce buffer busy waits is to reduce the total I/O on the system
• Depending on the block type, the actions will differ

    Data Blocks:

• Eliminate HOT blocks from the application.
• Check for repeatedly scanned / unselective indexes.
• Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.
• Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes).
• Increase INITRANS and MAXTRANS and reduce PCTUSED This will make the table less dense .
• Reduce the number of rows per block
• Segment Header:Increase of number of FREELISTs and FREELIST GROUPs
• Undo Header:Increase the number of Rollback Segments

Ref:
A process that waits on the buffer busy waits event publishes the reason code in the P3 parameter of the wait event.

The Oracle Metalink note # 34405.1 provides a table of reference - codes 130 and 220 are the most common.

Resolving intense and random buffer busy wait performance problems. Note# 155971.1