AWR 설정 및 관리
1) 현재 SNAP_INTERVAL, RETENTION 확인
col snap_interval for a20
col retention for a50
set linesize 200
select * from DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------------------------------------- ----------
2191910355 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
(Oracle 11g의 기본저장 주기는 1시간 주기, 8일간이다.)
2) snap_interval, retention 설정 (15분 마다 수행, 7일간 저장)
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval =>60);
or
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>10080,interval =>15);
or
-- 10분 간격, 7일간 보관으로 설정할 경우
begin
dbms_workload_repository.modify_snapshot_settings (interval => 15, retention => 7*24*60 );
end;
/
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------------------------------------- ----------
772999712 +00000 00:15:00.0 +00007 00:00:00.0 DEFAULT
- 생성
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
- 삭제 (특정시간)
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
(LOW_SNAP_ID=>241, HIGH_SNAP_ID=>241);
END;
/
- 삭제 (특정범위)
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
(LOW_SNAP_ID=>240, HIGH_SNAP_ID=>241);
END;
/
- 수정 -
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
( retention => 43200, interval => 30);
-> retention : 30일 interval : 30분
- 베이스라인 (생성)
BEGIN
DBMS_WORKLOAD.REPOSITORY.CREATE_BASELINE
(START_SNAP_ID=>240, END_SNAP_ID=>241, BASELINE_NAME=>'baseline_name');
END;
/
