New STATSPACK Methodology (part 1)

Posted in: Technical Track

Recently, I was doing some performance analysis for one of our clients, using STATSPACK and found that they were not using a consistent method for control. STATSPACK was installed on most instances but some instances had scheduled cron jobs for STATSPACK gathering, while others had dba_jobs. In addition, there was no cleanup implemented for some of the instances.

This situation led me to think about finding a consistent, integrated and simple way of setting up and configuring STATSPACK.

After reviewing the different options available, I’ve settled on a preferred methodology that I’ll be outlining in this post. I’ve used this strategy on several of the existing instances and the client is happy with the result.

In this methodology , all statspack settings are handled via the STATSPACK_SETTING table. Actually, STATSPACK_SETTING is the only gateway for any STATSPACK configuration. This table contains all configuration settings for STATSPACK. In order to make any changes on a STATSPACK configuration or in order to disable/enable a STATSPACK configuration, a user can simply update STATSPACK_SETTING. Changes are automatically propagated to the relevant jobs.

STATSPACK_SETTING has the following structure:

create table statspack_setting(
dbid number not null,
snap_disable number(1) default 0 not null,
snap_interval INTERVAL DAY(5) to second (1) not null,
snap_retention_day number(3) not null,
snap_cleanup_scheduled_time DATE not null,
snap_cleanup_interval INTERVAL DAY(5) to second (1) not null );


Each configuration instance has only 1 record on this table. (Delete can not be run on an existing record). This record has all the configuration settings for a specified instance.

  • snap_disable determines whether or STATSPACK should be gathered. (snap_disable=1, No STATSPACK gathering).
  • snap_interval determines how often STATSPACK Should be gathered.
  • snap_retention_day determines how long snaps should be kept.
  • snap_cleanup_scheduled_time and snap_cleanup_interval determine scheduled time for first cleanup run and frequency of running cleanup.

That’s all we require for STATSPACK. As I mentioned before, any changes on this table will update the job accordingly. Allow me to provide some scenarios to see how the framework simplifies things:

Scenario 1: Enable STATSPACK on database with dbid=3259669668. STATSPACK gathers snapsevery hour and keeps snaps for a month . STATSPACK cleanup runs at 23:00 every sunday

Solution 1 :
insert into statspack_Setting values(3259669668,0,'0 01:00:00',30,to_date('08/06/06 23:00:00','MM/DD/YY HH24:MI:SS'),'7 00:00:00');

Scenario 2: Disable STATSPACK gathering.

Solution 2:
update statspack_setting set snap_disable=1 where dbid=3259669668;

Scenario 3: Increase STATSPACK frequency (every 15 minutes) to have more detailed information

Solution 3:
update statspack_setting set snap_interval='0 00:15:00' where dbid=3259669668;

In the following parts, I will explain:

  • how this methodology works
  • how to handle consistency between statspack_setting and scheduled job
  • how to properly cleanup STATSPACK
  • some more complex scenarios

New STATSPACK methodology script.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

2 Comments. Leave new

Arvid Warnecke
September 7, 2006 6:50 am

Okay, nice kick-off so far. Even though I can guess what the idea of that settings table is, I am interested in the next part of this post. When will it happen to appear here?


Shervin Sheidaei
September 7, 2006 10:03 am

Thanks Arvid for comment.
The next part is available at the following address .

Hope you find it useful.




Leave a Reply

Your email address will not be published. Required fields are marked *