Oracle Scratchpad

December 27, 2006

Analysing Statspack (2)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 11:16 pm GMT Dec 27,2006

[Further Reading on Statspack]

One of the important things to know about the standard statspack report is where not to look. Here’s an example:

Instance Efficiency Percentages (Target 100%)
            Buffer Nowait %:  100.00       Redo NoWait %:    100.00
            Buffer  Hit   %:  100.00    In-memory Sort %:    100.00
            Library Hit   %:   99.96        Soft Parse %:     99.00
         Execute to Parse %:   98.02         Latch Hit %:    100.00
Parse CPU to Parse Elapsd %:   92.74     % Non-Parse CPU:     98.56       

The Instance Efficiency summary (note especially the indication that 100% is the ideal in all cases) is essentially useless. At least, it is useless in isolation if you run off the occasional report trying to spot problems.

What does the summary above tell you ? We keep finding data in memory, all our sorts are in memory, most of our parses are “soft” parses, we keep finding the objects we need in the library cache, we get lots of executions from each parse, and very little of our CPU is used for parsing. 

The only hint of a threat comes from the 92.74% Parse CPU to Parse Elapsed, we are losing a little time whilst parsing – but does it really matter since parsing is such a small fraction of the work we’re doing ?

In fact, this instance is overloaded with terrible response time. It’s suffering from three major design flaws, and they are hiding each other. There is a hint of a clue – and it really is the 92.74%: how can you lose time on parsing – one possibility is by burning CPU like crazy doing other work.

Always remember: percentages (or ratios) hide the scale. A hard parse percentage of 100% (every parse call is a hard parse) isn’t necessarily a bad thing if you are only doing one parse call per second. A soft parse percentage of 100% (none of the parse calls turn into hard parses) may be a disaster if you are doing 10,000 parse calls per second – except they may still be recorded as “parse calls” despite being redirected through the session cursor cache. A ratio of 1,000 executes per parse (99.9%) is not good if 900 of those executes are actually a pointless waste of effort. [See also my note on Parse Calls]

If you do want to glance at the Instance Efficiency Percentages make sure you take a few extra moments to look closely at the Load Profile section just above it, especially at the “per second” figures, to decide if the amount of work your instance is doing is reasonable for the tasks you have to perform.

And finally always remember the law of averages: if you put your head in an ice bucket and your feet in a fire, on average you should be perfectly comfortable.

[Further Reading on Statspack]

Footnote: Any advice about reading Statspack reports is almost always relevant when reading AWR reports (and vice versa).


  1. “And finally always remember the law of averages: if you put your head in an ice bucket and your feet in a fire, on average you should be perfectly comfortable.”

    I don’t know about that. Fire should have a temperature of >233 degrees (Celsius, 451 Fahrenheit for the benefit of our US friends) while the ice bucket has a temperature of ~ 0 (Celsius, 32 Fahrenheit). That would result in an average of > 100 degrees (212 Fahrenheit). Hardly comfortable.


    Comment by Wolfgang Breitling — December 28, 2006 @ 3:59 pm GMT Dec 28,2006 | Reply

  2. Wolfgang, It also depends on how much of your head is in the ice bucket and the amount of time it has been left there to cool down; whether you’re averaging by body mass, volume, or length; etc. etc. etc. Maybe a histogram would be in order.

    Comment by Jonathan Lewis — December 28, 2006 @ 6:46 pm GMT Dec 28,2006 | Reply

  3. Is that the origin/rationale of “Cooler heads will prevail” ?

    Comment by Wolfgang Breitling — December 28, 2006 @ 6:49 pm GMT Dec 28,2006 | Reply

  4. […] The similarly-named An Expert’s Guide to Oracle Technology has a series on Oracle Streams. This week part 2 describes how to Send CDC [Change Data Capture] Data to 9i Jonathan Lewis‘ Oracle Scratchpad scratches out a second part to his Analysing Statspack. […]

    Pingback by Sheeri Kritzer » Blog Archive » Log Buffer #25: a Carnival of the Vanities for DBAs - The MySQL She-BA — December 29, 2006 @ 6:06 pm GMT Dec 29,2006 | Reply

  5. […] a bit of bad press that statspack has received lately, I still find it (and AWR reports – the optional and […]

    Pingback by Case Study: Statspack/AWR Latch Waits (Part 1) : Ardent Performance Computing — June 27, 2007 @ 5:41 pm GMT Jun 27,2007 | Reply

  6. john ,

    Thanks for giving such a good notes on statspack , with wonderful quotes :)

    Comment by jony — May 10, 2011 @ 6:26 pm GMT May 10,2011 | Reply

  7. […] 原文: […]

    Pingback by Analysing Statspack 2 | xpchild — June 21, 2011 @ 11:06 am GMT Jun 21,2011 | Reply

  8. […] pointed out in the past how bad the Instance Efficiency ratios are in highlighting a performance problem. Here’s a recent example from OTN repeating the […]

    Pingback by Irrational Ratios « Oracle Scratchpad — November 13, 2011 @ 11:24 am GMT Nov 13,2011 | Reply

  9. […] conclusions without having some context (for a detailed explanation of importance of context, see a great post by J. Lewis), i.e. going to specific events without getting a general idea of the scales from the “load […]

    Pingback by AWR report case study: stating the obvious « Oracle Diagnostician — July 9, 2012 @ 7:14 am GMT Jul 9,2012 | Reply

  10. […] (often in favor of instance efficiency percentages, which is easier to read but much more likely to mislead).  I decided to make some sort of a short guide for it, describing how different statistics in it […]

    Pingback by AWR report: load profile « Oracle Diagnostician — August 13, 2012 @ 4:49 am GMT Aug 13,2012 | Reply

  11. Pretty gud startup for statspack — Rajeev

    Comment by Rajeev C R — September 20, 2012 @ 10:25 am GMT Sep 20,2012 | Reply

  12. […] examples. 2-Analyaz statspack. 3-Active Session History. 4-Statspack Article. 5-About Statspack. 6-Using Statspack. 7-AWR […]

    Pingback by Understand AWR Report « AbbasGol's Blog — October 10, 2012 @ 6:42 am GMT Oct 10,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 4,556 other followers