Statspack Report scheduling based on the Requirment timings

The below steps are to schedule the statspack collection to run on the specific timings. ie it runs in the weeksdays apart from monday by the interval 3 hours, and from sunday 12 pm to monday 12 pm it runs every 1 hour (ie during the peak time).


STEP 1: Remove the existing statspack job schedule:
EXECUTE DBMS_JOB.REMOVE(1);

STEP 2 : Function that will return the date to the job:

create or replace function statspack_schedule
return date
as
N_date date;
begin
select case
when ( to_char(dt,'dy') = 'sun' and to_char(dt,'hh24:mi')>'12:00')
or ( to_char(dt,'dy') = 'mon' and to_char(dt,'hh24:mi')<'12:00') then d1 else d2 end into N_date from ( select sysdate dt , trunc(sysdate, 'hh24') +1/24 d1 , trunc(sysdate, 'hh24') +3/24 d2 from dual ); return N_date; end; /

STEP 3: statspack Job scheduling :

BEGIN
DBMS_JOB.isubmit (
job => 1,
what => 'statspack.snap;',
next_date => sysdate,
interval => '( select statspack_schedule from dual )');
COMMIT;
END;
/

STEP 4: Purge the old data with the retention of 1 month (Manually do this activity every month):

CONNECT perfstat/perfstat
@?\rdbms\admin\sppurge.sql
(specified the snap id with retention of 1 month)
to identified the begining and end snap id:
select snap_id, snap_level "level", to_char(snap_time,' dd/mm/YYYY HH24:mi:ss') snap_date from stats$snapshot
where snap_timeorder by 1;