buffer busy waits
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Jul 2005 15:47:45 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
