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" <[EMAIL PROTECTED]> 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 ----- > [EMAIL PROTECTED]> > To: "Multiple recipients of list ORACLE-L" < <mailto:ORACLE-L@;fatcity.com> > [EMAIL PROTECTED]> > 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:[EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
