Response Time Analysis 방법론과 OWI

(1) Response Time Analysis 방법론

CPU time과 Wait time을 각각 break down하면서 서버의 일량과 대기 시간을 분석한다.

1. Response Time

✅ Response Time 공식

• Response Time = Servic Time + Wait Time
  또는
• Response Time = CPU Time + Queue Time

• 서비스 시간(Servic Time 또는 CPU time) : 프로세스가 정상적으로 동작하며 일을 수행한 시간
• 대기 시간(Wait Time 또는 Queue Time) :: 대기 이벤트가 발생해 수행을 잠시 멈추고 대기한 시간

(2) OWI(Oracle Wait Interface)

• Response Time Analysis 방법론을 지원하려고 오라클이 제공하는 기능과 인터페이스를 통칭한다.
• Response Time Analysis 방법론에 기반한 튜닝의 요약은 병목해소 과정이다.

💡서버의 일량과 대기 시간 분석 예제

아래 쿼리를 수행하는 20개 프로세스가 동시에 떠서 작업을 수행하는 상황

INSERT INTO t1
SELECT /*+ ordered use_nl(t3) */ seq.nextbal, t2.*, t3.*
FROM t2, t3
WHERE t2.key = t3.key
and t2.col between :range1 and :range2; 

[첫번째 상황 분석]

1-1. 상황 분석

• db file scattered read 대기 이벤트가 Wait time의 대부분을 차지한다.
  → Full Table Scan 상황

1-2. 원인

• t2 테이블 기준으로 NL 조인을 수행하면서 반복 액세스가 일어나는 t3 테이블 조인 컬럼에 인덱스가 없어 매번 Full Table Scan으로 처리하고 있기 때문이다.

1-3. 조치

• 인덱스를 추가해 정상적인 Index Scan으로 처리한다.

[두번째 상황 분석]

첫번째 조치가 끝났지만 새로운 상황이 아래와 같이 발생한다고 가정한다.

2-1. 상황 분석

• buffer busy waits과 latch: cache buffers chains 이벤트가 새롭게 발생하게 된다.

  ✅ 대기 이벤트 종류

  • buffer busy waits : Exclusive 모드로 점유된 상태의 버퍼 블록 헤더의 Lock 대기자 목록(Waiter List)에 등록 후 래치를 해제한 뒤 선행 버퍼 Lock 해제시 획득하는 블록 단위의 버퍼 Lock을 얻는 과정에서 일어난다.
  • latch: cache buffers chains : 버퍼 캐시를 사용하기 위해 해시 체인을 탐색하거나 변경하기 위해 해당 체인을 관리하는 cache buffers chains 래치를 획득하는 과정에서 일어난다.

2-2. 원인

• 서버 프로세스의 처리 속도가 크게 향상되면서 버퍼 블록에 대한 동시 액세스가 증가하면서 메모리 경합이 발생하기 때문이다.

2-3. 조치

• 캐싱된 버퍼 블록에 대한 읽기 요청이 많아 생기는 문제이므로 블록 요청 횟수를 줄여야 한다. 따라서 NL조인을 해시 조인 방식으로 변경한다.

[세번째 상황분석]

두번째 조치가 끝났지만 또 새로운 상황이 다음과 같이 발생한다고 가정한다.😅

3-1. 상황 분석

• log buffer space와 enq:SQ-contention 이벤트가 새롭게 발생한다.

3-2. 원인

• select 경합이 해소되면서 insert에 의한 Redo 레코드 생성 속도가 증가하면서 Redo 로그 버퍼 공간이 부족하고 Sequence 테이블에 대한 경합이 발생한다.

3-3. 조치

• Redo 로그 버퍼 크기를 약간 늘려준다.
• Sequence 캐시 사이즈를 10에서 20으로 늘린다.

(3) Response Time Analysis 방법론을 지원하는 오라클

• 버전을 거듭할 수록 대기 이벤트는 세분화 되고 있고, 유용한 동적 성능 뷰도 계속 증가하고 있다.
• 10g부터는 쿼리를 이용하지 않고 직접 SGA 메모리를 액세스하기 때문에 더 많은 정보 수집이 가능하다.
• Response Time Analysis 방법론을 지원하는 오라클 표준도구
  • Statspack
  • AWR