Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Please help, comment required urgently

Re: Please help, comment required urgently

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Fri, 18 Oct 2002 16:08:21 -0800
Message-ID: <F001.004EDED4.20021018160821@fatcity.com>


Barb,

There's nothing in the STATSPACK report itself that mentions 25% or 16% or whatever, so your math skills are just fine... :-)

---

It is the YAPP report (obtained by uploading STATSPACK report to
full-functionality YAPP processor within www.oraperf.com) that shows these
breakdowns.  The YAPP report first trims out the time belonging to "idle"
wait-events, with the time for the remaining wait-events plus CPU times from
V$SYSSTAT comprising "total response-time".  This total breaks first down
into "CPU Time" (a.k.a. "processing time") and "Wait Time" (a.k.a. remaining
non-idle wait-events).

For the YAPP report resulting from the STATSPACK report provided by George,
the first breakdown of CPU/Wait was roughly 57% vs 43%, if I remember
correctly (I no longer have the email or the report).  Drilling down on the
"CPU Time" (i.e. 57%) provided further breakdown into "Parse Time" (showed
less than 1%), "Recursive CPU Time" (showed less than 1%), and "Other CPU
Time" (showed greater than 99%).  So, 99% of the 57% of "total
response-time" was due to "Other CPU Time".  Drilling down into the details
of "Other CPU Time" showed a "top SQL" report sorted by buffer gets (a.k.a.
logical reads) where one particular SQL statement accounted for about 55% of
all buffer gets.  So, 57% times 99% times 55% equals roughly 25% as I did it
in my head...

...so that's where I got the "25% of total response-time" number from...

Going in the other direction, remember that 43% of total response-time was
"Wait Time".  Drilling down on that showed the "Latch Free" wait-event
consuming about 40% of all wait-time.  Drilling down on the "Latch free"
wait-event showed the "cache buffers chains" latch consuming 99% of all
"latch free" wait-events.  So, 43% times 40% times 99% equals roughly 16%
(again, when I do it in my head)...

...and that's where I got the "16% of total response-time" number from...

---

None of this talk about "breakdowns" and "drilling down" makes any sense
whatsoever unless you are looking at a YAPP report in front of you.  To
obtain one, you first have to register for a free account on
www.oraperf.com.  Once you have obtained a free account, then log in and
follow the instructions to upload the STATSPACK report to the website.  The
website will return an HTML document which is the YAPP report.  It is a
fully hyperlinked document, so my comments above about "drilling down"
should make more sense when you see the report...

Additionally, in the left-hand navigation bar there is a link for "white
papers".  There are only 5-6 white papers currently available, but the first
two are the most useful.  The first is the "YAPP Methodology" paper written
by Anjo Kolk et al.  The second is about using STATSPACK to obtain a YAPP
report, by Bjorn Engsig.  That's pretty much what we've been discussing
here...

---

As Jared had commented earlier, this report is the probably the best thing
since sliced bread.  It is very much a waste of time to attempt to make
heads or tails of the standard STATSPACK or BSTAT/ESTAT reports.  Instead,
upload the text files for those reports up to www.oraperf.com and read a
report that makes sense...

Hope this helps...

-Tim

P.S.    Again, big thanks to Anjo and his friends for all the hard work in
putting together and maintaining this website...

----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Friday, October 18, 2002 12:12 PM



> HELP
> (I'm an idiot)
> OK, never claimed math was my strong suit.
> Why is this 25% of total response time? (Truthfully, I don't really
> understand the column heading "of total.."
>
> Here are the top SQL statements ordered by physical reads per execute:
>
> Statement Executes Physical Reads Hash Value
> Of Total
> Reads /Execute
> SELECT T5.CONFL 903 549033 608.01 3509998681
> 47.31 %
>
> Barb
> >
> > ----------
> > From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Friday, October 18, 2002 7:53 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Please help, comment required urgently
> >
> > 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 -----
> > From: "Leonard, George" < george.leonard_at_farnell.com>
> > To: "Multiple recipients of list ORACLE-L" < ORACLE-L_at_fatcity.com>
> > Sent: Friday, October 18, 2002 2:53 AM
> > Subject: Please help, comment required urgently
> >
> >
> > > 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
> > >
> > > 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: Baker, Barbara
> INET: bbaker_at_denvernewspaperagency.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).
-- 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).
Received on Fri Oct 18 2002 - 19:08:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US