CH03.오라클 성능관리 - 07. Response Time Analysis 방법론과 OWI 08.Statspack / AWR

07.Response Time Analysis 방법론과 OWI

 

대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법,과정을 '대기이벤트 기반' 또는 'Response Time Analysis' 성능관리 방법론이라고 합니다.

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

서비스시간은 프로세스가 정상적으로 동작하여 일을 수행한 시간을 말합니다.(CPU Time과 같은 의미)

Wait Time은 대기 이벤트가 발생해 수행을 잠시 멈추고 대기한 시간을 말합니다(Queue Time과 같은 의미)

 

OWI는 Response Time Anaysis 방법론을 지원하려고 오라클이 제공하는 기능과 인터페이스를 통칭하는 말로써, 'Oracle Wait Interface'의 줄임말입니다.

Response Time Analysis 방법론에 기반한 튜닝은 병목해소 과정이라고 요약할 수 있습니다.

예를 들어 아래 쿼리를 수행하는 20개 프로세스가 동시에 떠서 작업을 수행 중입니다. 하루 저녁에 1억 건을 처리해야 하는 배치 프로그램이어서 속도를 향상시키려고 서로 읽는 범위를 달리하여 프로그램 Parallel 방식으로 수행되도록 한 것입니다.

 

insert into t1

select /*+ ordered use_nl(t3) */ seq.nextval,t2.*,t3.*

from t2,t3

where t2.key = t3.key

and t2.col between :range1 and :range2;

 

20개를 동시에 수행시켰는데도 생각처럼 속도가 나지 않아 이벤트 분석을 해보니 db file scattered read 대기 이벤트가 Wait time의 대부분을 차지하고 있었습니다. 이 이벤트의 발생 원인은 Full Table Scan입니다.

분석 결과, t2 테이블 기준으로 NL 조인을 수행하면서 반복 액세스가 일어나는 t3 테이블 조인 컬럼에 인덱스가 없어 매번 full table scan으로 처리되고 있었습니다. 따라서 인덱스를 추가해 정상적인 Index Scan으로 처리되도록 튜닝을 실시했습니다.

 

그랬더니 이번에는 buffer busy waits와 latch : cache buffer chains이벤트가 새롭게 발생했습니다.

앞에서는 디스크 I/O 때문에 서버 프로세스의 처리속도가 매우 느렸었는데, 이제 많은 처리를 버퍼 캐시 내에서 할 수 있게 됨으로 인해 서버 프로세스의 처리 속도가 크게 향상되었고 그 때문에 버퍼 블록에 대한 동시 엑세스가 증가하면서 메모리 경합이 발생하기 시작한 것입니다.

캐싱된 버퍼블록에 대한 읽기 요청이 많아 생기는 문제이므로 블록 요청 횟수를 줄여야 합니다. NL 조인 -> 해시 조인으로 바꿔보았습니다.

 

이제 버퍼 캐시에서 발생하던 경합이 해소됨으로 인해 또 다른 문제가 발생하였습니다. select 경합이 해소되니까 이번에는 insert 처리부분에서 경합이 발생하기 시작한 것입니다. insert에 의한 Redo 레코드 생성 속도가 증가하니까 Redo 로그 버퍼 공간이 부족할 때 발생하는 log beffer space 대기 이벤트가 새롭게 발생하기 시작했습니다. 또한 Sequence 테이블에 대한 경합으로 enq : SQ - contention까지 발생하기 시작했습니다. 내재돼 있던 문제들이 앞선 문제를 해결하면서 드러나고 있는 것입니다.

 

Redo 로그 버퍼 크기를 늘려주고, Sequence 캐시 사이즈를 10에서 20으로 늘려주었습니다.

 

이제 모든 병목구간이 모두 제거돼 속도가 빠르게 개선된 예를 살펴보았습니다.

이처럼 OWI에 기반한 튜닝은 모니터링과 튜닝을 반복하면서 병목을 해소해 나가는 방법론입니다. 버전이 올라갈수록 점점 세분화되고 있습니다.

 

 

08.Statspack / AWR

앞에서 v$sysstat과 v$system_event를 주기적으로 수집해 성능관리에 활용하는 사례를 보여주었는데 8i부터 사용하던 Statspack과 10g이후 사용하게 된 AWR도 같은 원리를 적용해 표준화된 방식으로 성능관리를 지원하려고 오라클이 제공하는 패키지입니다.

이들 패키지는 앞에서 설명한 Ratio 기반 성능진단과 Wait Event 기반 성능진단 방법론 둘 다 가지고 있습니다.

아래 나열한 V$ 뷰를 주기적으로 특정 Repository에 저장하고, 이를 분석해 오라클 데이터베이스 전반의 건강 상태를 체크하고 병목원인과 튜닝 대상을 식별해 내는데 사용합니다.

v$segstat

v$undostat

v$latch

v$latch_children

v$sgastat

v$pgastat

v$system_event

v$waitstat

v$sql

v$sql_plan

v$sqlstats

v$active_session_history

v$osstat

등등

 

Statspack과 AWR은 거의 같은 내용을 담고 있으며, 다른점은 정보를 수집하는 방식에 있습니다.

Statspack은 SQL을 이용한 딕셔너리 조회 방식입니다.

AWR은 DMA방식으로 SGA를 직접 액세스하기 때문에 좀 더 빠르게 정보를 수집할 수 있습니다. 부하가 적기 때문에 AWR은 Statspack보다 많은 정보를 수집하고 제공할 수 있게 되었습니다.

Statspack은 DB 부하가 심해지는 원인이 될 수 있습니다.SQL을 통해 직접 정보를 조회하고 가져오는 방식이기 때문입니다.

 

AWR은 기본설정을 변경하지 않았을 때 스냅샷 주기는 1시간이고, 일주일 간 보관됩니다.

 

(1) Statspack / AWR 기본 사용법

Statspack은 PREFSTAT계정 밑에 'stats$'로 시작되는 뷰를 통해 수집된 성능 정보들을 조회합니다.

AWR은 sys계정 밑에 'dba_hist_'로 시작되는 뷰를 이용합니다.

이들 뷰를 이용해 다양한 성능 분석자료를 보고서 형태로 뽑아 볼 수 있습니다.

-AWR 사용 시

SQL> @?/rdbms/admin/awrrpt

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:

-Statspack 사용 시 

SQL> @?/rdbms/admin/awrrpt

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

 

성능 진단 보고서를 출력할 때는 측정 구간, 시작 스냅샷 ID와 종료 스냅샷 ID가 가장 중요합니다.

 

 

(2) Statspack/AWR 리포트 분석

Statspack과 AWR 리포트에는 맨 첫 장을 보면 오라클 데이터베이스의 건강상태를 한눈에 파악해 볼 수 있는 요약보고서가 나옵니다. 이 한 장의 보고서를 정확히 해석할 수 있으면 AWR을 효과적으로 사용할 수 있는 준비가 된것입니다.

Per Second는 각 측정 지표값들을 측정 시간으로 나눈 것입니다. 따라서 초당 부하 발생량을 의미합니다.

Per Transaction은 각 측정 지표 값들을 트랜잭션 개수로 나눈 것입니다. 한 트랜잭션내에서 평균적으로 얼만큼의 부하가 발생하는지를 나타내는 것인데, 사실 트랜잭션 개수가 commit 또는 rollback수행 횟수를 단순히 더한 값이여서 의미 없는 수치로 받아들여질때가 있습니다.

해당 지표들은 dba_hist_sysstat뷰에서 얻은 결과입니다.

 

인스턴스 효율성에 관한 리포트가 그다음으로 나오며 매우 중요한 성능 지표들입니다.

 

Wait event에 관한 리포트가 나옵니다.

 

Memory와 Cache size Shared pool에 관한 통계정보가 나옵니다.