Statspack scheduling through dbms_scheduler

Oracle pushes only the Enterprise Edition (EE) + Diagnostics pack Automatic Workload Repository (AWR) with 11g2 for statistics reporting, which doesn’t help Standard Edition (SE) or lower users. We are still forced to use (luckily) existing statspack package for performance tuning and reporting.

I don’t like redundant information, so this won’t be another post on installing and using Oracle statspack. There are plenty of other good articles about that, like for example Akadia’s article and Oracle 9.2 Performance tuning documentation, chapter 21. Instead, I’m going to tell you how I scheduled statspack scripts to be run automatically via dbms_scheduler.

Oracle 11g2 contains statspack, but its documentation has diminished from the previous versions. The required scripts are in the %ORACLE_HOME%\rdbms\admin folder, with names beginning with “sp”. The folder also holds a document called spdoc.txt, which is nice, since the rest of the documentation has been removed from the Performance tuning guide. Now the Oracle documentation gives away the presence of this file only in the “Streams concepts and administration” manual which doesn’t feel like a logical place to me.

Spdoc.txt and Oracle 9.2 documentation list all the scripts and their purpose, with the exception that spdoc documents also updates made to the scripts up until version 11.1. Since the old spauto.sql contains directions to use the dbms_job, I converted it to do the same thing through dbms_scheduler to be more modern. We have also run into problems with jobs scheduled in time zones using DST, causing excessive errors in alert.log. So in essential, this article is more about using scheduler than statspack. 🙂

After the installation of statspack, the procedure is simple. First I need to grant as sys the option to create scheduler jobs to perfstat user.

sqlplus / as sysdba
grant create job to perfstat;

Next I need to login as perfstat and create the actual job. I want two snaps with a time difference of 10 minutes, three times a day. This gives me some coverage throughout the day, but I suspect I will have to modify this schedule later on, when I have collected some data to be analyzed. So this schedule should be considered just as an example.

sqlplus /nolog
connect perfstat/pw;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'snap_1',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
repeat_interval => 'FREQ=HOURLY; BYHOUR=9,12,15; BYMINUTE=5,15',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Statspack automated snap');
END;

This creates a scheduler job with current time being the start_date and the actual execution is performed at times given by the repeat_interval at 9:05, 9:15, 12:05, 12:15, 15:05 and 15:15. The job is enabled at the same time. Scheduler is very versatile, so there are additional options for job classes, groups, schedules, windows etc., but these options are sufficient for my purpose.

I also needed another job purging old snapshots from the db to avoid growing SYSAUX uncontrollably. It was a bit disappointing to notice that I couldn’t use a simple job task for purging old snaps, since statspack.purge is overloaded and normal job cannot distinguish which version I’m trying to use from the job just by giving argument numbers. To define arguments by name, I tried using a program which in turn requires additional steps to define arguments, job and enabling both. Problem was that even with this approach Oracle couldn’t distinguish which version I tried to access or had type mismatch problems.

There was also a possibility to use anonymous PL/SQL block instead of a stored procedure, but in my opinion, that would make altering the parameters more difficult by making it mandatory to redefine the job again, instead of just setting an argument for it. Oh, and I misunderstood one part in the documentation and also wanted to do something in PL/SQL. 🙂

In the end I wrote a small wrapper which can be later customized to access different purge versions with  completely separate names. This made it possible to revert back to simple jobs, as can be seen below with the code for the wrapper. Other way of putting this is to write an anonymous block with the command, as stated in the article by dba-oracle.com, but this doesn’t have all the other functionality purge procedures have:

select * from stats$snapshot where snap_time < sysdate - 14;

Wrapper and job definition with arguments.

CREATE OR REPLACE
PACKAGE STATSPACK_WRAPPER AS 

  procedure PURGE
    ( i_num_days        IN  number
      , i_extended_purge  IN  boolean default FALSE
      , i_dbid            IN  number  default null
      , i_instance_number IN  number  default null
      );

END STATSPACK_WRAPPER;

create or replace
PACKAGE BODY STATSPACK_WRAPPER AS

  procedure PURGE
    ( i_num_days        IN  number
    , i_extended_purge  IN  boolean default FALSE
    , i_dbid            IN  number  default null
    , i_instance_number IN  number  default null
    ) IS
  BEGIN

    STATSPACK.PURGE
    ( i_num_days
    , i_extended_purge
    , i_dbid
    , i_instance_number
    );
  END PURGE;

END STATSPACK_WRAPPER;

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'snap_purge_1',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'statspack_wrapper.purge',
   number_of_arguments => 1,
   repeat_interval    =>  'FREQ=DAILY; BYHOUR=19; BYMINUTE=00',
   auto_drop          =>   FALSE,
   comments           =>  'Statspack automated purge');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name           => 'snap_purge_1',
   argument_position  => 1,
   argument_value     => '14');
DBMS_SCHEDULER.ENABLE('snap_purge_1');
END;

That’s it. Regular statspack-scripts like spreport.sql can be used to create reports and to get rid of the statspack-objects.

—

Edit: Had to change the wrapper since DBMS_SCHEDULER doesn’t approve boolean values as arguments to jobs. PLSQL_BLOCKs won’t  work either. Here’s the new wrapper:

CREATE OR REPLACE
PACKAGE STATSPACK_WRAPPER AS 

  PROCEDURE PURGE
	( i_num_days        IN  number
	, i_extended_purge  IN  varchar2 default 'FALSE'
	, i_dbid            IN  number  default null
	, i_instance_number IN  number  default null
	);

END STATSPACK_WRAPPER;
/
CREATE OR REPLACE
PACKAGE BODY STATSPACK_WRAPPER AS

  PROCEDURE PURGE
    ( i_num_days        IN  number
    , i_extended_purge  IN  varchar2 default 'FALSE'
    , i_dbid            IN  number  default null
    , i_instance_number IN  number  default null
    )
  IS
    i_extended_purge_conv BOOLEAN default FALSE;    
  BEGIN
    IF i_extended_purge = 'TRUE'
      THEN i_extended_purge_conv := TRUE;
    END IF;
    
    STATSPACK.PURGE
    ( i_num_days
    , i_extended_purge_conv
    , i_dbid
    , i_instance_number
    );
  END PURGE;
END STATSPACK_WRAPPER;
/

;