HOW TO AUTO-PURGE STATSPACK SNAPSHOTS

 
GOAL

 
 
SOLUTION
 

To purge unnecessary statspack data use the STATSPACK.PURGE procedure. The procedure has the parameter that accepts the number of days of snapshots to keep

SQL> exec statspack.purge(number_of_days);

 
For instance, to purge data that is older than 14 days

SQL> exec statspack.purge(14);

 
To auto-purge statspack snapshots create a job through the DBMS_JOB procedure that runs at the specified period of time. As the job’s parameter pass the STATSPACK.PURGE procedure with the desired number of days of keeping data.

 
For instance, this creates a job that runs every day at midnight purging any snapshots older than 14 days

SET SERVEROUTPUT ON
VARIABLE JOB_NUMBER NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(JOB  => :JOB_NUMBER,
                  WHAT => 'STATSPACK.PURGE(I_NUM_DAYS => 14, I_EXTENDED_PURGE => TRUE);',
                  NEXT_DATE => TRUNC(SYSDATE) +1,     -- Run at the midnight
                  INTERVAL  => 'TRUNC(SYSDATE) +1');  -- Run on the next day at the midnight
  COMMIT;
END;
/

 
When the purge job is created find out its details

SET PAGES 999
SET LINES 300
COL JOB FOR 9999999
COL WHAT FOR A25
COL SCHEMA_USER FOR A15 
COL NEXT_RUN_DATE FOR A20 
COL INTERVAL FOR A18
COL FAILURES FOR 999999999

SELECT JOB,
       SCHEMA_USER,
       WHAT,
       TO_CHAR(NEXT_DATE,'DD-MON-YYYY HH24:MI:SS') NEXT_RUN_DATE,
       INTERVAL,
       FAILURES
FROM
       DBA_JOBS
WHERE
       WHAT LIKE 'STATSPACK.PURGE%'; 

     JOB SCHEMA_USER     WHAT                      NEXT_RUN_DATE        INTERVAL               FAILURES
-------- --------------- ------------------------- -------------------- ------------------ ------------
     907 PERFSTAT        STATSPACK.PURGE(I_NUM_DAY 09-OCT-2020 00:00:00 TRUNC(SYSDATE) +1
                         S => 14, I_EXTENDED_PURGE
                          => TRUE);


SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
08-OCT-20 05.08.23.293604 PM +03:00

So, the purge job was created with id of 907 and it will be run at the next day at 00:00:00.

 
Addtional commands to manage the purge job:

If you wish to run the purge job now

-- EXEC DBMS_JOB.RUN(907);

 
If you wish to reschedule the purge job, for instance run every 12 hours

-- EXEC DBMS_JOB.INTERVAL(907, 'sysdate+12/24');

 
If you wish to remove the purge job

-- EXEC DBMS_JOB.REMOVE(907);

 
 
REFERENCES
 
Statistics Package (STATSPACK) Guide (Doc ID 394937.1)
FAQ- Statspack Complete Reference (Doc ID 94224.1)