Database administration: Causes of Oracle buffer busy waits

[From Builder.com]

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 article continues at

http://builder.com.com/5100-6388-5110875.html?tag=sc