buffer busy waits

This 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 (and the session is waiting for that read to complete)
2. Another session holds the buffer in an incompatible mode to our request (that is, some other session is changing the buffer).

Wait Time:

Normal wait time is 1 second. If the session was waiting for a buffer during the last wait, then the next wait will be 3 seconds.

select *
  from v$datafile
 where file# = file#;

select name, kind
  from ext_to_obj_view
 where file#  = file#
   and lowb  <= block#
   and highb >= block#;

Finding Blockers:

Finding the blocking process can be quite difficult as the information required is not externalized. If P3 parameter value (which is reason code) 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#.

If the wait is due to the buffer being held in an incompatible mode then it should be freed very soon. If not then it is advisable to contact Oracle Support and get 3 SYSTEMSTATE dumps at one minute intervals as the blocking session may be spinning. (Look for ACTIVE sessions with high CPU utilization).

Systemwide Waits:

If the TIME spent waiting for buffers is significant then it is best to determine which segment/s is/are suffering from contention. The "Buffer busy wait statistics" section of the Bstat/estat or STATSPACK reports shows which block type/s are seeing the most contention. This information is derived from the view V$WAITSTAT which can be queried in isolation:

SELECT time, count, class
  FROM V$WAITSTAT
 ORDER BY time,count
;

This shows the class of block with the most waits at the BOTTOM of the list. Oracle Support may also request that the following query be run to show where the block is held from when a wait occurs:

SELECT kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT"
  FROM x$kcbsw s, x$kcbwh w
 WHERE s.indx=w.indx
   and s."OTHER_WAIT">0
 ORDER BY 3

Note: "OTHER_WAIT" is "OTHER WAIT" in Oracle8i (a space rather than an underscore) The following table lists the most commonly seen buffer busy waits and the operations/functions causing the buffer busy waits.

Additional information regarding which files contain the blocks being waited for can be obtained from the internal view X$KCBFWAIT:

SELECT count, file#, name
  FROM x$kcbfwait, v$datafile
 WHERE indx + 1 = file#
 ORDER BY count
;

This shows the file(s) with the most waits (at the BOTTOM of the list). By combining the above information we know what block type(s) in which file(s) are causing waits. The segments in each file can be seen using a query such as:

SELECT distinct owner, segment_name, segment_type
  FROM dba_extents
 WHERE file_id= &FILE_ID
;

If there are a large number of segments of the type listed then monitoring V$SESSION_WAIT may help isolate which object is causing the waits.

For example, repeatedly run the following statement and collect the output. After a period of time sort the results to see which file & blocks are showing contention:

SELECT p1 "File", p2 "Block", p3 "Reason"
  FROM v$session_wait
 WHERE event='buffer busy waits'
;

Note: In the above query there is no reference to WAIT_TIME as we are not interested in whether a session is currently waiting or not, just what buffers are causing waits.

If a particular block or range of blocks keeps showing waits you can try to isolate the object.

Reducing Waits / Wait times:

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:

The following statistics are related to buffer cache and can be used to investigate the buffer busy waits related issues.

• db_block_gets
• consistent_gets
• physical_reads
• physical_writes
• write_requests
• summed_dirty_queue_length
• db_block_changes
• change_write_time
• consistent_changes
• redo_synch_writes
• redo_synch_time
• exchange_deadlocks
• free_buffer_requested
• dirty_buffers_inspected
• free_buffer_inspected
• DBWR_timeouts
• DBWR_make_free_requests
• DBWR_free_buffers_found
• DBWR_lru_scans
• DBWR_summed_scan_depth
• DBWR_buffers_scanned
• DBWR_checkpoints
• DBWR_flush_block-range
• DBWR_cross_instance_writes
• remote_instance_undo_writes
• remote_instance_undo_requests
• cross_instance_CR_read
• recovery_blocks_read
• recovery_array_reads
• recovery_array_read_time
• CR_blocks_created
• Current_blocks_converted_for_CR