buffer busy waits

Hi everyone and happy sysadmin day :) (www.sysadminday.com)

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.

Thanks,
Brandon

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 29 2005 - 17:48:29 CDT