Statspack 10g

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             12.1.0.2
rem             11.2.0.4
rem             10.2.0.5
rem     Not relevant
rem              9.2.0.8
rem              8.1.7.4
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             12.1.0.2
rem             11.2.0.4
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.