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 select snp.dbid, snp.instance_number instance, snp.snap_id, to_char(snp.snap_time,'dd-mon-yyyy hh24:mi') snap_time, snap_level, snp.snapshot_exec_time_s Elapsed_sec from stats$snapshot snp where 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 snp.dbid, snp.instance_number, snp.snap_id ;
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:
DBID INSTANCE SNAP_ID SNAP_TIME SNAP_LEVEL ELAPSED_SEC ---------- ---------- ---------- ----------------- ---------- ----------- 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.)