By Franck Pachot

.
I’ve published a few month ago an article in the UKOUG OracleScene magazine on Improving Statspack Experience: quick setup script, changing settings, filling Idle Events,etc. In the article, I used dbms_job to schedule the snapshots, because I have this script for years and never took the time to do it with dbms_scheduler. Thanks to Nicolas Jardot here is the equivalent script using dbms_scheduler.

The idea is to have a script to run on each instance (when in RAC) in order to have a job calling statspack.snap and statspack.purge on each instance.


DECLARE
    instno NUMBER;
    snapjob VARCHAR2(30);
    purgejob VARCHAR2(30);
BEGIN
    select instance_number into instno from v$instance;
    snapjob  := 'PERFSTAT.STATSPACK_SNAP_' || instno;
    purgejob := 'PERFSTAT.STATSPACK_PURGE_' || instno;
 
    DBMS_SCHEDULER.CREATE_JOB (
       job_name => snapjob, 
       job_type => 'PLSQL_BLOCK', 
       job_action => 'statspack.snap;',
       number_of_arguments => 0,
       start_date => systimestamp,
       repeat_interval => 'FREQ=HOURLY;BYTIME=0000;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN',
       end_date => NULL,
       enabled => TRUE,
       auto_drop => FALSE,
       comments => 'Take hourly Statspack snapshot');
 
    DBMS_SCHEDULER.CREATE_JOB (
       job_name => purgejob, 
       job_type => 'PLSQL_BLOCK', 
       job_action => 'statspack.purge(i_num_days=>31,i_extended_purge=>true);',
       number_of_arguments => 0,
       start_date => systimestamp,
       repeat_interval => 'FREQ=WEEKLY;BYTIME=120000;BYDAY=SUN',
       end_date => NULL,
       enabled => TRUE,
       auto_drop => FALSE,
       comments => 'Weekly purge Statspack snapshot');
 
    DBMS_SCHEDULER.SET_ATTRIBUTE( name => snapjob, attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF );
    DBMS_SCHEDULER.SET_ATTRIBUTE( name => snapjob, attribute => 'INSTANCE_ID', value=>instno);
    DBMS_SCHEDULER.SET_ATTRIBUTE( name => snapjob, attribute => 'INSTANCE_STICKINESS', value=>TRUE);
 
    DBMS_SCHEDULER.SET_ATTRIBUTE( name => purgejob, attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF );
    DBMS_SCHEDULER.SET_ATTRIBUTE( name => purgejob, attribute => 'INSTANCE_ID', value=>instno);
    DBMS_SCHEDULER.SET_ATTRIBUTE( name => purgejob, attribute => 'INSTANCE_STICKINESS', value=>TRUE);  
END;
/

I used the Oracle Cloud Service to provision quickly a two nodes RAC database to validate, and I’ll check the scheduling:


[oracle@rac-dg01-1 admin]$ alias sql='JAVA_HOME=$ORACLE_HOME/jdk bash $ORACLE_HOME/sqldeveloper/sqlcl/bin/sql'
[oracle@rac-dg01-1 admin]$ TWO_TASK=//10.31.143.86/pdb1.a511644.oraclecloud.internal sql sys/"Ach1z0#d" as sysdba
 
SQLcl: Release 12.2.0.1.0 RC on Wed Aug 23 18:57:12 2017
 
Copyright (c) 1982, 2017, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
 
SQL> set sqlformat ansiconsole
SQL> select job_name, state, enabled, next_run_date, instance_stickiness, instance_id from dba_scheduler_jobs where owner='PERFSTAT';
JOB_NAME           STATE      ENABLED  NEXT_RUN_DATE                           INSTANCE_STICKINESS  INSTANCE_ID
--------           -----      -------  -------------                           -------------------  -----------
STATSPACK_SNAP_1   SCHEDULED  TRUE     23-AUG-17 07.00.00.981193000 PM +00:00  TRUE                 1
STATSPACK_PURGE_1  SCHEDULED  TRUE     27-AUG-17 12.00.00.074939000 PM +00:00  TRUE                 1
STATSPACK_SNAP_2   SCHEDULED  TRUE     23-AUG-17 07.00.00.644681000 PM +00:00  TRUE                 2
STATSPACK_PURGE_2  SCHEDULED  TRUE     27-AUG-17 12.00.00.755685000 PM +00:00  TRUE                 2

One hour later, the job has run on each instance:


JOB_NAME           STATE      ENABLED  NEXT_RUN_DATE                           INSTANCE_STICKINESS  INSTANCE_ID
--------           -----      -------  -------------                           -------------------  -----------
STATSPACK_SNAP_1   SCHEDULED  TRUE     23-AUG-17 08.00.00.325755000 PM +00:00  TRUE                 1
STATSPACK_PURGE_1  SCHEDULED  TRUE     27-AUG-17 12.00.00.074939000 PM +00:00  TRUE                 1
STATSPACK_SNAP_2   SCHEDULED  TRUE     23-AUG-17 08.00.00.644681000 PM +00:00  TRUE                 2
STATSPACK_PURGE_2  SCHEDULED  TRUE     27-AUG-17 12.00.00.755685000 PM +00:00  TRUE                 2

Now running a spreport to see the instances having snapshots:


[oracle@rac-dg01-1 admin]$ TWO_TASK=//10.31.143.86/pdb1.a511644.oraclecloud.internal/cdb12 sqlplus sys/"Ach1z0#d" as sysdba @ spreport
 
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
  924704606        2 CDB1         cdb12        rac-dg01-2
  924704606        1 CDB1         cdb11        rac-dg01-1
 
Using  924704606 for database Id
Using          2 for instance number

Here it is. dbms_job is deprecated. Let’s use dbms_scheduler.