Introduction

대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법 또는 과정
Queing 이론이란?

• Cpu를 할당 받아 일하는 시간과 Cpu를 할당 받기 위해 기다리는 시간이 존재함

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

• 오라클 서버의 응답 시간을 서비스 시간과 대기 시간의 합으로 정의
• 서비스 시간(Service Time)은 프로세스가 정상적으로 동작하여 일을 수행한 시간을 의미하며 CPU Time과 같음
• 대기 시간(Wait Time)은 대기 이벤트가 발생해 수행을 잠시 멈추고 대기한 시간을 의미하며 다른말로 Queue Time이라고도 함
• CPU Time은 파싱 작업에 소요된 시간인지 쿼리 본연의 오퍼레이션 수행을 위해 소요된 시간인지 분석
• Wait Time은 각각 발생한 대기 이벤트들을 분석해 가장 시간을 많이 빼앗긴 이벤트 중심으로 해결방안을 모색
• OWI는 Response Time Analysis 방법론을 지원하려고 오라클이 제공하는 기능과 인터페이스를 통칭하는 말
• Response Time Analysis 방법론에 기반한 튜닝은 병목해소 과정

예시)

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개 프로세스가 동시에 떠서 작업을 수행
• 하루 저녁에 1억 건을 처리해야 하는 배치 프로그램
• 서로 읽는 범위를 달리하여 프로그램 Parallel 방식으로 수행

(그림 3-4)
문제1 : db file scattered read 대기 이벤트가 Wait time의 대부분을 차지

• 원인 : t2 테이블 기준으로 NL 조인을 수행하면서 반복 액세스가 일어나는 t3 테이블 조인 컬럼에 인덱스가 없어 매번 Full Table Scan으로 처리되고 있음
• 해결 : 인덱스를 추가해 정상적인 Index Scan으로 처리되도록 튜닝

(그림 3-5)
문제2 : buffer busy waits과 latch: cache buffers chains 이벤트가 새롭게 발생

• 원인 : 문제1의 해결으로 많은 처리를 버퍼 캐시 내에서 할 수 있게 됨으로 인해 서버 프로세스의 처리 속도가 크게 향상되어 그 때문에 버퍼 블록에 대한 동시 액세스가 증가하면서 메모리 경합이 발생하기 시작한 것
• 해결 : 캐싱된 버퍼 블록에 대한 읽기 요청이 많이 생기는 문제이므로 블록 요청 횟수를 줄이기 위해 NL 조인을 해시 조인 방식으로 변경

(그림 3-6)
문제3 : log buffer space, enq:SQ - contention 이벤트가 새롭게 발생

• 원인 : select 경합이 해소되니 insert 처리부분에서 경합이 발생(insert에 의한 Redo 레코드 생성 속도가 증가하면서 Redo 로그 버퍼 공간이 부족해 졌으며, Sequence 테이블에 대한 경합 발생)
• 해결 : Redo 로그 버퍼 크기를 약간 늘려주고, Sequence 캐시 사이즈 10에서 20으로 증가시킴

• OWI에 기반한 튜닝은 모니터링과 튜닝을 반복하면서 병목을 해소해 나가는 방법
• 10g 기준으로 890여 개, 11g 기준 960여 개의 방대한 대기 이벤트가 정의돼 사용
• 버전이 올라갈수록 사용할 수 있는 유용한 동적 성능 뷰도 계속 증가
• 10g부터는 쿼리를 이용하지 않고 직접 SGA메모리를 액세스하기 때문에 더 많은 정보수집이 이루어 짐

참조문서

서적(오라클 성능 고도화 원리와해법 I)