Oracle Metric Buffer Busy Waits

Oracle Consulting Tips by Burleson

The Buffer Busy Waits Oracle metric occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:

The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

Because buffer busy waits are due to contention between particular blocks, there's nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.

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:

Top 5 Timed Events
                                                          % Total
Event                         Waits        Time (s)     Ela Time
--------------------------- ------------ ----------- -----------
db file sequential read       2,598        7,146           48.54
db file scattered read       25,519        3,246           22.04
library cache load lock         673        1,363            9.26
CPU time                      2,154          934            7.83
log file parallel write      19,157          837            5.68

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.

The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes.

This, and many other Oracle performance metrics are discussed in my book "Oracle Tuning" by Rampant TechPress. You can buy it directly from the publisher and save 30% at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

You may also check out this website containing information on fixing the buffer waits busy metric:

http://www.remote-dba.net/oracle_10g_tuning/t_buffer_busy_waits.htm