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.
