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:
rem rem Script: statspack_elapsed.sql rem Author: Jonathan Lewis rem Dated: February 2007 rem Purpose: rem rem Last tested rem 126.96.36.199 rem 188.8.131.52 rem 10.2.0.5 rem Not relevant rem 184.108.40.206 rem 220.127.116.11 rem 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.)
Update for AWR (Oct 2016)
Following a recent piece of work where I asked for an export of the statspack schema (from an 11g database) I realised that I hadn’t published the equivalent script of the AWR (automatic workload repository). The information is there if you want it, but it doesn’t appear in the standard report anywhere. You could got to base tables, but my little hack simply queries the snapshot view, converting the column types to match the report I’ve got above for Statspack:
rem rem Script: awr_elapsed.sql rem Author: Jonathan Lewis rem Dated: Oct 2016 rem rem Last tested rem 18.104.22.168 rem 22.214.171.124 rem set linesize 254 set trimspool on set pagesize 60 set timing off 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.begin_interval_time,'dd-mon-yyyy hh24:mi') snap_time, snap_level, 60 * extract(minute from snp.flush_elapsed) + extract(second from snp.flush_elapsed) Elapsed_sec from dba_hist_snapshot snp where snp.begin_interval_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 ;
I’ve limited the query to the last 7 days, on the current database and instance (which you can change, of course, especially if you’re running RAC), and I’ve converted an interval type to number of seconds (which, I hope, is all that it takes to perform a typical AWR snapshot).
One of the nice things about this query is that it gives you a simple measure of how heavily loaded the instance (or database, or system) is – if the AWR snapshot takes an unusually long time to run then something much be putting an unusual load on the system at the time of the snapshot.