Oracle Scratchpad

May 14, 2007

Statspack notes

Filed under: Statspack — Jonathan Lewis @ 8:38 pm BST May 14,2007

If you’re a user of statspack, have a little browse of Dan Fink’s blog, you’ll find some useful comments there. For future readers of this pointer, check particularly around the March 2007 timeframe.


  1. Why not post the permalink?

    Comment by Jason Bucata — May 15, 2007 @ 3:33 am BST May 15,2007 | Reply

  2. i find not so surprisingly what Dan Fink reports. It seems to me that same problem appens if you go directly on v$sql for an executing statement, statistics are not updated until the end of the execution of the statement, so can’t be the snapshot of statspack about that statement. Do you agree?

    Comment by Cristian Cudizio — May 15, 2007 @ 7:25 am BST May 15,2007 | Reply

  3. Jason, there was a good reason for not posting the specific permalink. There are several items in the blog about statspack that should be read, and linking to just one of them makes it easier for people to miss the others.

    Cristian, it looks as if you followed the specific link rather than the general link – don’t forget to browse around the rest of his notes on Statspack. Your lack of surprise is a good sign, of course – every observation you make about Oracle gives you another reference point for the future. When you see “Y”, you have a better chance of recognising it as a side-effect, or relative, of “X”. I am rarely surprised by Oracle because I have such a large list of prior observations to rely on. (The whole trouble-shooting thing is a bit like Greg House M.D. with his differential diagnoses – except I don’t think you could get a TV series out of a DBA).

    Comment by Jonathan Lewis — May 15, 2007 @ 7:48 am BST May 15,2007 | Reply

  4. Oh. I thought you were referring to that one specific post on top… I think it might have been clearer (for whatever it’s worth, it would have been clearer for *me*!) to just post permalinks to the three articles in March that you were interested in.

    To try to atone for my sins:

    (He’s got other articles on Statspack going back to November, but I don’t know if you were referring to those.)

    Comment by Jason Bucata — May 15, 2007 @ 2:56 pm BST May 15,2007 | Reply

  5. Finks comments point out why focusing on end user or batch response time is key and why it’s all about proper collection scoping. In his case, the scope was poorly defined so snaps between scope are meaningless if not dangerously misleading.

    Comment by Ted — May 15, 2007 @ 6:56 pm BST May 15,2007 | Reply

  6. Ted, I don’t think anyone could argue with the statement that you only get the right results if you collect the right data at the right time.
    Focusing on the end user or batch does not necessarily give you the right data though; it only tells you what the end-user (or batch) sees as a symptom. That’s why sometimes you have to rely on things like statspack to give you snapshots of everything else that’s going on at about the same time – and why you need to be reminded of the limitations of a sampling process that isn’t (and often can’t) be synchronised with your carefully scoped investigation.

    Comment by Jonathan Lewis — May 16, 2007 @ 10:26 am BST May 16,2007 | 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 5,388 other followers