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.
update statspack_setting set snap_disable=1 where dbid=3259669668;
Scenario 3: Increase STATSPACK frequency (every 15 minutes) to have more detailed information
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.