Multiple busy table blocks are by far the most likely buffer busy waits (pre-Oracle Database 10g) and read by other session (Oracle Database 10g and later) wait event situation. In this case, each time we check for busy buffers (for example, Figure 6-25), they are different, the busy buffers are data segments, and they are not header blocks. When this situation occurs, the cause is usually centered around queries or a mix of DML and queries.

For pure query situations, what is happening is multiple sessions require the same block, and that same block does not reside in the buffer cache. The first session that makes a call to the IO subsystem will post either a db file sequential read or db file scattered read wait event. It would be silly for the other sessions to also issue an IO call for the same block, so they wait for the first session to complete not only the IO call, but also for the block to be properly placed into the buffer cache. Then the other sessions can access the buffer just like any other session. While the other sessions are waiting for the first session to complete, starting in Oracle Database 10g, the waiting sessions will post a read by other session wait event; for prior releases, the waiting sessions will post a buffer busy waits event. So truly, the session is waiting for the read by the other session to complete!

The solutions are very straightforward and centered around increasing the likelihood of blocks residing in the buffer cache. If the buffer were in the buffer cache, then this busy buffer wait situation would not exist. So think of ways to increase the likelihood of finding buffers in the buffer cache.