문제

대규모 이벤트를 시작한 A사에서 이벤트 응모자가 많은 12:00~14:00에 이벤트 관련 어플리케이션의 성능 저하 현상이 발생했다.

Top 5 Time Events

• db file sequential read는 avg walt이 8ms로 양호한 편이다.
• 성능 저하의 원인이 될만한 이벤트는 latch free, enq:TX - index contention, buffer busy waits 대기이벤트다.
• 가장 많은 경합을 발생시키는 latch free 대기 이벤트를 먼저 확인하기 위해 Latch Sleep Breakdown 단위 보고서를 점검한다.

Latch Sleep Breakdown 단위 보고서

• enqueue 래치의 슬립 횟수가 150621번으로 전체 latch free 대기 이벤트의(153885)의 97.88%를 차지한다.
• enqueue 래치가 경합 래치이고, 어떤 SQL이 enqueue 래치 경합을 발생시키는지 확인한다.
• 아래 경우는 경합 유발 SQL의 범위를 축소할 수 있다.
  • 성능 저하 어플리케이션을 정확히 파악하고 있는 경우, 해당 어플리케이션을 사용하는 SQL로 범위를 축소할 수 있다.
  • enqueue 래치 경합은 DML 수행 시에만 발생하므로, SQL문중 DML문으로 범위를 축소할 수 있다.
  • 성능 저하 구간대에 수행한 SQL문으로 범위를 축소할 수 있다.

경합 유발 SQL 확인

ASH보고서를 통한 경합 유발 SQL 확인

• 성능 저하 구간에서 ASH보고서를 생성하고 Top SQL Statements 단위보고서를 확인한다.
• 아래 SQL에서 latch free, enq:TX - index contention 대기 이벤트가 발생함을 확인할 수 있다.
  

DBA_HIST_ACTIVE_SESS_HISTORY, V$ACTIVE_SESS_HISTORY 검색

• DBA_HIST_ACTIVE_SESS_HISTORY, V$ACTIVE_SESS_HISTORY를 조회한 SQL_Stat4.sh 수행 결과에서 성능 저하를 유발하는 대기 이벤트가 발생함을 확인 할 수 있다.
  
  

성능 저하 원인 파악

• enq:TX - index contention 대기 이벤트가 발생하고 있으므로, 인덱스 구조를 확인한다.
  
• CUST_EVT_2009_I1 인덱스는 고객이 이벤트에 응모한 날짜와 시간이 선행 컬럼으로 구성되어 있으며, 이벤트 번호는 동일한 번호를 부여받는다. 인덱스 컬럼 데이터가 순차적으로 증가하며 오른쪽 확장 인덱스다.
• 오른쪽 확장 인덱스는 과부하 발생시 경합을 일으킬 확률이 매우 높으므로 이 인덱스를 래치 경합 유발 원인으로 의심해 볼 수 있다.
• 반면, CUST_EVT_2009_PK 인덱스는 분포도가 좋은 고객ID 컬럼이 선행 컬럼이므로 인덱스 경합이 발생할 확률이 낮다.
• 경합 발생 인덱스를 확인하기 위해 AWR DB보고서의 Segments by Row Lock Waits 단위보고서를 확인한다.

Segments by Row Lock Waits 단위보고서

• CUST_EVT_2009_I1 인덱스에서 Row Lock의 81.99%가 발생함을 확인할 수 있다.

해결 방안

• CUST_EVT_2009_I1 오른쪽 확장 인덱스 경합을 해소하기 위해 해쉬 파티션을 적용하여 인덱스 재생성

특이사항

• 일반적으로 오른쪾 확장 인덱스 경합시 발생하는 enq:TX - index contention 대기 이벤트가 아닌 latch free가 가장 상위 대기 이벤트로 발생한 것은 과도한 인덱스 경합으로 이전 단계인 래치 획득에서 경합이 발생한 특이한 경우로 AWR DB 보고서를 상세하게 분석하면 근본적인 문제의 원인을 파악 할 수 있다.