Re: buffer busy waits

Brandon,

When you have a reason code of 130, your buffer busy wait sessions are
waiting on other sessions which are reading from disk.  I'm willing to bet
your bbw issue is closely tied with the db file sequential reads you're
experiencing (your second highest wait).  First, the general caveat-- if
your users aren't experiencing response issues and no one's complaining,
then don't worry about it.  But I imagine that, if you're seeing 3 seconds
of bbw and 2+ seconds of dfsr per elapsed second, your users are suffering.

See if the db file sequential read waits are on the same table/indexes as
the bbw waits (very likely).  If so, your "hot block" problem is compounded
by having to get the hot blocks off disk.  So you need to reduce your I/O
requirements.  The solution could involve SQL tuning, increasing your buffer
cache, forcing the problem table or its indexes to stay in cache (keep
pool), or just executing SQL less frequently.  The fastest things to do are
increasing the buffer cache and putting the table/indexes into a properly
sized keep pool, but the appropriateness of these steps will depend on your
setup, and it's unlikely that you're going to keep a 5GB table in the buffer
cache.  But the buffer busy waits are on blocks needed by more than one
session, so the more of the table or its indexes you keep in cache the
better.  Maybe the indexes in cache would take care of the problem.  I'd
guess that reducing the disk reads will also reduce or
eliminate your buffer busy waits.

I described a situation similar to this in a paper a while back
(http://www.dbspecialists.com/presentations/wait_events2.html), but the
actual solution in that case was specific to the situation there; it
wouldn't necessarily apply in your case.  But the analysis of the problem
would be similar.

--Terry

----- Original Message ----- 

I have a 9.2.0.6 database with the following top 5 timed events from
statspack over a period of 396 minutes:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s) Ela
Time
-------------------------------------------- ------------ ----------- ------
--
buffer busy waits                               8,911,867      77,134
37.62
db file sequential read                         3,991,319      59,535
29.03
CPU time                                                       32,274
15.74
latch free                                      5,410,288      23,786
11.60
log file sync                                     728,532       6,620
3.23

Almost all the 'buffer busy waits' are on the class 'data blocks', and from
what I've seen in v$session_wait, and v$segment_statistics, they are mostly
on one table (and its indexes), which is by far the largest in the database
at 5GB, and they have a reason code (p3) of 130, which means "Block is being
read by another session . . .".

I've RTFM and all the usual good sources (JLComp,Ixora,Asktom,Metalink) and
found suggestions for increasing freelists and using reverse-key indexes
when 'buffer busy waits' are the result of concurrent inserts, but I can't
find any good suggestions for how to handle this when it's the result of
concurrent disk read attempts.  It seems to me that the disk subsystem is
saturated and the only way to resolve this is to increase the throughput
(stripe across more disks, faster disks, more cache, etc.) and/or reduce the
I/O requirements by SQL/schema tuning.  I've already identifed the top SQL
statements and am working on tuning them.  Any other ideas?  Please let me
know if you'd like more specific info to help make a better informed
assessment.


--
http://www.freelists.org/webpage/oracle-l

Other related posts: