New STATSPACK Methodology (part 1)

Aug 8, 2006 / By Shervin Sheidaei

Tags: ,

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 );

ALTER TABLE STATSPACK_SETTING ADD (CONSTRAINT PKC_STATSPACK_SETTING PRIMARY KEY(DBID));

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.

2 Responses to “New STATSPACK Methodology (part 1)”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>