Analysing Statspack (2)

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