[Oracle] Statspack Snapshot유지 기간 Retention 설정 관리

오라클에서 성능 분석을 위해 Statspack을 설정하였는데 시간이 지남에 따라 SYSAUX 테이블스페이스에 과도하게 데이터가 쌓이는 것을 확인하였다.

AWR Report는 Retention 에 설정한 유지 기간에 따라 자동으로 스냅샷을 관리해주는 기능이 있지만 Statspack 에는 자동 관리 기능이 없다. Statspack을 사용할 경우 직접 정리 작업을 하거나 스케줄러를 등록하여야 한다.

 

 

Statspack 스냅샷 확인

-- Statspack 스냅샷 리스트 확인
select * from stats$snapshot
order by SNAP_TIME desc;

 

Statspack 데이터 삭제

-- 3달 경과한 Statspack 데이터 삭제
DELETE from stats$snapshot where snap_time < sysdate - 90;

 

Statspack 데이터 삭제 프로시저

--세 달 경과한 STATPACK 데이터 삭제하는 프로시저
CREATE OR REPLACE PROCEDURE PERFSTAT.DEL_STAT_SNAP
IS
BEGIN
	DELETE FROM STATS$SNAPSHOT WHERE SNAP_TIME < SYSDATE - 90;
	COMMIT;
END DEL_STAT_SNAP;
/

 

서버 성능에 따라 시간이 오래 소요될 수 있으니 조금씩 개수를 늘려가며 테스트를 진행해야 한다.

 

 

 

 

+

ORA-01031 등 권한 문제 발생 시 STATSPACK 설치 계정인 PERFSTAT에 대한 삭제 권한이 있는지 검토하는 것이 좋다. 아니면 PERFSTAT 계정으로 프로시저를 생성하는 것도 방법이다. (하지만 PERFSTAT 계정을 삭제하면 같이 삭제되니 관리에 유의할 것) 

 

추가로 아래 테이블에 관련 데이터가 존재하는지 확인한다.

select * from stats$sysstat;
select * from stats$system_event;
select * from stats$sesstat;
select * from stats$sql_summary;

내 경우는 stats$snapshot만 삭제 진행해도 위 테이블들에 해당 snap_id 데이터가 다 삭제가 되었는데, 아닐 경우 프로시저에 삭제 명령문을 추가한다.

삭제 명령문은 아래와 같다.

delete from stats$sysstat
where snap_id not in (select snap_id from stats$snapshot);

delete from stats$system_event;
where snap_id not in (select snap_id from stats$snapshot);

delete from stats$sesstat;
where snap_id not in (select snap_id from stats$snapshot);

delete from stats$sql_summary;
where snap_id not in (select snap_id from stats$snapshot);

 

돌려보진 않았지만 not in 절이 있는걸 보니 대량으로 수행하면 부하가 꽤 있을 것 같다.