1st_Statspack & AWR (by niceboyous) [2012.10.27]
h1.Introduction
Statspack / AWR
- Statspack : Oracle database에 대한 부하 및 resource 사용량의 trend 분석이나
성능 문제 분석을 위하여 사용되는 Tool로 oracle 8i부터 제공되는 Tool
- AWR : Oracle Database 10g 이후 구성 요소가 문제 감지 및 자체 튜닝 목적을 위해
통계를 수집, 유지 관리 및 활용할 수 있도록 서비스를 제공 하는 infrastructure
데이터베이스 통계,metric 등에 대한 데이터 웨어하우스로 간주할수 있다.
기본적으로 데이터베이스는 SGA에서 60분 마다 자동으로 통계 정보를 캡쳐하여 스냅샷의 형태로 AWR에 저장합니다. 이러한 스냅샷은 MMON(Manageability Monitor)이라는 백그라운드 프로세스에 의해 디스크에 저장됨.
또 기본적으로 스냅샷은 7일간 보관되며, 스냅샷 간격과 retention간격은 모두 수정가능함.
- Ratio 기반 성능진단과 Wait Event 기반 성능진단 방법론을 둘다 가지고 있다.
- 아래 나열한 동적 성능 뷰를 주기적으로 특정 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 과 AWR은 거의 같은 내용을 담고 있으며 , 다른점이 있다면 정보를 수집하는 방식에 있다.
h3.Statspack / AWR 기본 사용법
성능 진단 보고서를 출력할 때는 측정 구간, 즉 시작 스냅샷 ID와 종료 스냅샷 ID를 어떻게 입력하느냐가 가장 중요하다. Load Profile을 비교할 목적이라면 하루 업무 시간을 기준으로 뽑아도 상관없지만 성능 이슈를 해결할 목적이라면 peak 시간대 또는 장애가 발생한 시점을 준후해 한 짧은 구간을 선택해야한다.
아래는 정해진 기간 동안 각 구간별로 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;
h3.Statspack /AWR 리포트 분석
Statspack와 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는 각 측정 지표값들을 측정 시간 으로 나눈것. 따라서 초당 부하 발생량을 의미.
Per Transaction은 각 측정 지표 값들을 트랜잭션 개수로 나눈 것. 한 트랜잭션내에서 평균적으로 얼마만큼의 부하가 발생하는지를 나타낸것인데, 트랜잭션 개수가 commit 또는 rollback 수행 횟수를 단순히 더한값이어서 의미 없는 수치로 받아들여질때가 있다.
위에서 보여지는 항목들은 dba_hist_sysstat 뷰에서 얻은 결과이며 각각 어떤 항목을 조회했는지를 안다면 각각의 이미를 어렵지 않게 이해할 수 있다.
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');
Load Profile 다음에는 아래 항목들이 나오게됨.
% Blocks changed per Read : 읽은 블록 중 갱신이 발생하는 비중을 나타냄
Recursive Call % : 전체 Call 발생 횟수에서 Recursive Call이 차지하는 비중을 나타낸다. 사용자 정의 함수/프로시저를 많이 사용하면 이수치가 높아지며, 하드파싱에 의해서도 영향을 받음.
Rows per Sort : 소트 수행 시 평균 몇 건씩 처리했는지를 나타낸다.
이 이후의 리포트에서는 인스턴스 효율성에 관한 정보가 나오며, 매우 중요한 성능지표임
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
일반론적으로는 Execute to Parse % 항목을 제외하면 모두 100%에 가까운 수치를 보이는것이 정상임.
인스턴스 효율성에 이어, Top 5 Timed Events로 넘어가기전에 Shared Pool 사용통계가 나옴.
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
Shared Pool 사용통계는 AWR 리포트 구간 시작 시점의 Shared Pool 메모리 상황과 종료 시점에서의 메모리 상황을 보여줌.
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
Top 5 Timed Events는 AWR 리포트 구간 동안 누적 대기 시간이 가장 컸던 대기 이벤트 5개를 보여줌.
위의 리포트는 Active 프로세스가 동시에 폭증하면서 과도한 Parse Call을 일으키고 OS레벨에서 PAGING까지 심하게 발생했던 장애 상황에서 측정한 것.
Response Time 의 공식을 생각하면 CPU TIME %와 Wait Time %를 더한 값이 100을 넘을 수 없지만 위 사례는 비정상적인 장애 상황이어서 100%가 넘어있음.
CPU time이 Total Call Time에서 차지하는 비중이 가장 높아 Top 1에 위치한다면 일단 DB의 건강상태가 양호하다는 청신호 이지만 반대로 비중이 가장 낮아 아래쪽으로 밀려날수록 이상이 발생했다는 신호이다.
또한 아래의 내용은 Top 5 Timed Events를 통해 정보를 파악할때 주의를 가지고 보아야할 사항이다
서비스가 정상적으로 수행된 시간대에 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 전후로 리포트 구간을 짧게 가져가더라도 시스템 레벨로 측정한 값이기 때문이다.