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)
Database : 11.2.0.4.0
Tags In
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)