RE: Please help, comment required urgently

Thanks for your contributions on this Tim (and on everything else you respond to).
These little tips of how to analyze statspack reports properly all add up and whilst I did look at the report and I did glean some of you what suggested I certainly did not pick up all that you spotted  

John  

 -----Original Message-----
Sent: 18 October 2002 14:54
To: Multiple recipients of list ORACLE-L

George,  

Two things jump out together:

• The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period...
• Waits on the "cache buffers chains" are consuming another 16% of total response-time

With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important...  

Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or inefficient index for a long, long, long range-scan operation, which is racking up all of those buffer gets. What do you expect from the rule-based optimizer? If you were running CBO and this happened, I'd suggest gathering column-level "histogram" statistics on the table. My guess also is that many concurrent users are running this statement during the course of the sample period, causing the latch contention for cache buffers in the Buffer Cache, thus the relationship between the two symptoms?  

I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48; don't know what CPU_COUNT is, but obviously this change has had zero impact on the latch contention problem. Tuning the SQL will fix the problem; accomodating the problem by configuring more latches has no impact.  

Tuning that one SQL statement (plus a few of it's look-alikes, also listed in the report) will resolve the major performance issues you are experiencing. In fact, it will have a miraculous impact...  

Hope this helps...  

-Tim  

• Original Message ----- george.leonard_at_farnell.com> To: "Multiple recipients of list ORACLE-L" < <mailto:ORACLE-L_at_fatcity.com> ORACLE-L_at_fatcity.com> Sent: Friday, October 18, 2002 2:53 AM

> Hi guys, I need a second opinion on the following Statspack output, I got
my
> suspicions but my manager and the client is not buying what I am say,
>
> Not knowing anything of the system architecture please look at the output
> and say what would concern you. What assumptions/recommendations you would
> make.
>
> Thx
>
>
>
> George
> ________________________________________________
> George Leonard
> Oracle Database Administrator
> Dimension Data (Pty) Ltd
> (Reg. No. 1987/006597/07)
> Tel: (+27 11) 575 0573
> Fax: (+27 11) 576 0573
> E-mail:george.leonard_at_za.didata.com
> Web: <http://www.didata.co.za> http://www.didata.co.za
>
> You Have The Obligation to Inform One Honestly of the risk, And As a
Person
> You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
Kill
> or Injure Themselves as They See Fit!
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: John.Hallas_at_vodafone.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).