New STATSPACK Methodology (part 1)

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.