Oracle - Wait Event 모니터링

[ Oracle Wait Event 모니터링 ]

 

☞ [1] Oracle Wait Event 모니터링   흔히 DBA를 3D업종이라고 부르는 이유 가운데 하나는 몸은 고달픈데 반해 그 성과가 별로 티가 나지 않는다는 사실 때문일 것이다. 실제로, DBA가 수행해야 하는 일상적인 관리 업무들은 몸은 다소 피곤하게 만들지 몰라도 어느 정도 경험이 쌓이면 그리 부담을 주는 일은 아니다. 우리가 한단계 업그레이드된 전문가로서 인정 받는 DBA가 되기 위해서는 장애상황 혹은 유사 장애 상황에서 DB 모니터링 작업을 수행하고 분석할 수 있어야 한다. 시스템이 갑자기 느려지고 업무가 마비되는 상황에 맞닥뜨렸을 때 문제의 원인이 무엇인지를 집어낼 수 있는 능력이 있어야 하며 최소한 오라클의 문제인지 아닌지를 판단할 수는 있어야 몸으로 야간작업이나 때우는 DBA가 아니라 조직에 없어서는 안될 전문가로서의 나의 존재가치를 인정 받을 수 있을 것이다. 이 글에서는 오라클 Wait Event에 대하여 간단히 알아보고 일시적인 성능저하 상황에서 Wait Event를 모니터링하고 그 원인을 찾아가는 방법에 대하여 다루어 보고자 한다. 짧은 지면 위에 다룰 수 있는 내용도 제한되어 있고 글쓴이의 지식 또한 일천하지만 오라클 전문가가 되기 위해 같은 길을 가고 있는 동료로서 가진 지식 몇 가지 공유한다는 취지로 이 글을 쓴다. 오라클의 Wait Event 정보는 V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT 등이 있는데, 이 가운데 V$SESSION_WAIT는 각 세션이 현재 Waiting 하고 있는 Event나 마지막으로 Wait한 Event 정보를 보관하고 있으며, V$SYSTEM_EVENT와 V$SESSION_EVENT는 시스템이 Startup된 이후 각각 시스템 전체, 혹은 세션별로 발생한 Wait Event 정보를 누적하여 기록하고 있다. 오라클의 Wait Event는 성격에 따라 Network교신이나 IO를 위해 대기하는 일상적인 Wait와 특정 자원에 대해 여러 프로세스가 동시에 액세스하고자 할 때 발생하는 Wait, 별달리 할 일이 없어 대기하고 있는 Idle Wait 등 세가지 유형으로 구분할 수 있는데 그 유형에 따라 해석방법도 달라진다. 일단, Idle Wait는 일반적인 관심의 대상에서 제외되며 IO나 Network 관련 Wait는 작업량이 증가하면 같이 증가하는 Wait이므로 전체 서비스 시간(CPU time)과 비교하여 상대적으로 평가해야 하며 총 Wait time보다는 평균 Wait Time에 관심을 두고 분석을 해야 할 것이다. 시스템 자원에 대한 Wait는 데이터베이스 서버 튜닝시 가장 주된 관심 대상이 되며 이들 Wait에 대해서는 평균 Wait Time뿐만 아니라 총 Wait Time에도 관심을 가지고 분석해야 할 것이다. 유형별로 대표적인 Wait Event를 살펴본다면 아래와 같다. ☞ [주요 Wait Event]   구분 이벤트명 설 명 일상적인    Wait Event db file scattered read Full Scan시 OS에 I/O를 요청해놓고 대기 db file sequential read Index Scan시 OS에 I/O를 요청해놓고 대기 (IO, Network) log file sync 변경 log buffer를 log file에 반영하는 동안 대기 DFS lock handle OPS 환경에서 노드간 분산 Lock 교환에 따른 대기 global cache cr request OPS 환경에서 노드간 Buffer Block 교환에 의한 대기 자원 경합에 따른 Wait Event  enqueue Type에 따라 세분화 (24개의 enqueue type (9i)) latch free Name에 따라 세분화 (239개의 latch가 존재 (9i)) buffer busy waits 동일블록에 대한 동시 액세스에 따른 경합 free buffer waits free buffer를 할당위해 DBWR의 Write를 대기 Log buffer space Log buffer를 할당 받기 위해 LGWR의 write를 대기 library cache lock SGA내의 library cache를 참조하기 위한 대기(검색) row cache lock SGA내의 dictionary cache를 참조하기 위한 대기 Idle Event  SQL*Net message from client Client로부터의 작업요청을 대기 Pmon timer PMON이 할일 없을 때 대기하는 Event

 

☞ 업무시간대에 시스템이 갑자기 느려졌다면서 오라클 서버에 문제가 없는지 문의가 들어오면 글쓴이는    우선 아래의 SQL을 수행시켜본다.   select /*+ ordered / distinct /* 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거 */        s.sid SID, s.username, s.program, p.spid "OS-Pid",w.seconds_in_wait as "W_time(Sec)",       decode(w.wait_time,0,'Wai-ting', 'Waited') Status, w.ename event, --              p1text || ':' || decode(event,'latch free',p1raw, to_char(p1)) ||','|| --              p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3) "Additional Info",            q.sql_text from ( select a.*, decode(a.event,'latch free', 'latch free (' ||b.name||')',                            'row cache lock', 'row cache lock (' || c.parameter || ')',                             'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)||                                                     chr(bitand(p1,16711680)/65535)||':'||                     decode(bitand(p1,65535), 1, 'N', 2, 'SS',3,'SX',4,'S',5,'SSX',6,'X') ||')',                              a.event ) ename        from  v$session_wait a, v$latchname b, v$rowcache c        where a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) = 'PARENT'        and   a.event not in ('rdbms ipc message','smon timer','pmon timer','slave wait',                               'pipe get','null event',                      'SQL*Net message from client', 'SQL*Net message to client','PX Idle Wait',                      'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',                      'ges remote message', 'wakeup time manager', /* idle event 적절히 수정 */                      'lock manager wait for remote message', 'single-task message')         ) w, v$session s, v$process p, v$sql q where w.sid = s.sid and s.paddr = p.addr and s.sql_hash_value = q.hash_value(+) and s.sql_address = q.address(+) order by w.ename; SQL의 구체적인 내용이야 필요한 정보와 개인적 취향에 따라 달라지겠지만, 중요한 것은 일단 V$SESSION_WAIT 뷰로부터 실시간 Wait Event 정보를 얻어낸다는 것이다. 위 SQL을 수행했을 때 나타나는 결과가 없다면 일단 오라클 측면에서 업무성능을 심각하게 마비시키는 Waiting이 발생하고 있지 않다고 봐도 큰 무리가 없을 것이다. 일반적인 상태에서는 주로 'db file sequential read'나 'db file scattered read' 가 나타날 텐데, 이러한 Wait Event는 보통 짧은 시간 동안 지속되며 대상 자원(블록)을 바꿔가며 Wait가 반복되는 형태로 나타날 것이다. 이는 작업 처리량이 많을 때 일상적으로 발생하는 IO관련 Wait Event이므로 해당 세션에서 IO를 제법 많이 유발하고 있다는 정도로 이해하고 넘어가면 될 것이다. 물론, Wait의 지속시간이 길거나 지나치게 빈번히 나타나는 SQL에 대해서는 비효율적인 실행계획을 수립하고 있지 않은지 검토해서 튜닝해 주어야 한다. 성능저하의 원인이 오라클 쪽에 있는 경우에는 특정 자원에 대한 Waiting이 상당히 오랫동안 지속되어 현재까지 Waiting이 진행 중인 세션들(STATUS가 'Wai-ting' (wait_time=0)이며 'W_time(sec)' (seconds_in_wait) 값이 상당히 큰 세션)이 존재할 가능성이 높다. 오라클의 내부적인 작업들은 매우 짧은 기간에 처리되어야 하므로, Idle event(where절에서 not in으로 처리한 부분, 버전에 따라 달라질 수 있다.) 이외의 특정 Wait Event가 눈에 띌 정도로 검출된다는 것은 오라클 내부적으로는 훨씬 더 많은 Waiting이 발생하고 있다고 생각해야 한다. 바로 이런 세션들이 문제의 범인들이며 이제부터 DBA는 이들 Wait Event에 대한 원인을 파악하여 조치하는 작업을 해주어야 한다. 각각의 Wait Event에 따라 원인을 추적하고 조치하는 방법은 달라질 것이다. 다음 호에서는, 자주 경험하는 몇가지 대표적인 Wait Event들에 대하여 SGA 영역별로 구분하여 좀 더 자세히 살펴보고, 그에 앞서 Lock 또는 Latch Event의 이해를 위해 필요한 Enqueue와 Latch의 개념을 간단히 알아보도록 하겠다.

출처: http://www.oracle.com/technology/global/kr/pub/columns/dbtuning04.html