Buffer busy wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is “busy”. The two main cases where this can occur are:

1. Another session is reading the block into the buffer
2. Another session holds the buffer in an incompatible mode to our request

Finding the blocking process can be quite difficult as the information required is not externalized. If P3 (Reason Code) (Column P3 of v$session_wait) shows that the “buffer busy wait” is waiting for a block read to complete then the blocking session is likely to be waiting on an IO wait (eg: “db file sequential read” or “db file scattered read”) for the same file# and block#. And If the wait is due to the buffer being held in an incompatible mode then it should be freed very soon.

As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that “buffer busy waits” for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) – the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk – the other sessions (scanning the same index) quickly ‘catch up’ and want the block which is currently being read from disk – they wait for the buffer as someone is already reading the block in.

The following hints may be useful for particular types of contention – these are things that MAY reduce contention for particular situations: