Buffer Busy Waits

JL Computer Consultancy

There are several reasons why a session may see a “buffer busy waits” event, and over time this note will accumulate a description of all the reasons, with examples of how they can be produced.  (Ed: The intention of the Dizwell Wiki, started in 2006, was to allow different people to expand and enhance notes – unfortunately the level of contribution was low: it will be interesting to see if the recent (2008) Oracle Wiki will fare any better).

The wait event buffer busy waits has three parameters,

• p1 = (absolute) file number of the block waited for
• p2 = block number within file of the block waited for
• p3 = reason code for the wait (missing in 8.1.5)

The reason codes changed between versions 8.0.6 and to 8.1.6 and only the newer 3-digit reason codes are mentioned below. For a cross reference of the old and new values, you could check Metalink note 34405.1.

If a session wants to examine a data block, the first step is to check if it is in memory. The commonest sequence of events is:

1. Calculate the hash bucket/chain id (using the tablespace, file, and block numbers, and block type).
2. Grab the relevant cache buffers chains latch
3. Search the hash bucket/chain
4. Pin the buffer header if found
5. drop the latch
6. Use the buffer (if it isn’t held by another session in an incompatible mode)
7. Grab the latch
8. Unpin the buffer header
9. Drop the latch

To pin a buffer, a session creates a buffer handle and links it in to the holders list (visible in the columns x$bh.us_nxt and x$bh.us_prv) on the buffer header.

Buffer busy waits occur at the step - “pin the buffer header”.

Waiting for other session to read a block (p3 = 130)

In this case, the search of the hash bucket shows that the buffer is not in memory. The session therefore has to acquire an empty buffer, attach it to the hash chain, set the buffer header to point at this buffer, pin the buffer header in exclusive mode, then drop the latch. At this point the session can read the block from disc into the pinned buffer.

If a second session now wants to use the same block, it will calculate the correct hash chain, grab the latch, search for and find the correct buffer header. However, the buffer header will be pinned in exclusive mode (and the buffer may not yet be full), so the second session will create a pin structure, but attach it to the buffer header on the waiters list (visible in the columns x$bh.wa_nxt and x$bh.wa_prv). Then it drops the latch, and goes into the “buffer busy waits” state.

When a session is showing “buffer busy waits” as its wait state this is essentially what it means - the session has a pin structure in the waiters list of a buffer header.

The “wait for other session to finish a read” is such a common reason for a “buffer busy waits” event that you should no longer see it in 10g, as it has been isolated from the rest of the buffer busy waits and been given its own special wait event: “read by other session”. Unfortunately this change has not been reflected in the events recorded in v$segstat.

To see this event, all you need do usually is run a disk intensive query from several sessions simultaneously. For example:

  select source from sys.source$ where source = 'xxxx';

This will do a tablescan of something that is a fairly large table but won’t return any data (probably).

To address buffer busy waits with p3 = 130, the primary strategy is to identify which object is subject to heavy, concurrent, I/O (v$segstat is very useful for this from 9i) and do something to reduce the requirement for reads (possibly refining the indexing used) or to increase the probability of the blocks being buffered. It is possible that the problem is appearing because of slow reads due to disc hot spots rather than excessive numbers of reads, so there may be some scope to limit the damage by finding ways of spreading the I/O load more evenly.

Back to Index of Topics