Re: oracle-l Digest V2 #217

Brandon,

I'd say that tuning individual SQL statements is of such importance that you're better off not tweaking anything at the database level until you are finished with with the SQL statements in question. Of course it's well worth while to fix anything you find to be clearly poorly configured, but I wouldn't go beyond that until the code you're running is efficient.

Scott

> ----- 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