The problem is that this rapid buffer access puts an incredible high concurrency requirement on the buffer's internal structures. When the internal structures are being changed, the block will be unavailable to other processes, issuing the buffer busy waits event. Because changing the architecture of the application is usually not a realistic option (but should be considered), creatively finding ways to spread the popular row information or popular rows is a solid approach. For example, if each row contains an application sequence number (like the next check number), then move the rows into their own blocks, add a large fixed-length column, or set pct_free to a very high value to keep the number of rows to a minimum. This solution is painful, but not as painful as continual buffer busy waits.

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!