New STATSPACK Methodology (part 2)

(Link to Part 1)

In this second part , I will outline more details of a new STATSPACK methodology. As I mentioned in previous part , the statspack_setting table is a bridge between the user and Oracle’s STATSPACK.

The user can configure STATSPACK by setting the proper value on the statspack_setting table, and the settings will be automatically propagated. In this new methodology the user doesn’t need to have a deep knowledge of STATSPACK. The user need only decide to set up STATSPACK and configure it. The statspack_setting table keeps the STATSPACK configuration for each instance. These configurations determine:

• how often a STATSPACK snap is gathered (the snap_interval field in statspack_setting table)
• how long a STATSPACK snap is kept (the snap_retention_day field in statspack_setting table)
• how often old snaps are cleaned up. (the snap_cleanup_interval field in statspack_setting)

The statspack_setting table has a trigger that fires before any DML (Insert/Update/Delete). This trigger synchronizes any changes in the table to the relevant STATSPACK gathering/cleanup jobs, and its action is transparent to the user configuring STATSPACK. The trigger takes care of the following:

• avoiding any delete on existing records. (In order to disable STATSPACK, the snap_disable field should be set.)
• allowing STATSPACK configuration only for existing instances. The trigger only allows new records in statspack_setting whose dbid values match the database id.
• any updates on statspack_setting propagates to relevant jobs

I know that this process may raise some questions. Allow me to go through some of them.

What happens if the trigger is dropped or is disabled?

If the trigger is dropped or is disabled, the whole process will not function properly. For proper functionality, not only must the trigger exist, it must be enabled. This led me to implement a monitoring script that runs on daily basis. It reports the trigger status, checks whether STATSPACK jobs are in sync with the configuration in statspack_setting, and checks whether current snaps are gathered and cleaned up as the user has asked for in statspack_setting, generally checking consistency between current STATSPACK snaps and the user’s STATSPACK configuration.

Here is the text of monitoring script.

statspack_monitoring_script.txt

What happens if scheduled STATSPACK jobs are manually dropped?

Manual dropping of a STATSPACK job (either a STATSPACK gathering job or a STATSPACK cleanup job) can be discovered by the monitoring script. If the monitoring script cannot find the relevant job, it will report a warning. In addition, if STATSPACK jobs are broken, an monitoring script will warn us. The combination of these two monitoring facilities guarantees that STATSPACK jobs are alive and functioning well.

Let me provide more scenarios to show how trigger controls the proper functionality of this methodology.

Scenario 1: The user sets a configuration for a remote database.

SQL> select dbid from v$database;

DBID
----------
253241524

SQL> select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

STATSPACK has already been configured for the existing database. The following script tries to configure STATSPACK for the remote database.

SQL> insert into statspack_setting values (343643234,0,'0 01:00:00',14,to_date('01-09-06','DD-MM-YY'),'7 00:00:00');

insert into statspack_setting values (343643234,0,'0 01:00:00',14,to_date('01-09-06','DD-MM-YY'),'7
*
ERROR at line 1:
ORA-20000: DBID value does not match with the existing database select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

The new configuration is not accepted because DBID does not match the existing database.

Scenario 2: The user would like to remove a STATSPACK configuration

SQL> select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

SQL> delete from statspack_setting where dbid=253241524;
delete from statspack_setting where dbid=253241524
*
ERROR at line 1:
ORA-20000: Delete is not allowed
ORA-06512: at "SCOTT.TRG_STATSPACK_SETTING_AIUD", line 11
ORA-04088: error during execution of trigger 'SCOTT.TRG_STATSPACK_SETTING_AIUD'

delete is not permitted by the trigger. truncate on this table cannot be controlled by the trigger, but it can be reported by the monitoring script .

Scenario 3: The user adds more than one configuration for a database.

SQL> select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

SQL> insert into statspack_setting values ( 253241524,0,'0 00:15:00',7,to_date('01-09-06','DD-MM-YY'),'7 00:00:00');

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PKC_STATSPACK_SETTING) violated

The primary key on DBID guarantees that only one configuration exists for each database.

Scenario 4: The user rolls back a STATSPACK configuration.

SQL> select * from dba_jobs;

no rows selected

SQL> select * from statspack_setting;

no rows selected

SQL> insert into statspack_setting values (253241524,0,'0 01:00:00',14,to_date('01-09-06','DD-MM-YY'),'07 00:00:00');

1 row created.

SQL>select job,LAST_DATE,NEXT_DATE,interval,WHAT from dba_jobs;

JOB LAST_DATE NEXT_DATE INTERVAL WHAT
---------- ------------------- ------------------- ---------------------------------------- --------
27 27-08-2006 21:20:04 trunc(sysdate,'MI')+60/(48*30) CRE_SNAP;
28 01-09-2006 00:00:00 trunc(sysdate,'MI')+10080/(48*30) RM_SNAP(14);

The scheduled job was automatically submitted according to the configuration. These jobs are visible only to the current session and are not active until the end of the transaction.

SQL> rollback;

Rollback complete.

SQL> select * from statspack_setting;

no rows selected

SQL> select * from dba_jobs;

no rows selected

Scheduled STATSPACK jobs are rolled back when the STATSPACK configuration is not committed. This scenario shows that no sign of the scheduled job remains when the user rolls back the configuration setting.

Proper statspack cleanup

I found that some of our clients have a scheduled job for STATSPACK cleanup. That’s good, but it’s unfortunate that their cleanup script deletes from stats$snapshot only those records that are older than retention days, because this way of cleanup does not clean up all relevant data of old snaps, causing PERFSTAT default tablespace to fill up after a while.

If you look carefully at the the PERFSTAT schema, you will find that most tables in this schema are children of the stats$snapshot table with the cascade on delete option. But there are some tables which have no dependency on the stats$snapshot table.

These tables are not cleaned up when delete occurs only on stats$snapshot. Here is the listing of this table.

SQL>select table_name from dba_tables where owner='PERFSTAT' and table_name!='STATS$SNAPSHOT'
minus
select table_name
from dba_constraints
where
R_CONSTRAINT_NAME=(select constraint_name from dba_constraints where owner='PERFSTAT' and table_name='STATS$SNAPSHOT' and constraint_type='P')
and
r_owner='PERFSTAT'
and
delete_rule='CASCADE';

STATS$DATABASE_INSTANCE
STATS$IDLE_EVENT
STATS$LEVEL_DESCRIPTION
STATS$SEG_STAT_OBJ
STATS$SQLTEXT
STATS$SQL_PLAN
STATS$STATSPACK_PARAMETER
STATS$UNDOSTAT

In order to get rid of all old snaps and related data, cleanup of STATSPACK should be more than a simple delete on stats$snapshot. The contents of all of the above tables should be examined for any old snaps. I use the following stored procedure to cleanup STATSPACK. (Also, Oracle provides $ORACLE_HOME/rdbms/admin/sppurge.sql for STATSPACK cleanup.)

STATSPACK Cleanup Stored Procedure

I would like to hear your thoughts on this new approach to STATSPACK, so please leave a comment. I hope you find it useful.