This was first published on https://blog.dbi-services.com/improving-statspack-experience (2017-08-23)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.