Oracle

STATSPACK Installation & Configuration

July 8, 2019

The STATSPACK utility can store snapshots of system statistics over time, allowing greater accuracy and flexibility.

It requires an isolated tablespace to contain all of the objects and data.

It is suggested the tablespace to be called PERFSTAT, the same name as the schema owner for the STATSPACK tables.

Step 1: Creation of PERFSTAT Tablespace

We can create the default tablespace for PERFSTAT schema using the following script:

CREATE TABLESPACE PERFSTAT DATAFILE
  '' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Step 2: Execute Installation script

In order to install Statspack, we have to execute the following script:

SQL> @/rdbms/admin/spcreate.sql

This script prompts for:

  • PERFSTAT password
  • name of the default tablespace for PERFSTAT schema (choose the tablespace that has been created during Step #1)
  • name of the default TEMP tablespace for PERFSTAT schema (choose TEMP)

Step 3: Test Statspack

Once installed you can connect to the PERFSTAT user and take a snapshot of the system statistics using the following procedure call:

SQL> EXEC STATSPACK.snap;

Step 4: Schedule Automatic STATSPACK Data Collections

Now that we have verified that STATSPACK is installed and working, we can schedule automatic data collection. By using the statsauto.sql script we can automatically schedule a data collection for STATSPACK.

In the following example we are scheduling an automatic data collection to run every 20 minutes:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.STATSPACK_COLLECTION_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2019/06/26 14:30:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=20'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'PERFSTAT.STATSPACK.SNAP'
      ,comments        => 'Collect Statspack statistics every 20 minutes'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_COLLECTION_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.STATSPACK_COLLECTION_JOB');
END;
/

Step 5: Create Purge Procedure

In this step we should proceed with the creation of the procedure that is going to be used for purging:

CREATE OR REPLACE PROCEDURE PERFSTAT.sp_purge_snap as
    m_instance_number    v$instance.instance_number%type;
    m_dbid            v$database.dbid%type;
    m_low_snap        stats$snapshot.snap_id%type;
    m_high_snap        stats$snapshot.snap_id%type;
begin
    select
        instance_number,
        dbid,
        min(snap_id),
        max(snap_id)
    into
        m_instance_number,
        m_dbid,
        m_low_snap,
        m_high_snap
    from
        stats$snapshot
    where
        snap_time < sysdate - 30
    and    dbid = (select dbid from v$database)
    and    instance_number = (select instance_number from V$instance)
    group by
        instance_number,
        dbid
    ;
    dbms_output.put_line(
        'Number of snapshots deleted: ' ||
        statspack.purge(
            i_begin_snap        => m_low_snap,
            i_end_snap        => m_high_snap,
            i_snap_range        => true,
            i_extended_purge    => false,
            i_dbid            => m_dbid,
            i_instance_number    => m_instance_number
        )
    );
end;
/

Step 6: Schedule Automatic STATSPACK Purge Jobs

In this step we are scheduling an automatic purge job for STATSPACK to run once per day. This scheduled job uses the procedure that was created in Step 5:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.STATSPACK_PURGE'
      ,start_date      => TO_TIMESTAMP_TZ('2019/06/26 14:35:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'freq=daily;byhour=0;byminute=0;bysecond=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'PERFSTAT.sp11g_purge_snap'
      ,comments        => 'PURGE STATSPACK DATA'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.STATSPACK_PURGE'
     ,attribute => 'AUTO_DROP'
     ,value     => TRUE);
  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.STATSPACK_PURGE');
END;
/

Leave a Reply

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