Re: buffer busy waits

From: Terry Sutton <terrysutton_at_usa.net>
Date: Sun, 31 Jul 2005 10:57:06 -0700
Message-ID: <016901c595f9$54c1ceb0$6700a8c0@TerrySutton>

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 Received on Sun Jul 31 2005 - 12:59:31 CDT