Re: Please help, comment required urgently

To give credit where credit is due...

I've never bothered to learn how to interpret a STATSPACK report; I've left that with what little I ever knew about interpreting a BSTAT/ESTAT report. Rather, I just send the report to the YAPP report post-processor at www.oraperf.com and it formats everything in such a way that things just jump out at you. It is quite instructive to de-construct the resulting YAPP report back to the original STATSPACK and BSTAT/ESTAT report (and thence further back to the originating V$ views).

Big-time kudos to Anjo and friends for creating and maintaining this invaluable resource!!!

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, October 18, 2002 8:34 AM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

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