AWR(Automatic Workload Repository)
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/gathering-database-statistics.html#GUID-CE73D449-0EE9-4022-B1F1-AA12F0955C03
https://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF94212
AWR
직역시 자동 부하 관련 데이터 저장소
AWR 데이터는 스냅샷 수행에 의해 저장되거나 숨김 파라미터 설정 값에 의해 데이터로 저장되며, 8i 버전부터 제공한 스태츠팩(STATPACK) 데이터또한 사용자가 필요에 의해 설치해야 했던 스태츠팩과 달리 데이터베이스 생성 후 오픈시 별도의 설치 작업 없이 자동으로 수행되어서 메모리 모니터링 백그라운드 프로세스에 의해 데이터가 수집되어 SYSAUX 테이블스페이스에 저장된다.
AWR 스냅샷은 기본적으로 1시간 주기로 수행되고 7일간 보관되며 수집 및 보관 주기의 변경이 가능하다. 사용자는 이를 이용하여 기존 사용 툴 못지 않은 데이터를 수집할 수 있으며, 이렇게 수집된 데이터를 이용하여 성능 문제 분석 및 운영 예측 작업을 수행할 수 있다. AWR에의 수집되는 다음과 같다.
DB 대기 이벤트 및 통계정보
시스템 통계 정보
데이터베이스 부하 정보
SQL 수행 정보
활동 세션 정보
AWR 데이터는 WRH$_* 또는 WRM$_* 테이블에 저장되며 DBA_HIST_* 딕셔너리를 통해 조회할 수 있다. AWR에 의해 저장되는 각 항목에 대해 간간히 알아보도록 하자.
-------------------------------------------------------------------------------
AWR 스냅샷 생성
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPTHOT
([FLUSH_LEVEL => 'flush_level']);
END;
/
AWR 스냅샷 삭제
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
(LOW_SNAP_ID => snap_id, HIGH_SNAP_ID => snap_id [DBID => dbid ]);
END;
/
AWR 스냅샷 설정 변경
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
([RETENTION +> retention_time][, INTERVAL => interval_time][,
TOPNSQL => topn_sql_number]);
END ;
/
베이스라인 설정
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE
( START_SNAP_ID => snap_id, END_SNAPID => snap_id,
BASELINE_NAME => 'baseline_name' [,DBID => dbid]
END;
/
베이스라인 삭제
BEGIN
DBMS_WORKLOAD_REPOSITRY.DROP_BASELINE
(BASELINE_NAME => 'baselie_name'[, CASCADE => true|false ][,DBID => dbid]);
END;
/
-------------------------------------------------------------------------------
AWR 스냅샷 생성
주기적으로 수행되는 AWR 스냅샷 이외에 원하는 시점에 AWR 스냅샷을
수행해야 할 경우 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
프로시저를 이용하여 스냅샷을 수동으로 실행시킬 수 있다.
다음의 예는 FLUSH_LEVEL을 TYPICAL, ALL로 설정하여 스냅샷을 수행한 예제다 .
TYPICAL 옵션으로 AWR 데이터 수집
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
ALL 옵션으로 AWR 데이터 수집
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (FLUSH_LEVEL => 'ALL');
FLUSH_LEVEL AWR 스냅샷 수행 시 통계 수집 레벨을 설정하며, 기본값은 TYPICAL이다.
STATISTICS_LEVEL 초기화 파라미터
STATISTICS_LEVEL 초기화 파라미터는 오라클 10g 부터 새로 추가되었으며 AWR 데이터 수집 레벨을 설정한다.
TYPICAL : AWR 기본 통계 데이터 수집 . 기본값
ALL : AWR 기본 통계 데이터 + 추가 Os 통계 + 추가 실행 계획 통계
BASIC : 통계수집 하지 않음
-------------------------------------------------------------------------------
AWR 스냅샷 삭제
AWR 스냅샷 데이터는 SYSAUX 테이블 스페이스에 저장된다.
SQL>
BEGIN
DBMS_WORKLOAD_REPOSITORY.
DROP_SNAPSHOT_RANGE (LOW_SNAP_ID => 1, HIGH_SNAP_ID => 10);
END;
/
-------------------------------------------------------------------------------
AWR 스냅샷 설정 변경
데이터베이스 생성후 AWR 스냅샷은 자동으로 수행되며 , 이때 기본 값은 다음과 같다.
기본 설정값을 변경하기 위해서는 아래의 예제와 같이 프로시저를 수행하면 된다.
SQL>
BEGIN
DBMS_WORKLOA_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (
INTERVAL => 30, RETENTION => 60*24*60, TOPNSQL =>'100');
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (INTERVAL => 15, RETENTION => 8*24*60, TOPNSQL =>'100');
END;
/
execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7);
출처: <https://dbaclass.com/article/ora-13541-system-moving-window-baseline-size-greater-retention/>
위의 예제는 스냅샷 수행주기를 15분, 보관기간을 8일(60*24*60),
수집되는 상위 SQL의 수를 100개로 변경한 예제다.MODIFY_SNAPSHOT_SETTING 프로시저에서 사용할 수 있는 옵션은 다음과 같다.
INTERVAL:스냅샷 수행 주기를 분 단위로 지정한다. 기본 값은 NULL이며,
이 경우 기존 설정 값이 유지된다.
NUMBER 타입 변수를 사용한다.
RETENTION : AWR 스냅샷 보관기간을 분 단위로 지정한다. 기본 값은 NULL이며, 이 경우 기존 설정 값이 유지 된다. NULBER 타입 변수를 사용한다.
TOPSQL: Elapse 시간, CPU 사용 시간, Parse Call 횟수, Sharable Memory,
Version Count의 항목에 대해 각각 수집되는 SQL 문의 수를 지정한다.
각 항목의 크기를 내림차순 정렬해서 설정한 개수만큼 저장된다.
NUMBER 타입 및 VARCHAR2 타입의 변수를 사용한다. 기본 값은 NULL이며, 이 경우 기존 설정 값을 유지한다.
DBID 데이터베이스 ID 지정 기본값은 NULL이다.
이 경우 프로시저를 수행한 데이터베이스가 선택된다.
-------------------------------------------------------------------------------
AWR 베이스라인 생성과 삭제
AWR 데이터를 이용한 성능 분석 시 튜닝 전후 비교나 정상 구간과 문제 구간 비교 등 여러 가지 이유로 기준 구간을 설정할 필요가 있다.
오라클 10g에서는 AWR 스냅샷에 의해 수집된 통계 수치의 비교를 위한 기준 구간을 지정하고 저장할 수 있으면, 이 기준 구간을 베이스 라인이라고 한다.
BASE LINE: AWR 수치 비교를 위해 기준 구간으로 지정하는 AWR 스냅샷 구간
예) 15시부터 18시까지 수행된 배치 작업의 수행 속도가 나빠서 이를 기준으로 튜닝 전과 튜닝 후의 통계치를 비교하기 위해서 아래 예저와 같이 베이스라인을 설정할 수 있다. 예제에서 15시 스냅샷 ID는 2004번이고 , 18시 스냅샷 ID는 2006번 이다.
SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
START_SNAP_ID => 2004, END_SNAPID => 2006,
BASELINE_NAME => 'BAD_BATCH', DBID = 1134139816);
END;
/
-------------------------------------------------------------------------------
AWR 데이터 백업
AWR 관리 주기 및 테이블스페이스 공간 부족 등으로 AWR 데이터를 삭제할 경우, 삭제 대상 AWR 데이터를 백업해야 할 필요가 발생할 수 있다.
이때, 다음 예제와 같이 awrextr.sql 스크립트를 수행하면, 데이터 펌프를 이요하여 AWR 데이터를 덤프 파일로 저장할 수 있다. 추후 필요 시
있다. AWR 데이터 백업 방법에 대해서 단계별로 알아보도록하자.
1.Awrextr.sql을 실행 한다.
SQL>@?/rdbms/admin/awrextr.sql
2.DBID를 입력한다. 백업받고자 하는 AWR 스냅샷을 저장하고 있는 데이터베이스 ID를 입력한다.
Enter value for dbid: 1970448782
3. 백업하려는 스냅샷 리스트 보여줄 날짜를 선택한다. 현재 날짜로부터 며칠 전의 스냅샷 리스트를 보여줄지 입력한다.
1을 선택시 최근 24시간동안의 스냅샷 리스트가 뜬다.
Enter value for num_days : 1
4.백업하려는 스냅샷 구간을 선택한다.
Enter value for begin_snap :718
Begin Snapshot Id specified : 718
Enter value for end_snap: 719
End Snapshot Id specfied: 719
5.export pump 사용 시 사용할 디렉토리를 설정한다.
데이터 펌프를 사용하기 위해서는 최소한 하나의 오라클 디렉토리 오브젝트가 생성되어 있어야한다.
Enter value for directory_name: AWR_TEST
6.덤프 파일의 이름을 지정한다.
Enter value for file_name : awrdump_node2_720_721
Using the dump file prefix: awrdump_node2_718_719.dmp
-------------------------------------------------------------------------------
AWR 데이터 복구
Awrextr.sql로 백업받은 AWR 데이터를 awrload.sql 스크립트를 사용하여 따른다.
- 백업된 AWR 데이터가 저장되어 있던 데이터베이스에 적재할 수 없으므로 백업 데이터를 활용하기 위해서는 다른 데이터베이스에 적재해야 한다.
-------------------------------------------------------------------------------
AWR 관련 스크립트는 다음의 디렉토리에 위치한다.
$ORACLE_HOME/rdbms/admin
사용용도
- 스크립트
AWR DB 보고서 생성
- awrrpt.sql, awrrpti.sql
AWR DB 비교 보고서 생성
- awrddrpt.sql, awrddrpi.sql
AWR SQL 보고서 생성
-- awrsqrpt.sql , awrsqrpi.sql
AWR 정보 검색 보고서
-- awrinfo.sql
ASH 보고서 생성
--ashrpt.sql, ashrpti.sql
ADDM 보고서 생성
-- addmrpt.sql, addmrpti.sql
기타 스크립트
--awrddinp.sql,awrinput.sql,awrinpnmp.sql
-------------------------------------------------------------------------------
Export/Import
Export 할려는 DB에서
1. Export 할려는 DB에서Awrextr.sql을 실행 한다.
SQL>@?/rdbms/admin/awrextr.sql
2.DBID를 입력한다. 백업받고자 하는 AWR 스냅샷을 저장하고 있는 데이터베이스 ID를 입력한다.
Enter value for dbid: 1970448782
3. 백업하려는 스냅샷 리스트 보여줄 날짜를 선택한다. 현재 날짜로부터 며칠 전의 스냅샷 리스트를 보여줄지 입력한다.
1을 선택시 최근 24시간동안의 스냅샷 리스트가 뜬다.
Enter value for num_days : 1
4.백업하려는 스냅샷 구간을 선택한다.
Enter value for begin_snap :718
Begin Snapshot Id specified : 718
Enter value for end_snap: 719
End Snapshot Id specfied: 719
5.export pump 사용 시 사용할 디렉토리를 설정한다.
데이터 펌프를 사용하기 위해서는 최소한 하나의 오라클 디렉토리 오브젝트가 생성되어 있어야한다.
Enter value for directory_name: AWR_TEST
6.덤프 파일의 이름을 지정한다.
Enter value for file_name : awrdump_node2_720_721
Using the dump file prefix: awrdump_node2_718_719.dmp
7.Filezillar 등으로 dmp file를 이동시킨다.
-단 TOBE Dbserver에서 오라클 디렉토리가 생성된 디렉토리로 옮길것
SQL>@?/rdbms/admin/awrload.sql
-.dmp file를 read하여 읽을 수 있게 import함.
Enter value for directory_name: DATA_PUMP_DIR
-원하는 .dmp file이 있는 오라클 디렉토리를 입력
Enter value for file_name: awrdata_30_40
-원하는 .dmp file의 파일명 입력 .dmp는 생략
Enter value for schema_name: AWR_STAGE
-awr데이터를 보관하기위한 새 schema 생성
Enter value for default_tablespace: SYSAUX
-새 스키마의 테이블 스페이스 지정
Enter value for temporary_tablespace: TEMP
-새 스키마의 템프 테이블 스페이스 지정
출처: <https://pat98.tistory.com/851>