쉽게 설명한 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
위의 실행결과는 스냅샷이 매 시간대 별로 수집되고 있으며 수집된 통계가 7일 동안 보관되고 있음을 보여주고 있습니다. 스냅샷 주기를 20분으로,
보관 주기를 2일로 변경하기 위해서는 아래와 같이 수행하면 됩니다. (매개변수는 분 단위로 표시됩니다.)
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 뷰의 레코드에 대한 조회 결과의 예가 아래와 같습니다:
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
----- ---------- ------------ ------- ------- -------- ----------
... 후략 ...
위 조회 결과를 통해 백 분의 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의 클라이언트의
하나로서 관리됩니다.
타임 모델 (Time Model)
성능 문제가 발생했을 때, 응답시간을 줄이기 위한 방법으로 DBA의 머릿속에 가장 먼저 떠오르는 것은 무엇일까요? 말할 필요도 없이, 문제의 근본원인을
찾아내어 제거하는 것이 최우선일 것입니다. 그렇다면 얼마나 많은 시간이 (대기가 아닌) 실제 작업에 사용되었는지 어떻게 확인할 수 있을까요?
Oracle Database 10g는 여러 가지 자원에 관련한 실제 사용 시간을 확인하기 위한 타임 모델(time model)을 구현하고 있습니다. 전체 시스템 관련 소요
시간 통계는 V$SYS_TIME_MODEL 뷰에 저장됩니다. V$SYS_TIME_MODEL 뷰에 대한 쿼리 결과의 예가 아래와 같습니다.
STAT_NAME VALUE
------------------------------------- --------------
DB time 58211645
DB CPU 54500000
background cpu time 254490000
sequence load elapsed time 0
parse time elapsed 1867816
hard parse elapsed time 1758922
sql execute elapsed time 57632352
connection management call elapsed time 288819
failed parse elapsed time 50794
hard parse (sharing criteria) elapsed time 220345
hard parse (bind mismatch) elapsed time 5040
PL/SQL execution elapsed time 197792
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 593992
Java execution elapsed time 0
bind/define call elapsed time 0
위에서 DB time이라는 통계정보는 인스턴스 시작 이후 데이타베이스가 사용한 시간의 누적치를 의미합니다. 샘플 작업을 실행한 다음 다시 뷰를 조회했을
때 표시되는 DB time의 값과 이전 값의 차이가 바로 해당 작업을 위해 데이타베이스가 사용한 시간이 됩니다. 튜닝을 거친 후 DB time 값의 차이를 다시
분석하면 튜닝을 통해 얻어진 성능 효과를 확인할 수 있습니다.
이와 별도로 V$SYS_TIME_MODEL 뷰를 통해 파싱(parsing) 작업 또는 PL/SQL 컴파일 작업에 소요된 시간 등을 확인할 수 있습니다. 이 뷰를 이용하면
시스템이 사용한 시간을 확인하는 것도 가능합니다. 시스템 / 데이타베이스 레벨이 아닌 세션 레벨의 통계를 원한다면 V$SESS_TIME_MODEL 뷰를 이용할
수 있습니다. V$SESS_TIME_MODEL 뷰는 현재 연결 중인 active/inactive 세션들의 통계를 제공합니다. 세션의 SID 값을 지정해서 개별 세션의 통계를
확인할 수 있습니다.
이전 릴리즈에서는 이러한 통계가 제공되지 않았으며, 사용자들은 여러 정보 소스를 참고해서 근사치를 추측할 수 밖에 없었습니다.
Active Session History
Oracle Database 10g의 V$SESSION에도 개선이 이루어졌습니다. 가장 중요한 변화로 wait 이벤트와 그 지속시간에 대한 통계가 뷰에 추가되어,
V$SESSION_WAIT를 별도로 참조할 필요가 없게 되었다는 점을 들 수 있습니다. 하지만 이 뷰가 실시간 정보를 제공하므로, 나중에 다시 조회했을
때에는 중요한 정보가 이미 사라져 버리고 없을 수 있습니다. 예를 들어 wait 상태에 있는 세션이 있음을 확인하고 이를 조회하려 하면, 이미 wait 이벤트가
종료되어 버려 아무런 정보도 얻지 못하는 경우가 있을 수 있습니다.
또 새롭게 추가된 Active Session History(ASH)는 AWR과 마찬가지로 향후 분석 작업을 위해 세션 성능 통계를 버퍼에 저장합니다. AWR과 다른 점은,
테이블 대신 메모리가 저장 매체로 이용되며 V$ACTIVE_SESSION_HISTORY 등을 통해 조회된다는 사실입니다. 데이타는 1초 단위로 수집되며, 액티브
세션만이 수집 대상이 됩니다. 버퍼는 순환적인 형태로 관리되며, 저장 메모리 용량이 가득 차는 경우 오래된 데이타부터 순서대로 삭제됩니다. 이벤트를
위해 대기 중인 세션의 수가 얼마나 되는지 확인하려면 아래와 같이 조회하면 됩니다:
select session_id||','||session_serial# SID, n.name, wait_time, time_waited from v$active_session_history a, v$event_name n where n.event# = a.event#
위 쿼리는 이벤트 별로 대기하는 데 얼마나 많은 시간이 사용되었는지를 알려 줍니다. 특정 wait 이벤트에 대한 드릴다운을 수행할 때에도 ASH 뷰를
이용할 수 있습니다. 예를 들어, 세션 중 하나가 buffer busy wait 상태에 있는 경우 정확히 어떤 세그먼트에 wait 이벤트가 발생했는지 확인하는 것이
가능합니다. 이때 ASH 뷰의 CURRENT_OBJ# 컬럼과 DBA_OBJECTS 뷰를 조인하면 문제가 되는 세그먼트를 확인할 수 있습니다.
ASH 뷰는 그 밖에도 병렬 쿼리 서버 세션에 대한 기록을 저장하고 있으므로, 병렬 쿼리의 wait 이벤트를 진단하는 데 유용하게 활용됩니다. 레코드가
병렬 쿼리의 slave process로서 활용되는 경우, coordinator server session의 SID는 QC_SESSION_ID 컬럼으로 확인할 수 있습니다. SQL_ID 컬럼은 wait
이벤트를 발생시킨 SQL 구문의 ID를 의미하며, 이 컬럼과 V$SQL 뷰를 조인하여 문제를 발생시킨 SQL 구문을 찾아낼 수 있습니다. CLIENT_ID 컬럼은 웹
애플리케이션과 같은 공유 사용자 환경에서 클라이언트를 확인하는 데 유용하며, 이 값은 DBMS_SESSION.SET_IDENTFIER를 통해 설정 가능합니다.
ASH 뷰가 제공하는 정보의 유용성을 감안하면, AWR과 마찬가지로 이 정보들을 영구적인 형태의 매체에 저장할 필요가 있을 수도 있습니다. AWR 테이블을
MMON 슬레이브를 통해 디스크로 flush 할 수 있으며, 이 경우 DBA_HIST_ACTIVE_SESS_HISTORY 뷰를 통해 저장된 결과를 확인할 수 있습니다.
수작업으로 스냅샷 생성하기
스냅샷은 자동으로 수집되도록 디폴트 설정되어 있으며, 원하는 경우 온디맨드 형태의 실행이 가능합니다. 모든 AWR 기능은 DBMS_WORKLOAD_REPOSITORY
패키지에 구현되어 있습니다. 스냅샷을 실행하려면 아래와 같은 명령을 사용하면 됩니다:
execute dbms_workload_repository.create_snapshot
위 명령은 스냅샷을 즉각적으로 실행하여 그 결과를 table WRM$_SNAPSHOT 테이블에 저장합니다. 수집되는 메트릭의 수준은 TYPICAL 레벨로 설정됩니다.
더욱 자세한 통계를 원하는 경우 FLUSH_LEVEL 매개변수를 ALL로 설정하면 됩니다. 수집된 통계는 자동으로 삭제되며, 수작업으로 삭제하려는 경우
drop_snapshot_range() 프로시저를 실행하면 됩니다.
베이스라인
성능 튜닝 작업을 수행할 때에는 먼저 일련의 메트릭에 대한 베이스라인(baseline)을 수집하고 튜닝을 위한 변경 작업을 수행한 뒤, 다시 또 다른 베이스라인
셋을 수집하는 과정을 거치는 것이 일반적입니다. 이렇게 수집된 두 가지 셋을 서로 비교하여 변경 작업의 효과를 평가할 수 있습니다. AWR에서는 기존에
수집된 스냅샷을 통해 이러한 작업이 가능합니다. 예를 들어 매우 많은 자원을 사용하는 apply_interest라는 프로세스가
이 기간 동안 스냅샷 ID 56에서 59까지가 수집되었다고 합시다. 이 스냅샷들을 위해 apply_interest_1이라는 이름의 베이스라인을 아래와 같이 정의할 수 있습니다:
exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')
위 명령은 스냅샷 56에서 59까지를 ‘apply_interest_1’이라는 이름의 베이스라인으로 표시합니다. 기존에 설정된 베이스라인은 아래와 같이 확인합니다:
select * from dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568 1 apply_interest_1 56 59
튜닝 과정을 거친 후, 또 다른 이름(예: apply_interest_2)의 베이스라인을 생성하여, 이 두 가지 베이스라인에 해당하는 스냅샷의 메트릭을 비교할 수 있습니다.
이처럼 비교 대상을 한정함으로써 성능 튜닝의 효과를 한층 향상시킬 수 있습니다. 분석이 끝나면 drop_baseline(); 프로시저로 베이스라인을 삭제할 수 있습니다
(이 때 스냅샷은 그대로 보존됩니다). 또 오래된 스냅샷이 삭제되는 과정에서, 베이스라인과 연결된 스냅샷은 삭제되지 않습니다
'쉽게 설명한 게시물 시리즈' 카테고리의 다른 글
| 쉽게 설명한 Transportable Tablespaces (0) | 2011.06.22 |
|---|---|
| 쉽게 설명한 Scheduler (0) | 2011.06.22 |
| 쉽게 설명한 Materialized Views (0) | 2011.06.22 |
| 쉽게 설명한 Automatic Shared Memory Management (0) | 2011.06.22 |
| 쉽게 설명한 ADDM과 SQL Tuning Advisor (0) | 2011.06.22 |