Statspack & AWR

• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900671&
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

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

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

• 인스턴스 효율성에 관한 리포트. (설명은 v$sysstat에서 다룸)
• Execute to Parse % 항목을 제외하면 모두 100%에 가까운 수치를 보여야 정상.
• 위에서 Parse CPU to Parse Elapsed % 항목이 0.85로 비정상적으로 낮은 수치를 보인 것은, Active 프로세스가 동시에 폭증하면서 과도한 Parse Call이 발생한 장애 상황에서 측정했기 때문.

• Shared Pool 사용량 통계
• AWR리포트 구간 시작 시점의 Shared Pool 메모리 상황과 종료 시점에서의 메모리 상황을 보여준다. (앞에서 설명)

• 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'를 참고하였습니다.

문서정보

• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900671&
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.