Oracle Wait Event 모니터링 Oracle Wait Event 모니터링 순서 [1] Oracle Wait Event 모니터링 [2] Enqueue 와 Latch [3] Shared Pool 관련 Wait Event [4] buffer cache 관련 Wait

Size: px

Start display at page:

Download "Oracle Wait Event 모니터링 Oracle Wait Event 모니터링 순서 [1] Oracle Wait Event 모니터링 [2] Enqueue 와 Latch [3] Shared Pool 관련 Wait Event [4] buffer cache 관련 Wait"

시영 애
2 years ago
Views:

1 Oracle Wait Event 모니터링 Oracle Wait Event 모니터링 순서 [1] Oracle Wait Event 모니터링 [2] Enqueue 와 Latch [3] Shared Pool 관련 Wait Event [4] buffer cache 관련 Wait Event [5] redo log 관련 Wait Event [6] Top SQL 튜닝 [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] 구분이벤트명설명

2 일상적인 Wait Event db file scattered read db file sequential read Full Scan 시 OS 에 I/O 를요청해놓고대기 Index Scan 시 OS 에 I/O 를요청해놓고대기 log file sync 변경 log buffer 를 log file 에반영하는동안대기 (IO, Network) DFS lock handle OPS 환경에서노드간분산 Lock 교환에따른대기 global cache cr request OPS 환경에서노드간 Buffer Block 교환에의한대기 enqueue Type 에따라세분화 (24 개의 enqueue type (9i)) latch free Name 에따라세분화 (239 개의 latch 가존재 (9i)) buffer busy waits 동일블록에대한동시액세스에따른경합 자원경합에따른 Wait Event 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 Pmon timer Client 로부터의작업요청을대기 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, )/ ) chr(bitand(p1, )/65535) ':'

3 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 의개념을간단히알아보도록하겠다. [2] Enqueue 와 Latch 개념이해하기 DBMS 의가장주된기능중에하나는동일자원에대한동시액세스를관리하는것이며, 이를위해오라클이사용하는대표적인제어구조가 Enqueue 와 Latch 이다. Enqueue 와 Latch 는모두특정자원에대한접근을 serialize 하는것이목적이라는점에서는같은 Lock 의일종이지만관리방식이나용도에서차이가있다. Enqueue 는이름에서보듯 Queue 를통해관리된다. 대상자원에대한 Owner, Waiter, Converter Queue 를관리하면서먼저요청한순서대로 Lock 을획득하도록하는구조이며, Exclusive 모드뿐아니라다양한수준의공유를허용한다. 대표적인것이테이블데이터를 Update 할때사용되는 TM, TX enqueue 이다. 반면에, Latch 는 Enqueue 에비해훨씬단순한구조로서매우짧은시간내에획득되고해제된다. Queue 를

4 통해관리되지않으므로먼저 Request 한프로세스가먼저 latch 를획득한다는보장이없으며, 대부분의경우 Exclusive 모드로만획득된다. Latch 는주로 SGA 의특정메모리구조체에대한액세스 (library cache latch, cache buffers chains latch) 혹은메모리할당시 (shared pool latch) 사용되거나오라클의중요한코드가동시에수행되지않도록하기위한용도로 (redo writing latch) 사용된다. Latch 는 Enqueue 보다는하위 level 에서 Locking 자체의부하를최소화하며작동하는제어메커니즘이라고할수있으며, 실제로 Enqueue 역시내부적으로는 Latch (enqueues, enqueue hash chains latch ) 에의해운영된다는점을생각하면둘사이의차이를쉽게이해할수있을것이다. Enqueue Enqueue 정보는내부적으로 Enqueue Resource 배열과 Enqueue Lock 배열에저장된다. 특정자원에대한 Lock 이요청되면대상을하나의 Resource 로정의하여할당하고그 Resource 에대해관련 Lock 정보를 Owner, Waiter, Converter 가운데하나로서 Link 시키는방식으로운영되며, 이러한정보는 V$RESOURCE 와 V$LOCK 뷰를통해조회해볼수있다. V$RESOURCE 와 V$LOCK 은 1:M 관계로하나의 Resource 에대하여여러건의 Lock 레코드가 Owner (LMODE>0, REQUEST=0), Waiter (LMODE=0,REQUEST>0), Converter (LMODE>0, REQUEST>0) 중하나로서대응된다. Enqueue Wait 이발생하는것은다른세션이이미나보다먼저해당자원에대한 Lock 을잡고있으므로인해내가원하는모드로 Lock 을할당받을수없기때문이다. 자신이필요로하는 Lock 의획득에실패한세션은 Owner 가작업을완료하고자신을깨워줄때까지 ( 세마포어를포스트해줄때까지 ) Waiter 혹은 Converter Queue 에서대기하게되며, 기다려도소식이없으면 3 초간격으로 timeout 에의해일어나혹시 Deadlock 상황이아닌지점검해본후다시 Sleep 에빠져들기를반복하게된다. 튜닝관련자료를보다보면가끔 Enqueue 에대한 Wait 이많은경우에 Enqueue_resource 나 Enqueue_lock 파라미터를증가시켜주어야한다는가이드를보게되는경우가있는데이파라미터들은 Enqueue resource 와 lock 배열의크기를늘려줄뿐특정 Enqueue 자원에대한동시경합을해소시키는것과는상관이없다. Enqueue Wait 를해소하기위한구체적인방법은 Enqueue type 에따라달라지지만결국은 Enqueue 를불필요하게요청하는경우가없는지를살펴 Enqueue 에대한요청을최소화하고 Enqueue 를점유하는시간을최대한단축시키는것이다. TX Enqueue 에대한 Wait 은대상자원에대한 Lock 을소유하고있는세션과그세션이수행중인 SQL 을찾아트랜잭션이장시간지속되고있는이유가무엇인지애플리케이션측면에서조사해야하며, SQ enqueue 는 Sequence 값할당시발생하는경합이므로 cache 값을늘려줌으로써완화시킨다거나 ST Enqueue 의경합이존재할경우에는 Locally managed tablespace 를사용하거나 Initial, Next 등의 extent 크기를적당한값으로조정하여실시간공간할당을감소시켜주는등의방법들이 Enqueue Wait 에대처하는대표적인사례이다. 지난호에서소개한 Session Waiter 스크립트는 Enqueue Wait 이벤트에대해서 Enqueue type 과모드를함께표시하여주도록하고있으며, 참고로 Enqueue type 별누적 Wait 현황을확인하고자하면아래 SQL 을수행하면된다. select q.ksqsttyp type, q.ksqstget gets, q.ksqstwat waits, round(q.ksqstwat/q.ksqstget,3) waitratio from sys.x$ksqst q where q.inst_id = userenv('instance') and q.ksqstget > 0 order by waits desc / Latch 오라클운영시에하위레벨에서내부적으로처리되는다양한조작들이 latch 의관할하에수행되는데 V$LATCHNAME 을조회해보면 (9i 기준으로 ) 239 종류나되는 Latch 가존재하는것을확인할수있다. 이가운데우리가자주접하게되는 latch 는다음과같은정도이며각 Latch 의기능은관련 SGA 별 Wait 를다룰때간단하게나마소개하도록하겠다.

5 Shared pool library cache latch, shared pool latch, row cache objects Buffer Cache cache buffers chains latch, cache buffers lru latch, cache buffer handle Redo log redo allocation latch, redo copy latch, redo writing latch OPS dlm resource hash list Willing to wait 모드와 No-wait 모드 Latch 획득방식은 No-wait 과 Willing to wait 의두가지모드로구분할수있다. Willing to wait 모드는 Latch 의획득에실패하면좀더시간을끌면서해당 Latch 를잡을때까지재시도를해보는방식을말한다. 일차적으로는 CPU 를놓지않고정해진횟수만큼 Spinning 을한후재시도를해보다가그래도실패하면 CPU 를놓고 Sleep 하다가 timeout 되어재시도하는작업을반복하면서 Latch 의획득을노력하게된다. Latch 가 sleep 에들어가게되면 'latch free' wait event 대기가시작된다. sleep 의지속시간은 sleep 횟수가늘어갈수록점점길어지게되는데, 따라서 V$LATCH 의 Gets 와 Sleeps 의비율과함께 Sleep1~sleep4 항목에서몇차 Sleep 까지발생했는지여부도각 Latch Wait 의심각성을판단하는요소가운데하나가된다. No-wait 모드는 Willing to wait 과는달리더이상미련을두지않고해당 Latch 에대한획득을포기하는것이다. No-wait 모드가사용되는경우는두가지가있는데, 하나는동일한기능을하는 Latch 가여러개존재하여그중에하나만획득하면충분하여서특정 Latch 에미련을가질필요가없는경우이다. 물론, 이때에도같은기능의모든 Latch 에대한시도가실패로끝날경우에는 Willing to wait 모드로요청을할것이다. No-wait 모드가사용되는다른한가지경우는 dead lock 을피하기위해서이다. 오라클은기본적으로 latch dead lock 상황을피하기위하여모든 Latch 에 level 을부여하여정해진순서를따라서만 Latch 를획득하도록하고있는데, 필요에의해이규칙을어기고 Latch 를획득하고자할경우일단 No-wait 모드로시도를해보는것이다. 다행히 Latch 를잡으면좋은것이고비록 latch 를잡을수없더라도무한정기다림으로써 dead lock 상태에빠지는일은피할수있는것이다. No-wait 모드의 Latch 작업에서는당연히 Latch 관련 wait 이발생하지않으며, redo copy latch 를제외하고는 Willing to wait 모드로 Latch 를획득하는경우가훨씬많다. Parent latch 와 Child latch Latch 가운데에는동일기능을하는 Child latch 들의 set 으로운영되는 Latch 도있으며하나의 Latch 로만운영되는 Latch 도있다. 전자의대표적인예로는 cache buffers chains ( 버퍼캐쉬블록들을같은이름의다수의 Latch 가나누어담당 ) 가있으며, 후자의예로는 shared pool latch (shared pool 내에서메모리할당을위해획득해야하는 Latch 로시스템에하나만존재 ) 가있다. 이와같은 Latch 관련통계정보는 Parent latch 와 Child latch 의개념으로관리가되는데 Latch set 에서개별 Child latch 에대한통계정보는 V$LATCH_CHILDREN 뷰를통해조회할수있으며, 단일 Latch 혹은 Latch set 의마스터 Latch (parent) 에대한통계정보는 V$LATCH_PARENT 뷰를통해조회할수있다. 지금까지한회분량을할애하여 Enqueue 와 Latch 에대해요약해본이유는, 많은 Waiting 이 SGA 내의공유자원 (Block, Cursor 등 ) 에대한경합으로인해발생하며이러한경합은다시해당자원에대한동시액세스를제어하는 Enqueue 와 Latch 에대한경합으로흔히드러나게되므로오라클의 Wait Event 를모니터링하기위해서는 Enqueue 와 Latch 의구조와작동원리에대해이해하는것이필수적이기때문이다. [3] Shared Pool 관련 Wait

6 Share pool 과성능문제 오라클이공유메모리 (SGA) 를사용하는가장큰이유는기본적으로메모리사용을최소화하면서처리성능은최대화하기위한것이다. 한번액세스된블록을 Database buffer cache 에캐쉬함으로써비용이큰 Disk I/O 를최소화하는것처럼, 한번처리된 SQL 의실행정보를 Shared Pool 에공유함으로써파싱작업을위한 CPU, 메모리자원의사용을최소화하고 SQL 수행속도를증가시킬수있다. Shared Pool 에는 SQL 이나 PL/SQL 을수행하기위한각종정보 - SQL 구문및실행계획, PL/SQL 소스, 테이블, 뷰등의각종오브젝트와오브젝트상호간의의존관계, 권한관계등 - 가저장되어있다. 지면관계상이글에서 Shared Pool 의관리메커니즘을상세히기술할수는없지만몇가지내재적인특징으로인해 Shared Pool 은오라클의메모리영역가운데에서도가장성능문제의요소가많은곳이면서도효과적인튜닝이수월치않은영역이기도하다. 무엇보다, Shared Pool 에서가장문제가되는것은메모리의조각화 (Fragmentation) 이다. Shared Pool 에서라이브러리캐쉬오브젝트를위해할당되는메모리단위를 chunk 라고부르는데 chunk 의크기는수바이트에서수 K 바이트에이르기까지필요에의해다양하게할당된다. 새로운 chunk 의할당이필요하게되면, 프로세스는이미존재하는 chunk 로부터필요한만큼의크기만을떼어내어사용하므로시간이흐를수록점차메모리가조각화되는것을피할수없다. ( 이는, Pctincrease 가 0 가아닌테이블스페이스에서익스텐트의할당과해제가반복됨에따라공간의조각화가심해지는것을떠올리면이해가쉬울것이다. ). 어느정도정형화된패턴의애플리케이션이수행되는환경이아니라, 공유가불가능한다양한형태의 SQL( 대표적으로 Literal SQL) 이빈번히요청되는환경이라면 Shared Pool 메모리조각화에따른문제는더욱심각해진다. 또한, Shared Pool 은일반적인메모리캐쉬와는달리메모리에저장되었던정보를잠시기록해둘대응되는디스크공간이없으므로한번 flush 된라이브러리캐쉬오브젝트를 reload 하기위해서는해당정보를재생성해야만한다. 이과정에서관련오브젝트정보의검색및참조, locking, 메모리할당등의작업을위해많은비용이들기때문에결국 Shared Pool 관련튜닝의최대과제는 SQL 공유를최대화하여새로운파싱요청과메모리요청을최소화하는것이라고할수있다. 헌데, 이는애플리케이션의설계와연계되는영역으로서이미개발이완료된운영서버에서는변경작업이여의치않은것이현실이다. 앞서, Shared Pool 이 DBA 로서튜닝이수월치않은영역이라고표현한이유가운데하나가여기에있다. Shared Pool 관련오해바로잡기 Shared Pool 과관련하여판단이쉽지않은부분가운데하나가과연 shared_pool_size 를얼마나할당할것인가하는것이다. 오라클은 Shared Pool 메모리를최대한효율적으로활용하기위하여다양한기법을동원하고있는데, 이러한메모리관리메커니즘에대해정확히알지못하여 Shared Pool 크기를지나치게크게할당함으로써오히려문제를악화시키는경우도드물지않다. 이러한오해를바로잡기위해 Shared Pool 의메모리할당과정을간단하게나마살펴보도록하겠다. 새로운메모리 Chunk 가할당되는과정을살펴보면, 우선프로세스는 Free List 를검색하여자신이필요로하는크기의 Free Chunk 를찾고, 그러한 Free Chunk 가없으면원하는크기보다한단계큰 Free Chunk 를찾아서필요한크기만큼분할하여사용하게된다. 만약 Free List 에서충분한크기의 Free Chunk 를찾을수없다면, 이미사용되었으나현재는사용되고있지않는 (unpinned) Chunk 들의 LRU List 를검색하여오래된것부터 8 개씩 flush 시켜 Free Chunk 로만든후자신이필요한크기를할당하여사용하게된다. 만약이과정에서현재사용중인 (pinned) Chunk 가대부분이거나, 너무메모리조각화가많이일어나서기존 Chunk 를 Flush 시킨후인접한 Free Chunk 들을병합해보아도원하는크기의 Free Chunk 를얻어낼수없다면오라클은 ORA-4031 에러를발생시키는데, 그이전에한가지최후의비밀무기가더숨어있다. 바로 Spare Free 메모리라는것인데오라클은인스턴스기동후처음에는전체 Shared Pool 의 50% 가량은 Free List 에올려놓지않고아예숨겨두었다가앞서와같이도저히피할수없는순간이되면조금씩해제시켜사용하도록한다. 그야말로메모리의조각화를최소화하기위한오라클의눈물나는노력이라고할수있을것이다. 물론이영역까지다소모한후에 flush 를통해서도필요한 Chunk 를확보할수없는상황이되면결국 ORA-4031 에러가발생할것이다. 많은이들이 Shared Pool 의남아있는 Free memory 의크기가작으면 shared_pool_size 를증가시켜주어야한다고믿고있는데이는잘못된것이다. Shared Pool 은정보의재사용을위해운영하는것이므로 SQL 실행이끝났다고해서해당 Chunk 를 Free List 로반납하지않는다. 즉, Free Memory 가남아있는한계속소모시키는방식으로사용되므로오랜시간동안운영되어온시스템에서 Shared Pool 의 Free Memory 가매우적게남아있는

7 것은그자체로는문제가되지않으며, 오히려피크타임이지난후에도많은양의 Free Memory 가남아있다면이는 Spare Free 메모리도다소모하지않은상태로서불필요하게많은메모리가할당되어낭비되고있음을의미한다. 더구나, Shared Pool 크기가지나치게크면 Free Memory 를다사용할때까지의기간이연장되는효과는얻을수있겠지만, 시간이지날수록 Memory 의조각화가더욱심해지고 Free List 의길이가길어져 Free Chunk 의검색과할당에걸리는시간이지연되므로오히려성능이악화되는결과를초래할것이다. 또한, 메모리조각화에따른영향을줄이기위해오라클은 5000 bytes 가넘는큰사이즈의 Chunk 만을위해전체 Shared Pool 의 5% 정도를따로관리하는방법을사용하고있는데, 경험적으로보면이공간은거의사용되지않고버려지고있는경우가많다. 이는 V$SHARED_POOL_RESERVED 뷰의 USED_SPACE 값을확인해보면알수있으며, 5000 bytes 이상의 large chunk 가거의요구되지않는환경에서는오히려이크기를줄여주는것이나을것이다. Shared Pool 관련 wait Shared Pool 과관련하여흔히발생하는 Wait 은라이브러리캐쉬오브젝트에대한동시액세스와메모리할당에따른관련 Lock 또는 Latch 에대한경합이대부분이며, 구체적인이름은다음과같다. (Latch free 이벤트시괄호안의관련 latch 이름은 v$session_wait 의 p2 값과 v$latchname 의 latch# 를조인하여얻어낼수있다. 1 회 SQL 참조 ) Latch Lock latch free ( library cache ) latch free ( library cache load lock) library cache lock, library cache pin library cache load lock latch free ( row cache objects ) row cache lock latch free ( shared pool ) Library cache lock, library cache pin, library load lock 은각각특정라이브러리캐쉬오브젝트에대한검색이나변경및실행또는로드시에대상오브젝트에대해할당되며, 이러한 Locking 작업은 library cache latch 와 library cache load lock latch 의관할하에처리된다. Shared pool latch 는 Free List 나 LRU List 를검색하거나메모리를할당하는작업에사용되며, row cache lock 과 row cache objects latch 는 Data dictionary cache 오브젝트에대한동시액세스를제어하는데사용된다. Latch 의개수는시스템전체적으로하나또는제한된개수가존재하는것이고 Lock 은대상오브젝트각각대해할당되는것이므로, 엄밀하게말해서 Lock 에대한경합은직접적으로는특정라이브러리캐쉬오브젝트에대한동시액세스로인해유발되는것인반면에, Latch 에대한경합은시스템전체적으로관련오퍼레이션 ( 즉, SQL 파싱 ) 자체가지나치게많이발생하거나, 짧은시간내에처리되지못함으로인해유발되는것이라고구분해볼수있다. 그러나, 결국이모든경합은근본적으로 Shared Pool 의조각화 (Fragmentation) 에따른문제가주된원인이며다시이러한조각화는요청되는 SQL 들이공유되지못하고지속적으로새롭게파싱되고메모리가할당됨으로인해발생하는것이다. 따라서, 이러한문제를해결하는가장효과적인방법은 Literal SQL 을바인드변수를사용하도록수정하거나, SQL 작성표준을마련하고, HOLD_CURSOR/ RELEASE_CURSOR, SESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIME, CURSOR_SHARING 등의파라미터를활용하는등의방법을통해 SQL 의공유도를높여주는것이며, 또한자주사용되는 PL/SQL 에대해서는 DBMS_SHARED_POOL 패키지를사용하여메모리에서 Flush 되지않도록보존하는등의조치를취해주면도움이될것이다. SQL 의수정이어려운환경이거나시스템에요청되는 SQL 의절대량이확보된메모리공간에비해많은상황이라면주기적으로피크타임을피해 Shared Pool 을직접 Flush(alter system flush shared_pool 명령을사용한다.) 시켜주는것도권장할만한관리방법이다. 많은이들이우려하는바와는달리 Shared Pool 을직접 flush 시키는것이심각한성능상문제를야기하지는않으며특히중요한패키지나 SQL cursor, Sequence 등이 keep 되어있는경우라면

8 더욱그러하다. 가끔버그를포함한특수한상황에서특정라이브러리캐쉬오브젝트에대한 lock 이장시간해제되지못하고있는경우도있는데이때는 X$KGLLK 뷰를조회하면 library cache lock 에대한 holder/waiter 를확인하여조치할수있다. 또한, Row cache lock 에대한경합은 Locally managed tablespace 를도입하거나, DML 이빈번한테이블에대한인덱스의개수를줄여주는등의조치를통해완화될수있을것이다. 부연하자면, Shared Pool 과관련된 Wait 는특정오브젝트자원에대한경합에의해발생하기보다는애플리케이션의설계, 보다단순화시켜표현하면 Literal SQL 에의한메모리조각화에의해발생하는경우가많다. 따라서, Shared Pool 관련 Wait 가많이발생하여오라클이그로인한성능상의문제를드러낼때눈에띄는하나의주범을찾아내려는노력은별효과를거두지못하는경우가많으며, 그러한시점에 DBA 가즉각적으로취할수있는조치로는직접 Shared Pool 을 Flush 시키는정도가있을것이다. 결국, 평소에꾸준한모니터링을통해 Shared Pool 의적절한크기와관련파라미터값을찾아가는것, 그리고무엇보다애플리케이션측면에서튜닝및수정작업을진행함으로써성능문제를사전에예방하는것이최선이다. [3] Buffer Cache 관련 Wait Buffer Cache 구조 Buffer Cache 의기본적인기능은여러프로세스에의해공통으로자주액세스되는데이터베이스블록을메모리에캐쉬하여물리적인디스크 IO 를최소화함으로써더빠른액세스속도를제공하기위한것이다. 복잡한설명은생략하고, Buffer Cache 의기본구조를이해하기위한몇가지핵심용어들을간단히정리해보도록하겠다. Buffer header 모든버퍼블록들은각자의 buffer header 를통해액세스되고관리된다. 즉, 메모리에캐쉬된특정데이터블록에대한액세스는먼저해쉬알고리즘을통해 cache chain 상의 buffer header 를찾고해당 buffer header 에기록된데이터블록의메모리상주소를찾아가원하는정보를읽는방식으로이루어진다. Buffer header 에기록되는주요정보는다음과같으며 Buffer header 의내용은 V$bh 뷰를통하여조회해볼수있다. - 메모리상에서의해당버퍼블록의주소 - 해당버퍼블록 ( 실제로는버퍼헤더 ) 가포함되어있는 hash chain - LRU, LRUW, CKPTQ 와같은리스트상에서의해당버퍼블록의위치 - 해당버퍼블록에대한 User, Waiter 와상태를나타내는각종 Flag Hash Buckets/ Hash Chains Buffer Cache 의모든블록은해쉬알고리즘을통해관리된다. 곧, 데이터블록의 DBA, Class 값으로 Hash Function 을적용하여해당블록이속하는 hash buckets 을할당하며, 동일한 hash buckets 에할당되는데이터블록의버퍼헤더들은 linked list 형태로 hash chain 을이루게된다. Hash buckets/hash chains 는특정데이터블록을찾아가기위한수단을제공한다. 각각의 hash buckets 에는자신에속한 hash chain 을보호하기위한 latch(cache buffers chains) 가할당된다. LRU LRU 는두개의리스트, 즉 LRUW 와 LRU 리스트의쌍으로구성된다. LRUW(LRU Write list) 는 dirty list 와같은말이며, 수정되어디스크에반영되어야할블록들의리스트이다. LRU(Least recently used list) 는 LRUW 에올라가지않은나머지버퍼블록들이등록되어있다. Buffer cache 상의버퍼블록은반드시 LRU 나 LRUW 둘중의하나에등록되며, 두리스트에동시에포함되는경우는없다. LRU 는 Free Buffer 를찾기위한수단을제공한다. 경합을피하기위해버퍼캐쉬블록들을여러개의 LRU 쌍으로나누어관리할수있으며, 각 LRU 리스트를보호하기위해 Latch(Cache buffers lru chain) 가하나씩할당된다.

9 Buffer Cache 운영규칙 메모리상의특정버퍼블록을찾아가거나, 특정블록이메모리에캐쉬되어있는지를확인하기위해서오라클은 hash bucket/hash chain 구조를사용한다. 새로운데이터블록을디스크로부터메모리로읽어들이기위한 free buffer 를확보하기위해오라클은 LRU 리스트를사용한다. 버퍼블록은 LRU 나 LRUW 둘가운데하나에등록된다. 하나의블록에대해시간대가다른여러개의복사본이존재할수있으며, 그가운데오직 CURRENT 버퍼만이변경될수있다. 하나의버퍼블록은한번에오직하나의프로세스에의해서만변경될수있다. Buffer Cache 관련 Waits 버퍼캐쉬와관련되어흔히발생하는대표적인 Wait 이벤트는다음과같다. buffer busy waits 여러세션이동시에같은블록을읽으려고하거나여러세션이같은블록에대한변경작업이완료되기를기다리고있는경우에발생하며, 특정블록에대한경합을해소하기위한조치는블록의유형에따라달라진다. Data block 에대한경합이많은경우는 Pct free 나 Pct used 값을사용하여블록당로우수를줄이거나, 특정블록에로우입력이몰리는구조의인덱스 (right-hand-index) 일경우는 reverse key index 의사용을검토하는등의방법이있으며, segment header 의경합이많은경우는 freelist 수를늘리거나 Extent 의크기를증가시키는등의방법이있고, undo header 나 undo block 에대한경합은롤백세그먼트의개수나크기를증가시키는것이전형적인조치방법이다. v$waitstat 과 x$kcbfwait 을이용하며 Class 또는 file 별로 wait 발생상황을판단할수있다. free buffer waits/write complete waits DBWR 가 dirty buffer 를 write 하는동안서버프로세스가대기하고있는경우발생한다. 곧, 너무나많은 dirty buffer 가생겨나거나 DBWR 의쓰기속도가충분히튜닝되지못한경우에발생한다. 점검포인트는물리적디스크의속성 (stripe size, layour, cache size) 최적화, Raw device 의활용, Async IO 나 multi- DBWR(db_writer_processes) 활용여부등이다. 위와같은버퍼블록에대한경합역시비효율적인실행계획을통해수행되는애플리케이션에의하여불필요하게많은블록이메모리로올라오는것이원인일경우가많으므로경합이빈번한블록이속하는테이블 / 인덱스명을찾아낼수있다면관련 SQL 을찾아내어보다효과적인튜닝작업이이루어질수있을것이다. v$session_wait 의 p1,p2 컬럼에각각 file#, block# 값을표시하여주므로이값을이용하여아래의 SQL 문으로현재어떤오브젝트에대하여해당 wait 가발생하고있는지를추적할수있다. ( 1 회에소개한 SQL 문에서는 Additional Info 값을참조. ) select segment_name, segment_type from dba_extents where file_id = :file# and :block# between block_id and block_id + blocks -1 cache buffers chains latch SGA 내에캐쉬된데이터블록을검색할때사용된다. 버퍼캐쉬는블록들의 chain 을이루고있으므로각각의

10 chain 은이 Latch 의 child 들에의해보호된다. 이 Latch 에대한경합은특정블록에대한대량의동시액세스가발생할때유발된다. 애플리케이션을검토해보아야한다. Ø cache buffers lru chain latch 버퍼캐쉬의버퍼를 LRU 정책에따라이동시켜야할필요가있는경우프로세스는이 Latch 획득하게된다. 이 Latch 에대한경합은 Multiple buffer pool 을사용하거나 DB_BLOCK_LRU_LATCHES 를증가시켜 LRU Latch 의개수를늘려서해소할수있다. SQL 문을튜닝하면해당프로세스에의해액세스될블록의수가줄어들것이므로당연히효과를거둘수있다. 위와같이버퍼캐쉬를관리하는 Latch 에대한경합은경합이집중되는특정 Child Latch 에의해관리되는버퍼블록을찾아해당블록이속한세그먼트정보를알아낸다면보다효과적인조치가가능할것인데, latch free wait 일경우 v$session_wait 의 p1raw 값이해당 Latch address 를의미한다. 이값을 x$bh 의 hladdr 값과조인하면관련오브젝트이름을추적해볼수있다. select file#, dbarfil, dbablk, obj, o.name from x$bh bh, obj$ o where bh.hladdr = :latch_address and bh.obj = o.obj#; [5] Redo buffer 관련 Wait Redo buffer 구조 오라클리두구조의핵심은모든트랜잭션정보를 OS 파일에기록해둠으로써시스템장애가발생해도트랜잭션단위의일관성을잃지않고데이터베이스를복구할수있도록하겠다는것이다. 리두버퍼 (redo buffer) 는이처럼데이터베이스에가해진모든변경내역을파일에기록하기위해잠시사용되는메모리영역이며리두버퍼에기록된리두정보는다시리두로그파일에기록되어짐으로써향후시스템복구작업이필요할때에사용하게된다. 오라클의리두구조를이해하기위한핵심적인개념을간단히정리해보면다음과같다. 데이터베이스에대한변경내역은블록단위로저장된다. 물론변경되는모든블록의복사본을통째로저장하는것은아니고블록별로어떠한오퍼레이션을수행하는가, 그리고그러한블록별오퍼레이션을어떠한순서로수행하는가를기록한다. 이러한블록별단위액션을 change vector 라고부르며 change vector 가순차적으로모여하나의의미있는 redo record 가된다. 리두로그는시스템내의모든프로세스들에의해생성되는 redo record 를 SCN 순서대로저장해놓은것이다. 이때리두로그에기록되는내용에는테이블이나인덱스등의데이터블록뿐만아니라 UNDO 블록또는 UNDO 세그먼트헤더블록에대한변경내용을포함하는모든버퍼캐쉬블록에대한변경내역이대상이된다. 리두정보는항상실제변경작업보다먼저보관되어야어떤상황에서도복구가가능해진다. 따라서트랜잭션을수행하는 ( 데이터베이스블록에변경을가하는 ) 프로세스는우선자신의메모리영역내에서수행하고자하는작업에대한리두레코드를만들며, 이를먼저로그버퍼에기록하고난후에실제버퍼블록에도리두레코드에담긴내용을따라적용하게된다. 또한같은이유로오라클은변경된버퍼캐쉬블록을디스크에기록하기전에먼저관련된로그버퍼를로그파일에기록하는작업을처리하게된다. 따라서, 리두버퍼또는리두파일 ( 아카이브파일을포함해서 ) 에대한쓰기작업에병목이생기면시스템에대한모든작업수행이대기상태로빠지게될것이다. 트랜잭션커밋을요청한프로세스는우선해당트랜잭션에대한로그버퍼가리두로그파일에기록되는작업이완료된후에야커밋완료메세지를받을수있다. 그렇게함으로써버퍼캐쉬변경내역을모두디스크에반영하지않고도시스템의비정상종료시리두파일에저장된리두레코드로부터커밋트랜잭션을보존할수있게된다. 리두버퍼관련 Wait 이벤트 일반적으로는로그버퍼관련해서심각한 Waiting 이발생하는경우는드물지만, 가끔볼수있는리두관련 Wait 이벤트로는다음과같은것들이있다.

11 Log file parallel write LGWR 가 OS 에리두버퍼를로그파일에기록하도록요청해둔상태에서대기하고있는이벤트이다. 이경우에는 DML 작업시 nologging 옵션등을사용하여시스템에서발생하는리두레코드의절대량을줄이거나하드웨어적으로 DISK IO 를개선시켜주는것이방안이다. Log buffer space 프로세스가로그버퍼를할당하기위해대기하는이벤트인데 LGWR 가로그버퍼를비우는것보다더빠른속도로프로세스들이리두레코드를생성하고있다는것을의미한다. 로그버퍼의크기를늘려주거나, DISK IO 의속도를개선시켜주어야할것이다. 로그버퍼는로그파일에대응되는블록이맵핑이된후에사용될수있으므로로그스위치발생시에도 log buffer space 이벤트에대한대기가발생할수있다. 로그스위치가너무잦다면리두로그파일의크기를증가시켜주는것이좋다. Log file sync 프로세스가커밋이나롤백을수행할경우우선 LGWR 에게해당트랜잭션까지의로그버퍼를 Write 하도록요청하게되는데이때사용자프로세스는 LGWR 가쓰기작업을완료할때까지 log file sync 이벤트를대기하게된다. 버전 8i 이전에서는 DBWR 가쓰기작업을수행하다가아직관련로그버퍼가파일에쓰여지지않을경우에도 LGWR 에쓰기를요청하고 log file sync 이벤트에대기하였으나 8i 이상에서는 log file sync 에대기하는대신 deferred write queue 에등록한다. 따라서버전 8i 이상에서 log file sync 이벤트는사용자프로세스에의해요청되는커밋, 롤백처리시에발생하며결국, 시스템전체적으로커밋, 롤백이지나치게자주수행되거나상대적으로 LGWR 의쓰기속도가느린것이원인일것이다. 또는, 로그버퍼가너무커서 LGWR 가백그라운드로 flush 시켜주기전 ( 보통 3 초간격및 1/3 이상의로그버퍼가찬경우 ) 에커밋에의한쓰기요청이이루어지므로커밋시점에써야할양이많아대기시간이길어지는경우도있는데이경우엔리두버퍼의크기를오히려줄여주어야할것이다. 또는, LGWR wait for redo copy 이벤트가많이나타난다면 redo copy latch 가너무많아 LGWR 이사용자프로세스가버퍼쓰기작업을마칠때까지기다리는일이잦은경우를뜻하며이경우엔 _LOG_SIMULTANEOUS_COPIES 파라미터를사용하여 copy latch 의수를줄여주는조치가필요할것이다. 시스템에따라서언급한외의다양한이벤트대기와원인이존재할수있고, 더구나버전에따라 redo copy latch 와 redo allocation latch 를포함한리두운영방식상상이한부분이많이존재하여그에따른추가적인튜닝요소가있으나이글에서는지면관계상 8i 를기준으로간략히정리해보았다. [6] Top SQL 튜닝하기 ( 맺음 ) Top SQL 튜닝의필요성 지난회까지실시간 Wait Event 모니터링과이벤트별원인분석에대해서간단히살펴보았다. 일시적성능장애시재빨리원인을찾아내는것도중요하지만보다바람직한것은이러한성능문제를사전에최대한예방하는것임은두말할필요도없다. 오라클성능문제를다루는데있어강조하고싶은한가지는시스템자원의배분을변경하거나증가를고려하기전에, 불필요한작업을최소화함으로써자원요구횟수와자원점유시간을줄여주는노력이선행되어야한다는점이다. Wait Event 에대한모니터링과분석이 DBMS 의자원에대한경합과관련된성능문제를파악하는데유용한방법임에틀림없지만, 이같은정보는데이터베이스혹은그하위레벨의구조적비효율성을드러내어줄뿐애플리케이션레벨의문제를직접적으로알려주지는않는다. 간단히말하자면, DBMS 튜닝을위해정성을쏟기이전에애플리케이션튜닝에더많은투자를하라는것이다. 이런관점에서 DBA 가비교적손쉽게수행할수있는것이오라클메모리로부터악성 SQL 을추출하여튜닝하는 Top SQL 튜닝이다. Top SQL 추출기준 사용자로부터요청되어오라클내에서처리되는모든 SQL 은오라클의공유메모리영역가운데 shared pool 내에캐쉬되어지며이렇게캐쉬되어있는 SQL 과관련통계정보는 V$SQL 또는 V$SQLAREA 뷰를통해서

12 조회할수있다. 이때, Top SQL 을추출하는데중요하게사용되는항목은 buffer_gets, disk_reads, executions, rows_processed 등이며일반적으로아래와같은기준으로 Top SQL 을추출한다. Buffer gets 수치가높은 SQL Buffer gets 은해당 SQL 이처리되는과정에서액세스한메모리블록의수 (Logical IO) 를의미한다. 물론이값이높다고해서무조건악성 SQL 임을의미하는것은아니다. 즉, 이러한 SQL 들중에는실제로요구되는작업량이많아서액세스블록수가많은 SQL 도있을것이며불필요한처리를수행하느라액세스블록수가많은 SQL 도있을것이다. 어느경우이든이 SQL 들이현재오라클서버에부하를많이유발하고있는 SQL 들이라는것만은분명하며사소한비효율적요소에의해서도서버에큰영향을미칠잠재적인가능성이있는 SQL 들이므로일차적으로점검해볼필요가있다. 악성 SQL 여부를판단하기위한 Buffer gets 의수치에절대적인기준은없으며시스템의데이터규모와트랜잭션량에따라다르다. buffer gets 값을기준으로역순으로정렬한후패턴을살펴적절한추출기준을선택하는것이좋을것이다. 만일, 상위몇개의 SQL 들과나머지 SQL 들간의 buffer gets 의편차가매우크게나타난다면상위몇개의 SQL 에대해서만튜닝을수행해주어도큰효과를볼수있을것이다. 일반적으로시스템에서수행되는 SQL 가운데심각한부하를야기하는 SQL 은소수에불과한경우가많으며뒤에기술될다른조건들과조합하여최대 Top 50 건정도를추출하여효율성을검증하고튜닝을통해개선하는작업을수행하여주면충분하다. Buffer gets/execution 수치가높은 SQL SQL 의단위수행당 buffer gets 수치를의미한다. 단위수행당 buffer gets 값이높다는것은해당 SQL 의처리가비효율적일가능성이높음을의미한다. 액세스블록수가비정상적으로많다는것은 rows_processed 값과비교하여상대적으로평가되어야할부분이다. 실제로반환하는로우수가매우많은배치성 SQL 이거나혹은반환되는로우수가 1 건이라도 Group Function 이사용된 Summary 성 SQL 이라면처리과정에서많은수의블록을액세스하는것은불가피하며이자체가문제가될수는없기때문이다. Execution 수치가높은 SQL Executions 는해당 SQL 이수행된횟수를의미한다. 수행횟수가잦은 SQL 은 buffer gets 가높을경우가많다. 일반적으로십만 ~ 백만회이상빈번하게수행되는 SQL 이라면 buffer gets/executions 값이 2 자리수이내의값을나타내어야정상이며단위수행당속도는 0.1 초이내로매우빨라야한다. 따라서, 이러한 SQL 의경우 SQL 단위로보면튜닝의효과를체감하기도어렵고필요성을느끼지못할수도있으나튜닝을통해아주적은차이라도개선을가져올수있다면시스템전체적인관점에서는매우큰효과를가져다줄수있다는점이중요하다. 하루에백만번수행되는 SQL 에대하여 0.01 초를개선한다면시스템시간으로하루에일만초를절약한셈이될것이다. 이러한 SQL 에대해서는현재빠르게수행되고있다고해도더빠르게처리할여지가없는지점검하고가능한모든방안을동원하여개선시키도록노력해야한다. disk_reads 수치가높은 SQL disk_reads 는 SQL 이처리되는과정에서물리적인 IO 를통해액세스한블록의수를의미한다. 물리적 IO 의발생여부는원하는블록이메모리에캐쉬되어있는지여부에따라달라지므로수행되는횟수와수행되는시간대의데이터베이스캐쉬상황에따라유동적이라고할수있다. 그러나, buffer gets 의값과비교하여 disk_reads 의비율이높은 SQL 은 Full Scan 을수행하는 SQL 일가능성이큰데그이유는 Full Scan 을통해액세스되는블록들은기본적으로 DB buffer Cache 의 LRU 알고리즘에의해관리되지않으므로작업후에곧바로메모리로부터밀려나버릴가능성이높기때문이다. 반면에인덱스를통하여액세스하는경우, 일상적으로액세스되는테이블에대해서는인덱스의 root block 과 branch block 은항상메모리에캐쉬되어있을확률이높으므로물리적 IO 를유발하는비율이낮을수밖에없다. Top SQL 추출기준 글을맺기전에마지막으로언급하고싶은것하나는, 문제가발생했을때문제의원인이 bug 로인한것일가능성을항상염두에두어야헛된고생을덜한다는것이다. 오라클도사람이만든프로그램이므로버그가없을

13 수없으나다행히오라클의버그및패치관리는매우훌륭한편이다. 오라클메타링크를활용하여유사한문제가보고된적은없는지관련버그에대한정보는없는지살펴보아야하며, 평소에정기적으로패치및버전관리를해주는것이바람직하다. 오라클시스템을운영하다보면현실에서는다양한문제가복잡하게얽혀나타나므로명백한원인을파악하기가쉽지않을때가많지만, 운영시자신의시스템에서자주발생하는 Wait 패턴또한분명히존재하므로굵은가지들부터하나씩이해하고해결해나가다보면오라클이우리가보인애정에보답해줄날이올것이라믿는다. 지면관계상 OPS(RAC) 관련 Wait 을비롯한기타 Wait 이벤트에대해다루지못한점, 그리고각 Wait 이벤트별로좀더친절한설명과사례를제시하지못한점이아쉬움으로남지만, 누구든아주작은것하나라도이글을통해새로이얻을수있었다면그이상바랄것은없다.

Oracle Wait Interface Seminar

Oracle Wait Interface Seminar 1 대용량 DBMS 의효율적인 모니터링및성능관리방안 Copyrights 2001~2007, EXEM Co., LTD. All rights reserved. 목차 2 1. 성능문제와 OWI 분석방법론 2. OWI 구성요소 3. Latch & LOCK 4. Oracle I/O 5. Cache Buffer 3 성능문제와 OWI 분석방법론 성능지연사례 1) 평소에