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.