Print Friendly, PDF & Email

GOAL
This note shows the way to purge automatically and regularly STATSPACK data that are older than a specified number of days.

SOLUTION
To purge unnecessary STATSPACK data use the STATSPACK.PURGE procedure. One of the procedure parameters accepts the number of days and removes all snapshots that are older that value.

 SQL> exec statspack.purge(number_of_days);

 
For instance, to purge all snapshots older than 14 days

SQL> exec statspack.purge(14);

 
To purge automatically and regularly 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. The job must be created by the user in which schema the STATSPACK was installed.

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

-- The STATSPACK owner

SQL> SHOW USER
USER is "PERFSTAT"
-- Create a job

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 to 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)

 
 

Version  : 17:18 08.10.2020
Database : 11.2.0.4.0