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

Introduction
(1) Statspack / AWR 기본 사용법
(2) Statspack / AWR 리포트 분석

Introduction

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을 효과적으로 활용할 수 있음


Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            750,167.58              5,155.22
              Logical reads:            159,411.54              1,095.49
              Block changes:              4,078.04                 28.02
             Physical reads:              5,464.77                 37.55
            Physical writes:                259.31                  1.78
                 User calls:             12,031.58                 82.68
                     Parses:                206.10                  1.42
                Hard parses:                 40.98                  0.28
                      Sorts:                156.75                  1.08
                     Logons:                  1.16                  0.01
                   Executes:             12,943.28                 88.95
               Transactions:                145.52

  % Blocks changed per Read:    2.56    Recursive Call %:     15.96
 Rollback per transaction %:    0.77       Rows per Sort:    230.08

Per Second는 각 측정 지표 값들을 측정 시간(Snapshot interval, 초)으로 나눈 것으로 초당 부하를 의미함
Per Transaction은 각 측정 지표 값들을 트랜잭션 개수로 나눈 것으로 한 트랜잭션 내에서 평균적으로 얼만큼의 부하가 발생하였는지를 나타내는 것인데, 트랜잭션 개수가 commit 또는 rollback 수행 횟수를 단순히 더한 값이어서 의미 없는 수치로 받아들여질 때가 종종 있음
(조회 위주의 시스템이라면 I/O 수치는 계속 누적되는 반면 commit 발생 횟수는 적기 때문에 트랜젝션당 Logical reads와 Physical reads 항목이 매우 높게 나타남)
실제 업무적인 의미에서의 트랜잭션과 괴리가 있다는 사실과, 본인이 관리하는 시스템의 특성을 이해한 상태에서 수치를 해석해야 함

% Block changed per Read : 읽은 블록 중 갱신이 발생하는 비중
Rollback per transaction % : 최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중
Recursive Call % : 전체 Call 발생 횟수에서 Recursive Call이 차지하는 비중
(사용자 정의 함수/프로시저를 많이 사용하면 이 수치가 높아지며, 하드파싱에 의해서도 영향을 받음)
Rows per Sort : 소트 수행 시 평균 몇 건씩 처리했는지를 나타냄

Load Profile만 가지고 시스템의 부하 정도를 말할 수 있을까?
AWR에서 보여지는 항목들을 v$sysstat 뷰를 이용한 SQL


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');

인스턴스 효율성에 관한 리포트 (설명은 v$sysstat에서 다룸)
Execute to Parse % 항목을 제외하면 모두 100%에 가까운 수치를 보여야 정상임
Execute to Parse % 의 수치는 커서 공유가 어려운 3-Tier 환경에서 대부분 낮게 나타나므로,웹(Web)과 같이 완전한 3-Tier 환경에서는 이 수치만으로 적정성을 판단할 수 없음
Shared Pool Statistics는 Shared Pool 사용량 통계
- AWR리포트 구간 시작 시점의 Shared Pool 메모리 상황과 종료 시점에서의 메모리 상황을 보여줌


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.98       Redo NoWait %:    100.00
            Buffer  Hit   %:   96.58    In-memory Sort %:    100.00
            Library Hit   %:  102.27        Soft Parse %:     80.12
         Execute to Parse %:   98.41         Latch Hit %:     96.77
Parse CPU to Parse Elapsd %:   26.59     % Non-Parse CPU:     98.40

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   94.83   93.37
    % SQL with executions>1:    6.04    2.94
  % Memory for SQL w/exec>1:    8.40    8.53

Top 5 Timed Events는 AWR 리포트 구간 동안 누적 대기 시간이 가장 컸던 대기 이벤트 5개를 보여줌(Idle 이벤트 제외)
CPU time은 대기 이벤트가 아니며 원활하게 일을 수행했던 Service time이지만, 가장 오래 대기를 발생시켰던 이벤트와의 점유율을 서로 비교해 볼 수 있도록 Top 5 대기 이벤트에 포함해 보여줌


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free                                     58,155,054     191,025    48.97
db file sequential read                        14,810,436      86,675    22.22
wait list latch free                            4,008,189      62,555    16.04
CPU time                                                       36,631     9.39
db file scattered read                          2,709,204       3,985     1.02

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 대기 이벤트 분석에 의한 성능 진단의 한계)


Response Time = Service Time   +   Wait Time
              = CPU Time       +   Queue Time

위 공식에 의하면 CPU time %와 Wait time %를 더한 값이 100을 넘을 수 없지만 비정상적이 장애 상황에서 100%를 넘을 수 있음