Oracle Scratchpad

February 9, 2007

Statspack 10g

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

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.

4 Comments »

  1. 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 GMT Feb 9,2007 | Reply

  2. […] 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 GMT Feb 18,2007 | Reply

  3. very nice post

    Comment by Aseem — July 17, 2009 @ 6:52 am GMT Jul 17,2009 | Reply

  4. […] years ago I wrote a short note about how Statspack actually captured its own execution time (from 10g onwards) and how you could run a report on it to check the run time. It’s worth […]

    Pingback by Fixed Stats | Oracle Scratchpad — October 17, 2016 @ 12:43 pm GMT Oct 17,2016 | 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 WordPress.com.