Oracle Scratchpad

February 28, 2008


Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 8:45 pm GMT Feb 28,2008

If you know of Douglas Adams you will know that the answer to life, the universe and everything is 42**.

In the case of Statspack, and AWR, 42 is my preferred answer to the question: “How long should I keep the snapshot data?”

One of the strengths of collecting snapshot data is that it makes it easy to compare reports from good periods and bad periods.  To this end, it wouldn’t be unusual to compare a report from today with: 

  • Yesterday
  • The same day last week
  • The same day four weeks (ISO month) ago
  • The same day of the month last month (e.g. 1st of month)
  • Same “logical day” of the last month (e.g. 2nd Friday)

If you’re going to do all these, you need five weeks of data in order to make sure that you can cover all the possibilities. And since it’s not always possible to do the analysis the moment the problem appears, it makes sense to leave one week in hand – i.e. six  weeks, hence 42 days.

The keep time for Statspack data is unlimited, the default keep time for AWR is seven days – so you may want to take steps to increase the AWR default (using procedure dbms_workload_repository.modify_snapshot_settings) and put in place a schedule for purging old Statspack data (using the sppurge.sql script).

Of course, you may want to do comparisons with the same periods a year ago  (compare this Christmas with last Christmas, for example) so there is an obvious argument for even longer retention periods.

If you are running your own reports against the data remember that you should (as with all SQL you develop) understand the data content and structure so that you can optimise the queries before putting them into production.  A query that works well on the first few days of snapshots may perform very badly when you have a couple of months of data recorded.

** If you know Douglas Adams’ work well, you will also know that the question is “What do you get when you multiply six by nine?”.  If you are a real nerd, you will realise that the answer to this question is 42 … but only if your arithmetic operates in base 13 rather than the more traditional base 10.  (Check the sum: 4 * thirteen + 2).


  1. I’m keeping six months of Statspack data online, and I never needed anything more than gathering Optimizer statistics to make sure my queries return in reasonable time (seconds). Of course, I’m always careful to do complete joins, including DBID and INSTANCE_NUMBER even on this non-RAC database. I find the ‘natural join’ syntax very helpful for this.
    Thanks for the Douglas Adams reference – it’s always reassuring to know this great man’s words live on.

    Comment by Flado — February 28, 2008 @ 10:39 pm GMT Feb 28,2008 | Reply

  2. Found it useful to use the statspack export facility: that way I can export and clean up every six months and import the data back to my little play database in the laptop, where I can analyze it to my heart’s content.

    Comment by Noons — February 29, 2008 @ 1:11 am GMT Feb 29,2008 | Reply

  3. I am 42 and a “grumpy old men”. But what would give me a year’s data (same period a year ago) when the actual date of the changing trending profile happened 159 days ago I didn’t see it on time and / or the business noticed (unforeseeable) / “detected” it for me, in that time period. Business demands follow other rules then actual fact.

    Comment by Marco Gralike — February 29, 2008 @ 1:28 am GMT Feb 29,2008 | Reply

  4. “Grumpy” aka Marco Gralike ;-)

    Comment by Marco Gralike — February 29, 2008 @ 1:29 am GMT Feb 29,2008 | Reply

  5. Thanks for the base 13 reference. Now I won’t be 42 until December, while I’ve been thinking I was already over 50!

    Comment by Mark W. Farnham — February 29, 2008 @ 12:36 pm GMT Feb 29,2008 | Reply

  6. Later this year, the U.S. Postal Service will be charging 42 cents for a first-class stamp. I’m seriously hoping they’ll adopt a Hitchhiker’s Guide theme.

    Jonathan: I loved your RMOUG presentations. I’ve been playing with optimizer statistics all week, just trying to wrap my brain around the concept.

    There are 10 kinds of people in this world: those who understand binary, and those who don’t.


    Comment by Robert Shepard — February 29, 2008 @ 7:06 pm GMT Feb 29,2008 | Reply

  7. Marco,

    There’s alway an argument for keeping everything “just in case”. If you can, and your queries, and the runtime for taking snapshots and running the spreport, don’t start taking up excess resources you can just do it.

    One strategy that I’ve seen from time to time is the hourly snapshot for a few weeks, then a daily snapshot for a year or so. You don’t have the detail, of course, but for slow trends it can be helpful.

    9i makes it easy to implement this strategy as it allows you to label a snapshot as a “baseline” – and the sppurge.sql script doesn’t delete snapshots that are baselines. (Don’t forget to coalesce indexes from time to time, though – and you may want to see if you can do something sensible with the setting for pctused on the tables).

    Comment by Jonathan Lewis — March 1, 2008 @ 2:31 pm GMT Mar 1,2008 | Reply

  8. […] some organisations, by the time you’ve got access five or six days might have passed. 35 and 42 days are oft-mentioned sensible suggestions, mostly so that a) you’ve got a decent time […]

    Pingback by Plans gone AWRy – an invASHtigation « OraStory — December 29, 2009 @ 4:37 pm GMT Dec 29,2009 | 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 )

Google photo

You are commenting using your Google 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: