buffer busy waits

Possible Causes :
· Buffer busy waits are common in an I/O-bound Oracle system.
· The two main cases where this can occur are:
· 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: i 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