Causes of Oracle Buffer Busy Waits

One of the most confounding problems with Oracle is the resolution of buffer busy wait events. Buffer busy waits are common in an I/O-bound Oracle system, as evidenced by any system with read (sequential/scattered) waits in the top-five waits in the Oracle STATSPACK report, like this:

The columns of the v$session_wait view that are of particular interest for a buffer busy wait event are:

• P1:  The absolute file number for the data file involved in the wait.
   
• P2:  The block number within the data file referenced in P1 that is being waited upon.
   
• P3:  The reason code describing why the wait is occurring.

Here's an Oracle data dictionary query for these values:

select
   p1 "File #",
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where
   event = 'buffer busy waits';

If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should show the name and type of the segment:

select 
   owner,
   segment_name,
   segment_type
from 
   dba_extents
where 
   file_id = &P1
and 
  &P2 between block_id and block_id + blocks -1;

Once the segment is identified, the v$segment_statistics performance view facilitates real-time monitoring of segment-level statistics. This enables a DBA to identify performance problems associated with individual tables or indexes, as shown below.

We can also query the dba_data_files to determine the file_name for the file involved in the wait by using the P1 value from v$session_wait for the file_id.

Interrogating the P3 (reason code) value from v$session_wait for a buffer busy wait event will tell us why the session is waiting. The reason codes range from 0 to 300 and can be decoded.
 

Reason codes

As I mentioned at the beginning of this article, buffer busy waits are prevalent in I/O-bound systems. I/O contention, resulting in waits for data blocks, is often due to numerous sessions repeatedly reading the same blocks, as when many sessions scan the same index.

In this scenario, session one scans the blocks in the buffer cache quickly, but then a block has to be read from disk. While session one awaits the disk read to complete, other sessions scanning the same index soon catch up to session one and want the same block currently being read from disk. This is where the buffer busy wait occurs?waiting for the buffer blocks that are being read from disk.

The following rules of thumb may be useful for resolving each of the noted contention situations: 

• Data block contention:  Identify and eliminate HOT blocks from the application via changing PCTFREE and or PCTUSED values to reduce the number of rows per data block. Check for repeatedly scanned indexes. Since each transaction updating a block requires a transaction entry, increase the INITRANS value.
   
• Freelist block contention:  Increase the FREELISTS value. Also, when using Parallel Server, be certain that each instance has its own FREELIST GROUPs.
   
• Segment header contention:  Again, increase the number of FREELISTs and use FREELIST GROUPs, which can make a difference even within a single instance.
   
• Undo header contention:: Increase the number of rollback segments.

Mark Bobak notes that buffer busy waits with P3=0 indicate disk I/O contention, indicating the freelists will not improve concurrency:

"buffer busy wait w/ P3=0 means the buffer is locked because the contents are being read from disk by another session. (See MOSC Doc ID 34405.1 for more details.)

This is most likely caused by multiple, concurrent sessions that are reading the same table or set of tables. In my experience, it's most often due to multiple, concurrent queries doing full table scans on the same table.

Since this a read concurrency problem, changing freelists will NOT help."

 In these cases, buffering-up the tables (e.g. KEEP pool), or using faster storage (SSD) can remove this disk enqueue wait events.