Oracle Scratchpad

February 18, 2008

Analysing Statspack

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 9:50 pm GMT Feb 18,2008

It’s been a few years since I last read this (pdf) article from Connie Dialeris Green of Oracle about how to use Statspack – and I’d forgotten how good it was.

You are bound to find a couple of details which are now outdated – it was published in 2001 and there’s a point (for example) where an updated case study would probably mention the Segment Statistics report – but as an education in how to approach Statspack, I don’t think it can be beaten.

I particularly liked the comments:

Gathering additional data usually requires skipping backwards and forwards through the report to check statistics which may be of interest.

Note that in some situations there may not be sufficient data within the Statspack report to fully diagnose the problem; in this case, it is necessary to gather additional statistics manually.

If you want to get the best out of Statspack you have to do more than take a quick look at a couple of numbers, you need to create and validate a sensible hypothesis based on all the information available. This paper instructs you in the method.


  1. Excellent tract, and certainly the Statspack ‘method’ to be adhered to.

    Comment by SeánMacGC — February 21, 2008 @ 1:50 pm GMT Feb 21,2008 | Reply

  2. Thanks for the good link Jonathan. Indeed, its really worthy to read.

    Comment by Syed Jaffar Hussain — February 22, 2008 @ 3:49 pm GMT Feb 22,2008 | Reply

  3. […] the Oracle Scratchpad, Jonathan Lewis links to a well regarded article on using Statspack, writing, “It’s been a few years since I last read this article from Connie Dialeris Green […]

    Pingback by Log Buffer #85: a Carnival of the Vanities for DBAs — February 22, 2008 @ 5:52 pm GMT Feb 22,2008 | Reply

  4. […] thought to carry around – and ties in nicely with the paper from Connie Green that I referenced earlier on this week – there’s a lot more to interpreting the statistics than translating one number into a […]

    Pingback by Serious Analysis « Oracle Scratchpad — February 22, 2008 @ 11:18 pm GMT Feb 22,2008 | Reply

  5. “Most times, a reliable instinctive feel is developed over time, by encountering and solving performance problems, and most importantly, by being aware of what is normal at your site.”

    And then one of two things happen: People begin skipping analysis and start propagating myths; or they note detail differences and go “that’s funny…” and go on to discover useful new things. The latter is all too rare, and the former is probably a good reason to get an outside opinion with a different expectation set.

    It’s tough to get rid of the “instinctive feel” when upgrading.

    Comment by joel garry — February 25, 2008 @ 10:10 pm GMT Feb 25,2008 | Reply

  6. […] is a totally useless tool. Jonathan Lewis recently referenced a rather nice article by Connie Green on how a saw can be used effectively for slicing through some issues. The traffic report can be […]

    Pingback by Oracle Diagnostic Tools - Some Random Thoughts (Hammer To Fall) « Richard Foote’s Oracle Blog — February 26, 2008 @ 12:08 pm GMT Feb 26,2008 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: