Oracle Scratchpad

February 9, 2007

Statspack 10g

Filed under: Performance,Statspack,Troubleshooting — Jonathan Lewis @ 3:58 pm BST Feb 9,2007

[Further Reading on Statspack]

I’ve always been a little nervous about advising people on the snapshot level and snapshot frequency for running statspack.snap(). In general level 0  every 15 minutes seems to be safe, with a slightly more cautious once per hour for levels 5 and above (which, in effect, is the default for the AWR). However, when taking snapshots, it would be sensible to monitor how much work goes into the snapshot so that you can adjust the frequency if you think that statspack itself could be causing some of your performance problems.

In 10g you get a little extra help with this. The snapshot code records the snapshot execution (elapsed) time in seconds. Check the snapshot_exec_time_s column in the stats$snapshot table. Unfortunately I can’t find any indications of this value being reported anywhere so you need to run a simple query of your own to check what’s going on; possibly something like the following:

break on instance skip 1 on dbid skip 1 on report
compute max of elapsed_sec on dbid
compute max of elapsed_sec on instance
compute max of elapsed_sec on report    

        snp.instance_number				instance,
        to_char(snp.snap_time,'dd-mon-yyyy hh24:mi')	snap_time,
        snp.snapshot_exec_time_s			Elapsed_sec
        stats$snapshot  snp
        snp.snap_time       >= trunc(sysdate - 7)
and	snp.dbid            =  (select dbs.dbid from v$database dbs)
and	snp.instance_number =  (select ins.instance_number from v$instance ins)
order by

In most (production) cases this is likely to do a full tablescan of the stats$snapshot table so treat the script with a little caution and don’t go firing it off a dozen times in a row.

Sample output – generated on a small PC system shortly after startup:

---------- ---------- ---------- ----------------- ---------- -----------
  85308249          1         11 09-feb-2007 10:28          0        4.02
                              12 09-feb-2007 10:30          5         .94
                              13 09-feb-2007 10:35          5         .69
                              14 09-feb-2007 10:35         10        1.13
**********                                                    -----------
maximum                                                              4.02
           **********                                         -----------
           maximum                                                   4.02
maximum                                                              4.02

Obviously you will want to modify the where clause and the break/compute section, especially if you are not running RAC. But I’ve left the instance_number and DBID in the code just to remind you that a single perfstat schema can hold multiple sets of data. And if you do need to reference the current DBID and instance_number you may need to arrange the appropriate privileges on the relevant dynamic performance views first.

For the more adventurous

You  could, of course, copy the 10g feature back into 9i. All you have to do is add a column to the stats$snapshot table, add a couple of variable definitions to the statspack package, and modify a couple of pieces of code (the select from dual that gets the snapshot id and can be used to get the starting systimestamp, the insert into stats$snapshot which currently uses sysdate, and the acquisition and update of the ending time that doesn’t currently exist). But there is a completely different, simpler, strategy which I will describe in a future post.

In a similar vein you will have noticed, if you are already using statspack in 10g, that the spreport script asks you for the number of days you want to see in the list of available snapshots. This is a great convenience if you keep a long history but only want to check back a couple of days.

Adopt the same strategy – 10g does this through a script called sprepcon.sql: copy it back to 9i and adjust as necessary.  (In fact this is a bit too much like hard work for my tastes, so I tend to edit the sprepins.sql script to add a clause ‘s.snap_time > trunc(sysdate – 14)’ to the initial query against stats$snapshot and stats$database_instance – and try to remember to take it out again before I leave the client site.)

[Further Reading on Statspack]


  1. Great post, Jonathan. Excellent.

    One minor point, the link to your post on snapshot levels comes out slightly wrong. I make it as having two http://'s.

    Comment by Chadders — February 9, 2007 @ 9:59 pm BST Feb 9,2007 | Reply

  2. the spreport script asks you for the number of days you want to see in the list of available snapshots. This is a great convenience if you keep a long history but only want to check back a couple of days.

    Yes, it’s amazing how much pleasure the little things give me.

    Perhaps I ought to rephrase that.

    Comment by Doug Burns — February 9, 2007 @ 10:29 pm BST Feb 9,2007 | Reply

  3. […] files — Jonathan Lewis @ 11:44 pm UTC Feb 18,2007 A few days ago, I described a change to Statspack in 10g that allowed you to see how much time each snapshot took. In that article I suggested copying the […]

    Pingback by Logoff Triggers « Oracle Scratchpad — February 18, 2007 @ 11:44 pm BST Feb 18,2007 | Reply

  4. very nice post

    Comment by Aseem — July 17, 2009 @ 6:52 am BST Jul 17,2009 | 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

Blog at