Re: oracle-l Digest V2 #217

From: Scott Swank <scott.swank@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Mon, 1 Aug 2005 08:19:49 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Re: oracle-l Digest V2 #217

Other related posts:
