by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Statspack & AWR




08. Statspack / AWR

  • 8i부터 사용하던 Statspack과 10g 이후 사용하게 된 AWR(Automatic Workload repository)도 주기적으로 동적 성능 뷰를 수집하여 표준화된 방식으로 성능관리를 지원하려고 오라클이 제고하는 패키지다.
  • 이들 패키지는 Ratio기반 성능진단과 Wait Event 기반 성능진단 방법론을 둘 다 가지고 있다.
  • 아래 동적 성능 뷰(Dynamic Performance View)를 주기적으로 특정 Repository에 저장하고, 이를 분석해 오라클 데이터베이스 전반의 건강 상태ㄹ르 체크하고 병목원인과 튜닝 대상을 식별해 내는 데 사용한다.
  • v$segstat
  • v$undostat
  • v$latch
  • v$latch_children
  • v$sgastat
  • v$pgastat
  • v$sysstat
  • v$system_event
  • v$waitstat
  • v$sql
  • v$sql_plan
  • v$splstats(10g이후)
  • v$active_session_history(10g 이후)
  • v$osstat(10g 이후)
  • Statspack은 SQL을 이용한 딕셔너리 조회방식.
  • AWR은 DMA(Direct Memory Access)방식으로 SGA를 직접 액세스.(빠르게 정보를 수집) 부하가 적기 때문에 Statspack 보다 더 많은 정보를 수집하고 제공할 수 있다.
  • 오라클 9i에서는 Statspack의 정보를 수집하는데 따른 부하 때문에 스냅샷을 자주 수행하기 어려웠다. 사용자가 수동이나 정해진 기간 동안만 JOB에 등록해 DB 성능 정보를 수집.
  • 10g AWR부터는 자동으로 성능 자료를 수집해 일정기간 보관한다.
    (스냅샷 주기 : 1시간, 보관주기 : 1주일 - 설정변경가능)

(1) Statspack / AWR 기본 사용법

  • Statspack : PERFSTAT 계정 밑에 'stats$'로 시작하는 뷰를 통해 수집된 성능 정보를 조회.
  • AWR에서는 SYS 계정 밑에 'dba_hist_'로 시작하는 뷰를 이용.
  • 표준화된 보고서 출력
    SQL>@?/rdbms/admin/awrrpt <--- ? AWR
    SQL>@?/rdbms/admin/spreport <--- ? Statspack
  • 성능 진단 보고서를 출력할 때는 측정구간, 즉 시작스냅샷 ID와 종료스냅샷 ID를 어떻게 입력하느냐가 가장 중요하다.
  • 매일매일 시스템의 Load Profile의 비교 목적이라면 업무시간을 기준으로 뽑고, 문제점을 찾아 성능 이슈를 해결할 목적이라면 peak 시간대 또는 장애가 발생한 시점을 전후해 가능한 한 짧은 구간을 선택해야 한다.
  • AWR뷰를 직접 쿼리해 하루 동안의 각 통계항목별 성능추이와 이벤트 발생 현황을 볼 수 있다.
  • 정해진 기간동안 각 구간별로 SQL 실행횟수를 뽑아오는 쿼리
    select to_char(min(s.begin_interval_time), 'hh24:mi') begin
    , to_char(min(s.end_interval_time),'hh24:mi') end
    , sum(b.value-a.value) "execute count"
    from dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot s
    where s.instance_number = &instance_number
    and s.snap_id between &gegin_snap and &end_snap
    and a.stat_name = 'execute count'
    and b.stat_id = a.stat_id
    and b.snap_id = s.snap_id
    and a.snap_id = b.snap_id - 1
    and a.instance_number = s.instance_number
    group by s.snap_id
    order by s.snap_id;

(2) Statspack / AWR 리포트 분석

  • Statspack과 AWR 리포트에 맨 첫 장을 보면 오라클 데이터베이스의 상태를 한눈에 파악해 볼 수 있는 요약보고서가 나온다. 그 한장의 보고서를 정확히 해석할수만 있다면 AWR을 효과적으로 활용할 수 있다.
    Loar Profile Per Second Per Transaction
    Redo size : 140,839.60 5,345.24
    Logical reads : 47,768.26 1,812.93
    Block changes : 711.34 27.00
    Physical reads : 736.69 27.96
    Physical writes : 84.69 3.21
    User calls : 2,401.63 91.15
    Parses : 412.66 15.66
    Hard parses : 1.49 0.06
    Sorts : 138.94 5.27
    Logons : 0.79 0.03
    Executes : 1,187.18 45.06
    Transactions : 26.35  
  • Per Second는 각 측정 지표 값들을 측정 시간(Snapshot interval, 초)으로 나눈 것.(초당 부하)
  • Per Transaction은 각 측정 지표 값들을 트랜잭션 개수로 나눈 것이다. 한 트랜잭션 내에서 평균적으로 얼만큼의 부하가 발생하였는지를 나타내는 것인데, 트랜잭션 개수가 commit 또는 rollback 수행 횟수를 단순히 더한 값이어서 의미 없는 수치로 받아들여질 때가 종종 있다.
    (조회 위주의 시스템이라면 I/O 수치는 계속 누적되는 반면 commit 발생 횟수는 적기 때문에 트랜젝션당 Logical reads와 Physical reads 항목이 매우 높게 나타난다)
  • 실제 업무적인 의미에서의 트랜잭션과 괴리가 있다는 사실과, 본인이 관리하는 시스템의 특성을 이해한 상태에서 수치를 해석해야 한다.
  • AWR에서 보여지는 항목들을 v$sysstat 뷰를 이용한 쿼리.
    select value rsiz from v$sysstat where name = 'redo size';
    select value gets from v$sysstat where name = 'session logical reads';
    select value chng from v$sysstat where name = 'db block changes';
    select value phyr from v$sysstat where name = 'physical reads';
    select value phyw from v$sysstat where name = 'physical writes';
    select value ucal from v$sysstat where name = 'user calls';
    select value prse from v$sysstat where name = 'parse count (total)';
    select value hprse from v$sysstat where name = 'parse count (hard)';
    select srtm + srtd from
    (select value srtm from v$sysstat where name = 'sorts (memory)' ),
    (select value srtd from v$sysstat where name = 'sorts (disk)' );
    select value logc from v$sysstat where name = 'logons cumulative';
    select value exe from v$sysstat where name = 'execute count';
    select ucom + urol from
    (select value ucom from v$sysstat where name = 'user calls'),
    (select value urol from v$sysstat where name = 'user rollbacks');
% Blocks changed per Read : 1.49 Recursive Call % : 35.33
Rollback per transaction % : 3.81 Rows per Sort : 274.24
  • % Block changed per Read : 읽은 블록 중 갱신이 발생하는 비중.
    select round(100*chng/gets, 2) "% Blocks changed per Read"
    from
    (select value chng from v$sysstat where name = 'db block changes'),
    (select value gets from v$sysstat where name = 'session logical reads');
  • Rollback per transaction % : 최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중.
    select round(100*urol/(ucom+urol), 2) "Rollback per transaction %"
    from
    (select value ucom from v$sysstat where name = 'user calls'),
    (select value urol from v$sysstat where name = 'user rollbaks');
  • Recursive Call % : 전체 Call 발생 횟수에서 Recursive Call이 차지하는 비중.
    (사용자 정의 함수/프로시저를 많이 사용하면 이 수치가 높아지며, 하드파싱에 의해서도 영향을 받는다)
    select round(100*recr/(recr+ucal), 2) "Recursive Call %"
    from
    (select value recr from v$sysstat where name = 'recursive calls'),
    (select value ucal from v$sysstat where name = 'user calls');
  • Rows per Sort : 소트 수행 시 평균 몇 건씩 처리했는지를 나타낸다.
    select decode((srtm+srtd), 0, to_number(null), round(srtr/(srtm+srtd),2))
    from
    (select value srtm from v$sysstat where name = 'sorts (memory)'),
    (select value srtd from v$sysstat where name = 'sorts (disk)'),
    (select value srtr from v$sysstat where name = 'sorts (rows)');
Instance Efficiency Percentages (Target 100%)      
Buffer Nowait % : 99.99 Redo NoWait % : 100.00
Buffer Hit % : 98.71 In-memory Sort % : 100.00
Library Hit % : 99.67 Soft Parse % : 99.64
Execute to Parse % : 65.24 Latch Hit % : 99.89
Parse CPU to Parse Elapsd % : 0.85 % Non-Parse CPU : 97.96
  • 인스턴스 효율성에 관한 리포트. (설명은 v$sysstat에서 다룸)
  • Execute to Parse % 항목을 제외하면 모두 100%에 가까운 수치를 보여야 정상.
  • 위에서 Parse CPU to Parse Elapsed % 항목이 0.85로 비정상적으로 낮은 수치를 보인 것은, Active 프로세스가 동시에 폭증하면서 과도한 Parse Call이 발생한 장애 상황에서 측정했기 때문.
Shared Pool Statistics Begin End
Memory Usage % : 69.20 93.96
% SQL with executions > 1 : 93.40 98.29
% Memory for SQL w/exec > 1 73.36 98.99
  • Shared Pool 사용량 통계
  • AWR리포트 구간 시작 시점의 Shared Pool 메모리 상황과 종료 시점에서의 메모리 상황을 보여준다. (앞에서 설명)
Top 5 Timed Events     Avg
Wait
(ms)
%Total
Call
Time
 
~~~~~~~~~~~~~~~~          
Event Waits Time(s)     Wait Class
Latch free 2,169,850 596,104 275 70.2 Other
Latch: shared pool 1,050,870 262,298 250 30.9 Concurrenc
Latch: library cache 868,920 219,076 252 25.8 Concurrenc
Db file sequential read 18,869,172 108,189 6 12.7 User I/O
CPU time   48,991   5.8  
  • Top 5 Timed Events는 AWR 리포트 구간 동안 누적 대기 시간이 가장 컸던 대기 이벤트 5개를 보여준다.(Idle 이벤트 제외)
  • 위 리포트는 Active 프로세스가 동시에 폭증하면서 과도한 Parse Call을 일으키고 OS 레벨에서 Paging까지 심하게 발생했던 장애 상황에서 측정한 것이다.
  • CPU time은 대기 이벤트가 아니며 원활하게 일을 수행했던 Service time이지만, 가장 오래 대기를 발생시켰던 이벤트와의 점유율을 서로 비교해 볼 수 있도록 Top 5 대기 이벤트에 포함해 보여주고 있다.
    Response Time = Service Time + Wait Time
    = CPU Time + Queue Time
  • 위 공식에 의하면 CPU time %와 Wait time %를 더한 값이 100을 넘을 수 없지만 위 사례는 비정상적이 장애 상황이어서 그런지 100%를 넘었다.
  • CPU time이 Total Call Time에서 차지하는 비중이 가장 높아 Top 1에 위치한다면 일단 DB의 건강상태가 양호하다는 청신호인 셈이다. 반대로 CPU time 비중이 아래쪽으로 밀려날수록 어딘가 이상이 발생했다는 적신호로 받아들여야 한다.
  • 서비스가 정상적으로 수행된 시간대에 AWR 리포트를 뽑더라도 CPU time을 제외하고 항상 4개의 대기 이벤트가 나열된다.
  • 예를 들어, 래치나 Lock관련 대기 이벤트 순위가 상위로 매겨졌다면, 문제가 발생했음을 나타내는 위험 신호일 가능성이 높지만 래티의 경우는, CPU 사용률까지 같이 분석해 봐야 한다. 래치 경합은 CPU 사용률을 높이는 주원인이므로, 그 당시 CPU 사용률이 높지 않았다면 다른 이벤트보다 상대적으로 많이 발생한 것에 불과 할 수 있다.
  • 트랜잭션 처리 위주의 시스템이라면 log file sync 대기 이벤트가 Top 5 내에 포함되었다고 무조건 이상 징후로 보기 어렵다.(이벤트가 많이 발생한 것만으로 불필요한 커밋을 자주 날렸다고 판단해서는 않된다)
  • I/O 관련 대기 이벤트가 상위로 올라오는 것은, 상황에 따라 다르게 해석해야 한다. 데이터베이스는 I/O 집약적인 시스템이므로 db_file_sequential read, db_file_scattered_read 대기 이벤트가 상위에 매겨지는 게 정상이다. 다만, 이 두 대기 이벤트가 CPU time 보다 높은 점융율을 차지하고, OS 모니터링 결과 CPU 사용률도 매우 높은 상황이 지속된다면 I/O 튜닝이 필요한 시스템일 가능성이 높다.
  • 대기 이벤트 발생 현황만을 놓고 보면 별 문제가 없어 보이지만 실제 사용자가 느끼는 시스템 성능은 매우 느린 경우가 많다. 아무리 peak time 전후로 리포트 구간을 짧게 가져가더라도 시스템 레벨로 측정한 값이기 때문에 그렇다.(Top-N 대기 이벤트 분석에 의한 성능 진단의 한계)

문서에 대하여

  • 최초작성자 : [김종원]
  • 최초작성일 : 2009년 11월 26일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.