꿈꾸는 개발자, DBA 커뮤니티 구루비

AWR: Automatic Workload Repository

구성하기

자동으로 설치 및 구성
(주의)statistics_level이 basic일 경우 자동으로 스냅샷을 찍지 않는다.
AWR 설정

구분	옵션명	내용	최소값	최대값	기본값
1	스냅샷 간격(snap interval)	두 스냅샷 사이의 간격을 분 단위로 설정	10분	100년	1시간
2	보존기간(retention)	스냅샷을 보존하는 기간, 분 단위 설정	1일	100년	8일(11.1)
3	Top SQL(Top n SQL)	자원을 가장 많이 사용한 SQL 저장 개수	30	50,000 또는 MAXIMUM	DEFAULT(30 또는 100)

SQL ID를 coloerd로 마킹 시 스냅샷을 찍을 때마다 해당 SQL을 반드시 수집(dbms_workload_repository 패키지의 add_colored_sql)
AWR 설정 확인 및 변경


SQL> SELECT SNAP_INTERVAL, RETENTION, TOPNSQL
FROM dba_hist_wr_control;

SNAP_INTERVAL     RETENTION         TOPNSQL
-------------     ---------         ----------
+00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

EXEC dbms_workload_repository.modify_snapshot_settings(
    interval => 20
   ,retention => 35*60*24
   ,topnsql => 'DEFAULT'
);

--AWR 용량 확인
SQL> SELECT OCCUPANT_DESC, SPACE_USAGE_KBYTES
FROM v$sysaux_occupants
WHERE occupant_name = 'SM/AWR';

OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Automatic Workload Repository                        1009216

스냅샷 찍기

자동 스냅샷뿐만 아니라 수동으로 스냅샷 가능
dbms_workload_repository 패키지의 create_snapshot(프로시저, 함수 둘 다 제공)


SQL> SELECT dbms_workload_repository.create_snapshot(flush_level => 'ALL') AS snap_id
FROM dual;

SNAP_ID
---------
    57592

--flush_level: 1)TYPICAL: 30개 2)ALL: 100개 SQL 저장

SQL> SELECT begin_interval_time
      ,end_interval_time
      ,decode(snap_level, 1 , 'typical', 2, 'all', snap_level) as snap_level
  FROM dba_hist_snapshot
WHERE SNAP_ID = 57592;

BEGIN_INTERVAL_TIME  END_INTERVAL_TIME    SNAP_LEVEL
-------------------- -------------------- ----------------------------------------
2017/11/13 13:00:01  2017/11/13 13:45:01  all

베이스라인 관리

고정 베이스라인	고정값인 시작 스냅샷 ID와 종료 스냅샷 ID로 구분되는 연속된 스냅샷 집합 필요한 만큼 생성 가능
이동 윈도우 베이스라인	가장 최근의 스냅샷으로 끝나며 일정 기간을 다루는 연속된 스냅샷의 집합 모든 데이터베이스는 하나의 이동 윈도우를 가지며, 어댑티브 임계치(adaptive thresholds) 기능을 위해 데이터베이스 엔진에 의해 사용

고정 베이스라인


SQL> EXEC dbms_workload_repository.create_baseline(
  start_snap_id => 57563
 ,end_snap_id   => 57572
 ,baseline_name => 'OFFICE_HOURS'
 ,expiration    => 30
);

SQL> SELECT  start_snap_id, start_snap_time, end_snap_id, end_snap_time, expiration
FROM dba_hist_baseline
WHERE baseline_name = 'OFFICE_HOURS'
    AND baseline_type = 'STATIC';

START_SNAP_ID START_SNAP_TIME      END_SNAP_ID END_SNAP_TIME        EXPIRATION
------------- -------------------- ----------- -------------------- ----------
        57563 2017/11/12 09:00:22        57572 2017/11/12 18:00:34          30


SQL> SELECT METRIC_NAME, METRIC_UNIT, MINIMUM, AVERAGE, MAXIMUM
FROM table(dbms_workload_repository.select_baseline_metric('OFFICE_HOURS'))
ORDER BY metric_name;

METRIC_NAME                                                      METRIC_UNIT                                                      MINIMUM   AVERAGE   MAXIMUM
---------------------------------------------------------------- ---------------------------------------------------------------- --------- --------- ---------
Active Parallel Sessions                                         Sessions                                                                 0         0         0
Active Serial Sessions                                           Sessions                                                                 0  1.0e+000         4
Average Active Sessions                                          Active Sessions                                                          0  4.9e-002  1.5e+000
Average Synchronous Single-Block Read Latency                    Milliseconds                                                             0  5.0e-004  1.3e-001
Background CPU Usage Per Sec                                     CentiSeconds Per Second                                                  0  3.3e-001  5.7e+001
Background Checkpoints Per Sec                                   Check Points Per Second                                                  0  2.8e-005  1.7e-002
Background Time Per Sec                                          Active Sessions                                                          0  1.4e-002  1.2e+000
Branch Node Splits Per Sec                                       Splits Per Second                                                        0  8.3e-005  3.3e-002
Branch Node Splits Per Txn                                       Splits Per Txn                                                           0  3.0e-005  1.1e-002
Buffer Cache Hit Ratio                                           % (LogRead - PhyRead)/LogRead                                            0  9.6e+001  9.9e+001
CPU Usage Per Sec                                                CentiSeconds Per Second                                                  0  3.1e+000  4.9e+001
CPU Usage Per Txn                                                CentiSeconds Per Txn                                                     0  1.9e+001  1.1e+002
CR Blocks Created Per Sec                                        Blocks Per Second                                                        0  8.5e-003  1.5e-001
CR Blocks Created Per Txn                                        Blocks Per Txn                                                           0  3.2e-002  8.6e-001
CR Undo Records Applied Per Sec                                  Undo Records Per Second                                                  0  1.7e-002  1.7e-001
CR Undo Records Applied Per Txn                                  Records Per Txn                                                          0  6.3e-002  8.6e-001
Captured user calls                                              calls                                                                    0         0         0
Cell Physical IO Interconnect Bytes                              bytes                                                                    0  1.1e+008  2.1e+009
Consistent Read Changes Per Sec                                  Blocks Per Second                                                        0  5.3e-001  1.1e+002
Consistent Read Changes Per Txn                                  Blocks Per Txn                                                           0  2.7e+000  9.4e+002

(중간생략)

User Rollback UndoRec Applied Per Sec                            Records Per Second                                                       0  1.7e-002  8.3e-002
User Rollbacks Per Sec                                           Rollbacks Per Second                                                     0         0         0
User Rollbacks Percentage                                        % (UserRollback/TotalUserTxn)                                            0         0         0
User Transaction Per Sec                                         Transactions Per Second                                                  0  2.0e-001  4.8e+000
Workload Capture and Replay status                               status                                                                   0         0         0

--Baseline 이름 변경
SQL> EXEC dbms_workload_repository.rename_baseline(
   old_baseline_name => 'OFFICE_HOURS'
  ,new_baseline_name => 'OFFICE_HOURS_DELETED'
);

SQL> SELECT  baseline_name, start_snap_id, start_snap_time, end_snap_id, end_snap_time, expiration
FROM dba_hist_baseline
WHERE baseline_name LIKE 'OFFICE_HOURS%'
    AND baseline_type = 'STATIC';

BASELINE_NAME           START_SNAP_ID START_SNAP_TIME      END_SNAP_ID END_SNAP_TIME        EXPIRATION
----------------------- ------------- -------------------- ----------- -------------------- ----------
OFFICE_HOURS_DELETED            57563 2017/11/12 09:00:22        57572 2017/11/12 18:00:34          30

--Baseline 이름 삭제
SQL> EXEC dbms_workload_repository.drop_baseline(
   baseline_name => 'OFFICE_HOURS_DELETED'
  ,cascade       => TRUE
);

SQL> SELECT  baseline_name, start_snap_id, start_snap_time, end_snap_id, end_snap_time, expiration
FROM dba_hist_baseline
WHERE baseline_name LIKE 'OFFICE_HOURS%'
    AND baseline_type = 'STATIC';

BASELINE_NAME                                                    START_SNAP_ID START_SNAP_TIME      END_SNAP_ID END_SNAP_TIME        EXPIRATION
---------------------------------------------------------------- ------------- -------------------- ----------- -------------------- ----------

0 rows selected.

이동 윈도우 베이스라인

윈도우 크기만 변경 가능: dbms_workload_repository 패키지의 modify_baseline_window_size


SQL> SELECT  BASELINE_NAME, BASELINE_TYPE, MOVING_WINDOW_SIZE
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';

BASELINE_NAME                                                    BASELINE_TYPE MOVING_WINDOW_SIZE
---------------------------------------------------------------- ------------- ------------------
SYSTEM_MOVING_WINDOW                                             MOVING_WINDOW                  8

EXEC dbms_workload_repository.modify_baseline_window_size(window_size => 30);