Buffer busy waits

/ deepak123cse

Buffer busy wait happens when a session wants to access a database block in the buffer cache but it cannot because the buffer is busy. Another session is modifying the block and the contents of the block are in flux during the modification. To guarantee that the reader has a coherent image of the block with either all of the changes or none of the changes, the session modifying the block marks the block header with a flag letting other users know a change is taking place and to wait until the complete change is applied.

The two main cases where this wait can occur are:

  • Another session is reading the block into the buffer
  • Another session holds the buffer in an incompatible mode to our request

While the block is being changed, the block is marked as unreadable by others. The changes that are being made should last under a few hundredths of a second. A disk read should be under 20 milliseconds and a block modification should be under one millisecond. Therefore it will take a lot of buffer busy waits to cause a problem.

However, in a problem situation, there is usually a hot block, such as the first block on the free list of a table, with high concurrent inserts. All users will insert into that block at the same time, until it fills up, then users start inserting into the next free block on the list, and so on.

Another example of a problem is of multiple users running full table scans on the same large table at the same time. One user will actually read the block physically off disk, and the other users will wait on Buffer Busy Wait for the physical I/O to complete.

The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won’t help.

The resolution of a “buffer busy wait”  events is one of the most confounding problems with Oracle.  In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.

Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes.  Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.

Increasing the db_cache_size parameter would not necessarily fix this problem.

Point of action is give below,

Look at v$waitstat (or the buffer busy drill down page) and determine the block type with the highest waits.

Block Type and Action:

  • Undo Header – Use Automatic Undo Management (AUM) or add more RBS segments)
  • Undo Block – Use AUM (or increase RBS sizes)
  • Data Block – First determine if it is an I/O problem. The Buffer Busy Waits drill-down page should provide this information. Otherwise, sample from v$session_wait 
    SELECT p3, count(*) 
  FROM v$session_wait 
  WHERE event='buffer busy wait'
;

    If p3 is less than 200 then it is an I/O problem. Either improve I/O performance or change application. Applications running concurrent batch jobs that do full table scans on the same large tables run into this problem.

  • Free List – Use ASSM (or freelists groups)

Source : http://logicalread.solarwinds.com/oracle-buffer-busy-wait-event/

Leave a comment