Problem solve Get help with specific problems with your technologies, process and projects.

Purge statspack snapshots to avoid data growth

This script offers an efficient way to purge Statspack snapshots to avoid data growth.

Script updated Sept. 10, 2004

You may find that sppurge.sql provided in statspack by Oracle is not good enough for you. Let us say you want to only keep the last three months of snapshots to avoid data growth. Because "DELETE FROM STATS$SQLTEXT" statement is resource intensive, Oracle commented it out in the original script.

I started thinking about an efficient way to do this job. This script is the result; it 1) has no performance impact, 2) has no junk data leftover, and 3) parameters are provided to give you more flexibility to fit your situation. It has been tested in statspack 8iR3, 9iR1 and 9iR2.

 --------------------------------------------------------------------------- -- Under schema PERFSTAT --------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE PERFSTAT.SPPURGE( --Snapshot will be expired in 90 days P_DAYS NUMBER DEFAULT 90, --Maximum amount of snapshots can be removed when run sppurge once P_LOOP NUMBER DEFAULT 4, --You may specify a bigger rollback segment to avoid ORA-1562 P_RBIG VARCHAR2 DEFAULT 'RBIG') AS ----------------------------------------------------------------------------- -- -- Purpose: to purge expired snapshots for statspack 8iR3, 9iR1 and 9iR2 -- -- First Created: emao Oct 30,2003 -- Last Modified: emao Nov 24,2003 ------------------------------------------------------- V_MIN_SNAP_ID NUMBER; V_LOOP NUMBER :=0; -- BEGIN -- EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=1048576'; -- WHILE V_LOOP<P_LOOP LOOP -- SELECT MIN(SNAP_ID) INTO V_MIN_SNAP_ID FROM PERFSTAT.STATS$SNAPSHOT WHERE (TRUNC(SNAP_TIME)+P_DAYS) < TRUNC(SYSDATE); IF V_MIN_SNAP_ID IS NOT NULL THEN EXECUTE IMMEDIATE 'SET TRANSACTION USE ROLLBACK SEGMENT '||P_RBIG; DELETE /*+ INDEX_FFS(ST) */ FROM STATS$SQLTEXT ST WHERE (ST.HASH_VALUE,ST.TEXT_SUBSET) IN (SELECT /*+ HASH_AJ FULL(SS) NO_EXPAND */ HASH_VALUE, TEXT_SUBSET FROM STATS$SQL_SUMMARY SS WHERE SS.SNAP_ID = V_MIN_SNAP_ID ); -- -- For statspack 9i R2 only -- -- Take the following comment out if you want to use it for statspack 9iR2 /* DELETE --+ INDEX_FFS(SSO) FROM STATS$SEG_STAT_OBJ SSO WHERE ( DBID, DATAOBJ#, OBJ# ) NOT IN (SELECT --+ HASH_AJ FULL(SS) NO_EXPAND DBID, DATAOBJ#, OBJ# FROM STATS$SEG_STAT SS WHERE SNAP_ID= V_MIN_SNAP_ID ); */ DELETE FROM PERFSTAT.STATS$SNAPSHOT WHERE SNAP_ID = V_MIN_SNAP_ID; COMMIT; V_LOOP:=V_LOOP+1; DBMS_OUTPUT.PUT_LINE('SNAP_ID:'||V_MIN_SNAP_ID||' removed!'); ELSE DBMS_OUTPUT.PUT_LINE('No expired snapshots!'); EXIT; END IF; END LOOP; END; / --------------------------------------------- -- FileName: statspack_purge.sql -- --------------------------------------------- set serverout on select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "Start" from dual; -- --default parameters --exec perfstat.sppurge(p_days=>90,p_loop=4,p_rbig=>'RBIG'); -- exec perfstat.sppurge; select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "End" from dual; exit; SQL> @statspack_purge.sql Start ------------------- 2003/11/25 14:46:25 SNAP_ID:418 removed! SNAP_ID:419 removed! SNAP_ID:420 removed! SNAP_ID:421 removed! PL/SQL procedure successfully completed. End ------------------- 2003/11/25 14:46:47


For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle and SQL