Automatic Workload Repository

Automatic Workload Repository
AWR을 이용하여 분석과 튜닝을 위한 데이타베이스 성능 통계정보와 메트릭을 수집하고, 데이타베이스에서 사용한 정확한 시간을 확인하거나 세션 정보를 저장할 수 있습니다. 

데이타베이스에 성능에 관련한 문제가 생겼을 때, 귀하가 DBA로서 가장 먼저 취하는 조치는 무엇입니까? 아마도 문제에 일정한 패턴이 존재하는지 확인하는 것이 가장 일반적인 접근방법의 하나일 것입니다. “동일한 문제가 반복되는가?”, “특정한 시간대에만 발생하는가?”, 또는 “두 가지 문제에 연관성이 있는가?” 등의 질문을 먼저 제기해 봄으로써 보다 정확한 진단을 수행할 수 있습니다. 

Oracle DBA들은 데이타베이스 운영에 관련한 통계정보를 수집하거나 성능 메트릭(metric)을 추출하기 위해 써드 파티 툴, 또는 직접 개발한 툴을 사용하고 있습니다. 이렇게 수집된 정보는, 문제 발생 이전과 이후의 상태를 비교하는 데 이용됩니다. 과거에 발생했던 이벤트들을 재현해 봄으로써 현재 문제를 다양한 관점【?분석할 수 있습니다. 이처럼 관련 통계정보들을 지속적으로 수집하는 것은 성능 분석에서 매우 중요한 작업 중의 하나입니다. 

오라클은 한동안 이를 위해 Statspack이라는 이름의 빌트-인 툴을 제공하기도 했습니다. Statspack은 상황에 따라 매우 유용하긴 했지만, 성능 관련 트러블슈팅 과정에서 요구되는 안정성이 결여되었다는 문제가 있었습니다. Oracle Database 10g는 성능 통계정보의 수집과 관련하여 그 기능이 비약적으로 향상된 Automatic Workload Repository(AWR)을 제공합니다. AWR은 데이타베이스와 함께 설치되며, 기본적인 통계정보뿐 아니라 통계정보로부터 유추된 메트릭(derived metric)도 함께 수집합니다. 

간단하게 테스트해 보기
AWR을 이용한 새로운 기능들은 $ORACLE_HOME/rdbms/admin 디렉토리의 awrrpt.sql 스크립트를 실행하고, 수집된 통계정보와 메트릭 정보를 바탕으로 생성된 리포트를 확인함으로써 가장 쉽게 이해할 수 있습니다. awrrpt.sql 스크립트는 Statspack과 유사한 구조로 되어있습니다. 먼저 현재 저장된 AWR 스냅샷을 모두 표시한 후, 시간 간격 설정을 위한 입력값을 요구합니다. 출력은 두 가지 형태로 제공됩니다. 텍스트 포맷 출력은 Statspack 리포트와 유사하지만, AWR 리포지토리를 기반으로 하며 (디폴트로 제공되는) HTML 포맷을 통해 section/subsection으로 구분된 하이퍼링크를 제공하는 등 사용자 편의성을 강화하였다는 점에서 차이를 갖습니다. 먼저 awrrpt.sql 스크립트를 실행하여 리포트를 확인해 보시고, AWR의 기능에 대한.특성을 이해하시기 바랍니다. 

구현 원리
이제 AWR의 설계방식과 구조에 대해 알아보기로 합시다. AWR은 수집된 성능관련 통계정보가 저장되며 이를 바탕으로 성능 메트릭을 제공함으로서 잠재적인 문제의 원인 추적을 가능하게 해주는 근간을 제공해 줍니다. Statspack의 경우와 달리, Oracle10g는 AWR을 활용하여 새로운 MMON 백그라운드 프로세스와, 여러 개의 슬레이브 프로세스를 통해 자동적으로 매시간별 스냅샷 정보를 수집합니다. 공간 절약을 위해, 수집된 데이타는 7일 후 자동으로 삭제됩니다. 스냅샷 빈도와 보관 주기는 사용자에 의해 설정 가능합니다. 현재 설정값을 보기 위해서는 아래와 같이 명령을 수행하면 됩니다:

select snap_interval, retention
from   dba_hist_wr_control;

SNAP_INTERVAL  RETENTION
----------------  ----------------
+00000 01:00:00.0   +00007 00:00:00.0

위의 실행결과는 스냅샷이 매 시간대 별로 수집되고 있으며 수집된 통계가 7일 동안 보관되고 있음을 보여주고 있습니다. 스냅샷 주기를 20분으로, 보관 주기를 2일로 변경하기 위해서는 아래와 같이 수행하면 됩니다(매개변수는 분 단위로 표시됩니다).

begin
  dbms_workload_repository.modify_snapshot_settings(
    interval => 20,
    retention => 2*24*60);
end;

AWR은 수집된 통계를 저장하기 위해 여러 개의 테이블을 사용합니다. 이 테이블들은 모두 SYS 스키마의 SYSAUX 테이블스페이스 내에 저장되어 있으며, WRM$_* 또는 WRH$_*의 네임 포맷을 갖습니다. WRM$_* 테이블은 수집 대상 데이타베이스 및 스냅샷에 관련한 메타데이타 정보를, WRH$_* 테이블은 실제 수집된 통계 정보를 저장하는데 사용됩니다. (예측하시는 바와 같이, H는 “historical”, M은 “metadata”의 약자를 의미합니다.) 이 테이블을 기반으로 DBA_HIST_라는 prefix를 갖는 여러 가지 뷰가 제공되고 있으며, 이 뷰들을 응용하여 자신만의 성능 분석 툴을 만들 수도 있습니다. 뷰의 이름은 테이블 이름과 직접적인 연관성을 갖습니다. 예를 들어 DBA_HIST_SYSMETRIC_SUMMARY 뷰는 WRH$_SYSMETRIC_SUMMARY 테이블을 기반으로 합니다. 

AWR 히스토리 테이블은 Statspack에서는 수집되지 않았던 다양한 정보(테이블스페이스 사용 통계, 파일시스템 사용 통계, 운영체제 통계 등)를 제공합니다. 테이블의 전체 리스트는 아래와 같이 데이타 딕셔너리 조회를 통해 확인할 수 있습니다: 

select view_name

from   user_views

where view_name like 'DBA_HIST_%' escape ''; 

DBA_HIST_METRIC_NAME 뷰는 AWR에 수집되는 주요 메트릭과 메트릭이 속한 그룹, 그리고 수집 단위(unit) 등을 정의하고 있습니다. DBA_HIST_METRIC_NAME 뷰의 레코드에 대한 조회 결과의 예가 아래와 같습니다:

DBID                  : 4133493568
GROUP_ID          : 2
GROUP_NAME    : System Metrics Long Duration
METRIC_ID         : 2075
METRIC_NAME   : CPU Usage Per Sec
METRIC_UNIT     : CentiSeconds Per Second

위에서는 "초당 CPU 사용량(CPU Usage Per Sec)" 메트릭이 “100분의 1초(CentiSeconds Per Second)” 단위로 수집되고 있으며, 이 메트릭이 "System Metrics Long Duration” 그룹에 속함을 확인할 수 있습니다. 이 레코드를 DBA_HIST_SYSMETRIC_SUMMARY와 JOIN하여 실제 통계를 확인할 수 있습니다:

select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation sd

from   dba_hist_sysmetric_summary where metric_id = 2075;

BEGIN  INTSIZE  NUM_INTERVAL MINVAL  MAXVAL  AVERAGE  SD
------  --------  ---------------  -------  ---------  ---------  ----------
11:39    179916     30                       0          33              3            9.81553548
11:09    180023     30                      21         35             28            5.91543912
...

위 조회 결과를 통해 백 분의 1초 단위로 CPU 자원이 어떻게 소비되고 있는지 확인할 수 있습니다. SD(standard deviation, 표준 편차) 값을 참조하면 계산된 평균 값이 실제 부하와 비교하여 얼마나 오차를 갖는지 분석 가능합니다. 첫 번째 레코드의 경우, 초당 백 분의 3초의 CPU 시간이 소모된 것으로 계산되었지만, 표준 편차가 9.81이나 되므로 계산된 3의 평균값이 실제 부하를 정확하게 반영하지 못하는 것으로 볼 수 있습니다. 반면 28의 평균값과 5.9의 표준 편차를 갖는 두 번째 레코드가 실제 수치에 더 가깝다고 볼 수 있습니다. 이러한 트렌드 분석을 통해 성능 메트릭과 환경 변수의 상관 관계를 보다 명확하게 이해할 수 있습니다. 

통계의 활용
지금까지 AWR의 수집 대상이 어떻게 정의되는지 알아보았습니다. 이번에는 수집된 데이타를 어떻게 활용할 수 있는지 설명하기로 합니다. 

성능 문제는 독립적으로 존재하는 경우가 거의 없으며, 대개 다른 근본적인 문제를 암시하는 징후로서 해석되는 것이 일반적입니다. 전형적인 튜닝 과정의 예를 짚어보기로 합시다: DBA가 시스템 성능이 저하되었음을 발견하고 wait에 대한 진단을 수행합니다. 진단 결과 “buffer busy wait”이 매우 높게 나타나고 있음을 확인합니다. 그렇다면 문제의 원인은 무엇일까요? 여러 가지 가능성이 존재합니다: 인덱스의 크기가 감당할 수 없을 만큼 커지고 있을 수도 있고, 테이블의 조밀도(density)가 너무 높아 하나의 블록을 메모리에 읽어 오는 데 요구되는 시간이 제한된 때문일 수도 있고, 그 밖의 다른 이유가 있을 수 있습니다. 원인이 무엇이든, 문제가 되는 세그먼트를 먼저 확인해 보는 것이 필요합니다. 문제가 인덱스 세그먼트에서 발생했다면, 리빌드 작업을 수행하거나 reverse key index로 변경하거나 또는 Oracle Database 10g에서 새로 제공하는 hash-partitioned index로 변경해 볼 수 있을 것입니다. 문제가 테이블에서 발생했다면, 저장 관련 매개변수를 변경해서 조밀도를 낮추거나, 자동 세그먼트 공간 관리(automatic segment space management)가 설정된 테이블스페이스로 이동할 수 있을 것입니다. 

DBA가 실제로 사용하는 접근법은 일반적인 방법론, DBA의 경험 및 지식 등을 그 바탕으로 합니다. 만일 똑같은 일을 별도의 엔진이, 메트릭을 수집하고 사전 정의된 로직을 바탕으로 적용 가능한 방법을 추론하는 엔진이 대신해 준다면 어떨까요? DBA의 작업이 한층 쉬워지지 않을까요? 

바로 이러한 엔진이 Oracle Database 10g에 새로 추가된 Automatic Database Diagnostic Monitor (ADDM)입니다. ADDM은 AWR이 수집한 데이타를 사용하여 결론을 추론합니다. 위의 예의 경우, ADDM은 buffer busy wait이 발생하고 있음을 감지하고, 필요한 데이타를 조회하여 wait이 실제로 발생하는 세그먼트를 확인한 후, 그 구조와 분포를 평가함으로써 최종적으로 해결책을 제시합니다. AWR의 스냅샷 수집이 완료될 때마다, ADDM이 자동으로 호출되어 메트릭을 점검하고 권고사항을 제시합니다. 결국 여러분은 데이타 분석 및 권고사항 제시를 담당하는 풀 타임 DBA 로봇을 하나 두고, 보다 전략적인 업무에 집중할 수 있게 된 셈입니다. 

Enterprise Manager 10g 콘솔의 “DB Home” 페이지에서 ADDM의 권고사항과 AWR 리포지토리 데이타를 확인할 수 있습니다. AWR 리포트를 보려면, Administration->Workload Repository->Snapshot의 순서로 메뉴를 따라가야 합니다. ADDM의 자세한 기능은 향후 연재에서 소개하도록 하겠습니다. 

특정 조건을 기준으로 알림 메시지를 생성하도록 설정하는 것도 가능합니다. 이 기능은 Server Generated Alert라 불리며, Advanced Queue에 푸시(push) 형태로 저장되고 리스닝 중인 모든 클라이언트에 전달되는 형태로 관리됩니다. Enterprise Manager 10g 역시 Server Generated Alert의 클라이언트의 하나로서 관리됩니다.