Microsoft Word - Oracle Wait 분석 테크닉.doc

Size: px

Start display at page:

Download "Microsoft Word - Oracle Wait 분석 테크닉.doc"

희원 방
5 years ago
Views:

1 Reviewed by Oracle Certified Master Korea Community ( ) Oracle Wait 분석테크닉 (by Donald K. Burleson) 오라클은현재진행중인오라클트랜잭션의 wait상태에대한정보를자세히제공하는 v$session_wait와 v$system_event라는특별한뷰를제공한다. 이글은시스템측면의경합과 wait상태를경험한개별개체를찾기위해어떻게오라클 wait이분석되어질수있는지를조사한다. 오라클을위한 wait 이벤트분석은세가지영역으로나뉘어질수있다.: 시간기반 [Time-based] 이벤트분석 : 오라클 STATSPACK 유틸리티는긴시간의구간에대해 wait 이벤트의흐름을보여줄수있고, wait의변화가때때로유용한정보를제공하기도한다. 시스템측면 [System-wide] 이벤트분석 : 우리는높은수준에서시작할것이고, 시스템측면의이벤트- 백그라운드프로세스를위한이벤트를보여주는-를추적하는데사용되는스크립트를보여준다. 세션 wait 이벤트 : 스크립트를수행한한순간의실시간 wait을말한다. 우리는 wait 이슈를야기하는특정데이터베이스객체들의위치를보여주는기법을보여줄것이다. 이제오라클 wait 이벤트에대한짧은리뷰를통해어떻게그것들이우리데이터베이스를최고상태로조정하는데도움을주는지이해하는것부터시작하자. 시스템측면 [system-wide] 의스크립트들을수행할때, 당신은데이터베이스가대부분의시간을소비하는영역이 waiting 이라는것을보게될것이다. 그러나, 높은 waits 은항상병목이나문제를내포하고있지는않다. 높은 waits 은병목을내포할수도있지만, 몇몇 waits 은데이터베이스운영의일상부분이기도하다. 일반적으로, 시스템측면 [system-wide] 의 waits 이벤트는데이터베이스가대부분의시간을소비하는곳이라고말한다. 예를들어, db file sequential read 이벤트의높은 waits 은디스크병목을암시하지만, 이 waits 이비정상인지를확인하기위해각디스크스핀들의평균디스크대기 (queue) 기간을반드시점검해보아야한다. 어떤경우, 오라클이시스템측면의 wait 이벤트를위한스크립트를수행하면 RAID-5 구성이막대한규모의디스크 enqueue 를발생시키는것을발견한다. 또한, 디스크를 RAID0+1 로재구성한후에는전체데이터베이스에걸쳐서 3 배의성능향상을경험했다고한다. 오라클시스템 waits 을모니터링하기위한선행조건오라클 waits 이벤트를수집하려면오라클파라미터중 timed_statistics=true 가필요하다. oracle9i 에서는이것이 default 이지만, Oracle9i 와이전버젼에서도 timed_statistics 의셋팅이필요하다. 우리는또한튜닝의노력에도움이되지않는 wait 이벤트를걸러내야한다. 아래목록은의미없는정보를주는모든시스템 'idle' wait 이벤트를보여준다.: dispatcher timer lock element cleanup Null event parallel query dequeue wait parallel query idle wait - Slaves pipe get PL/SQL lock timer pmon timer

2 rdbms ipc message slave wait smon timer SQL*Net break/reset to client SQL*Net message from client SQL*Net message to client SQL*Net more data to client virtual circuit status 일단적절한스크립트가준비되면, 우리는오라클 wait 이벤트에대한조사를시작할수있다. 오라클 STATSPACK 유틸리티를이용한시계열 [time-series] wait 이벤트분석을시작하자. STATSPACK 을이용한 Event Wait 분석 STATSPACK 에서, 당신은오라클 wait 이벤트의매시간 snapshot 을얻을수있고시간에따른 wait 행태의변화의도표화할수있다. 당신은또한기준치를설정해서미리정의된기준치를초과하는 wait 이벤트에대해서만리포트할수있다. 다음은 wait 이벤트의기준치설정리포팅에일반적으로사용되는스크립트다. -- prompt -- prompt -- prompt *********************************************************** -- prompt Excessive waits on background events -- prompt *********************************************************** -- prompt ttitle 'High waits on background events Rollup by hour' column mydate heading 'Yr. Mo Dy Hr' format a13; column event format a30; column total_waits heading 'tot waits' format 999,999; column time_waited heading 'time wait' format 999,999; column total_timeouts heading 'timeouts' format 9,999; break on to_char(snap_time,'yyyy-mm-dd') skip 1; select to_char(snap_time,'yyyy-mm-dd HH24') e.event, e.total_waits - nvl(b.total_waits,0) e.time_waited - nvl(b.time_waited,0) e.total_timeouts - nvl(b.total_timeouts,0) from mydate, total_waits, time_waited, total_timeouts

3 stats$bg_event_summary b, stats$bg_event_summary e, stats$snapshot sn where snap_time > sysdate-&1 e.event not like '%timer' e.event not like '%message%' e.event not like '%slave wait%' e.snap_id = sn.snap_id b.snap_id = e.snap_id-1 b.event = e.event e.total_timeouts > 100 ( e.total_waits - b.total_waits > 100 or e.time_waited - b.time_waited > 100 ) ; 이것은위스크립트의출력물이다. 보이는것처럼, 우리는시계열 [time-series] 결과를통해우리의기준치를초과하는일자와시간을볼수있다. 만약우리가이리스트를살펴본다면, 우리는매일저녁 10:00pm 과 11:00pm 사이에 redo logs 에높은 waits 를겪었음을알수있다. Wed Aug 21 page 1 High waits on background events Rollup by hour Yr. Mo Dy Hr EVENT tot waits time wait timeouts LGWR wait for redo copy 9,326 1, LGWR wait for redo copy 8, buffer busy waits , LGWR wait for redo copy LGWR wait for redo copy LGWR wait for redo copy 9,207 1,

4 buffer busy waits , LGWR wait for redo copy 9, buffer busy waits , LGWR wait for redo copy LGWR wait for redo copy 8,030 2, buffer busy waits , LGWR wait for redo copy 8, buffer busy waits , LGWR wait for redo copy 1, rdbms ipc reply , LGWR wait for redo copy v$event_wait 에대응하는쿼리들을사용 아래는가장일반적으로사용되는시스템측면의이벤트를보여주는스크립트이며, current_waits.sql 라 부른다. set pages 999 set lines 90 column c1 heading 'Event Name' format a30 column c2 heading 'Total Waits' format 999,999,999 column c3 heading 'Seconds Waiting' format 999,999 column c4 heading 'Total Timeouts' format 999,999,999 column c5 heading 'Average Wait (in secs)' format ttitle 'System-wide Wait Analysis for current wait events' select event c1, total_waits c2, time_waited / 100 c3, total_timeouts c4, average_wait /100 c5 from sys.v_$system_event where event not in ( 'dispatcher timer', 'lock element cleanup', 'Null event', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'pipe get',

5 'PL/SQL lock timer', 'pmon timer', 'rdbms ipc message', 'slave wait', 'smon timer', 'SQL*Net break/reset to client', 'SQL*Net message from client', 'SQL*Net message to client', 'SQL*Net more data to client', 'virtual circuit status', 'WMON goes to sleep' ) AND event not like 'DFS%' event not like '%done%' event not like '%Idle%' AND event not like 'KXFX%' order by c2 desc ; 아래는위스크립트의출력물이다. 우리가보는것처럼, 리포트는인스턴스가시작된이후모든누적된 waits 를볼수있다. Tue Aug 20 page 1 System-wide Wait Analysis for current wait events Average Event Total Seconds Total Wait Name Waits Waiting Timeouts (in secs) db file sequential read 2,902,850 3, latch free 2,248, ,524, PX Deq: Table Q Normal 2,080,463 4, PX Deq Credit: send blkd 1,321,019 52,251 23, direct path read 986,951 6, PX Deq Credit: need buffer 800,970 1, log file parallel write 415,175 5,

6 direct path write 321,096 9, PX Deq: Execution Msg 198,768 56,384 26, log file sequential read 118, PX Deq: Execute Reply 92,487 5,628 2, log file sync 87,295 1, db file scattered read 70, enqueue 44,335 1, PX Deq: Join ACK 42, file open 28, PX Deq: Signal ACK 26, , log file switch completion control file parallel write 23, SQL*Net more data from client 19, PX Deq: Parse Reply 17, db file parallel write 17,745 1, PX Deq: Msg Fragment 15, rdbms ipc reply 11, PX Deq: Table Q qref 7, LGWR wait for redo copy 7, control file sequential read 4, buffer busy waits 2, PX Deq: Table Q Sample 2, library cache pin 1, PX Deq: Table Q Get Keys local write wait file identify refresh controlfile comm PX qref latch log file single write imm op process startup write complete waits library cache load lock log buffer space db file single write row cache lock db file parallel read instance state change reliable message library cache lock 이것은 " 현재 " wait 이벤트의리스트를주지만, 어떤이벤트들은다른것들보다좀더중요하다. 다음은 눈여겨볼항목들이다.:

7 async disk IO control file parallel write control file sequential read db file parallel write db file scattered read db file sequential read direct path read direct path write log file parallel write log file sync 개별 wait 이벤트들에대한세부사항을위해, Oracle9i Reference Manual 의 Appendix A 를참조하거나 Metalink 를체크하도록한다. 일단우리가시스템측면의 wait 이벤트의상황을확인하면, 우리는백그라운드프로세스와직접적으로관련된 wait 이벤트로파고들수있다. set pages 999 set lines 90 column c1 heading 'System ID' format 999 column c2 heading 'Prcs' format a10 column c3 heading 'Event Name' format a30 column c4 heading 'Total Waits' format 999,999 column c5 heading 'Time Waited (in secs)' format 999,999 column c6 heading 'Avg Wait secs' format 999 column c7 heading 'Avg Wait secs' format 999 column c8 heading 'Max Wait (in secs)' format 999 ttitle 'System-wide Wait Analysis Detail Analysis for current wait events' select b.sid c1, decode(b.username,null,c.name,b.username) c2, event c3, a.total_waits c4, round((a.time_waited / 100),2) c5, a.total_timeouts c6, round((average_wait / 100),2) c7, round((a.max_wait / 100),2) c8

8 from sys.v_$session_event a, sys.v_$session b, sys.v_$bgprocess c where event NOT LIKE 'DFS%' event NOT LIKE 'KXFX%' a.sid = b.sid b.paddr = c.paddr (+) event NOT IN ( 'lock element cleanup', 'pmon timer', 'rdbms ipc message', 'smon timer', 'SQL*Net message from client', 'SQL*Net break/reset to client', 'SQL*Net message to client', 'SQL*Net more data to client', 'dispatcher timer', 'Null event', 'io done', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'pipe get', 'PL/SQL lock timer', 'slave wait', 'virtual circuit status', 'WMON goes to sleep' ) order by 4 desc ; 여기에위스크립트의출력물이있다. 여기서우리는각백그라운드프로세스와각각이오라클내부에서기다리면서보낸시간을볼수있다. Tue Aug 20 page 1 System-wide Wait Analysis Detail Analysis

9 for current wait events Time Avg Avg Max System Event Total Waited Wait Wait Wait ID Prcs Name Waits (in secs) secs secs (in secs) LGWR log file parallel write 415,082 5, ARC0 log file sequential read 108, CKPT control file parallel write 23, CKPT file open 20, DBW0 db file parallel write 17,684 1, CKPT direct path read 9, ARC1 log file sequential read 9, LGWR LGWR wait for redo copy 7, CKPT direct path write 3, SMON db file scattered read 1, CKPT control file sequential read 1, SMON db file sequential read CPA1_OWNER db file sequential read LGWR control file sequential read ARC0 control file sequential read LGWR control file parallel write DBW0 control file sequential read LGWR file open CPA1_USER log file sync ARC1 control file sequential read DBW0 file open SMON file open DBW0 direct path read LGWR direct path read DBW0 file identify LGWR direct path write ARC0 control file parallel write ARC0 file identify file open file open file open LGWR log file single write LGWR file identify CPA1_USER log file sync ARC0 file open LGWR imm op

10 3 LGWR log file sequential read file open RECO db file sequential read CPA1_OWNER latch free CPA1_USER latch free DBW0 latch free SMON latch free SYS latch free CPA1_USER latch free LGWR latch free CPA1_OWNER file open ARC1 control file parallel write CPA1_USER db file sequential read ARC1 file identify CPA1_USER log file sync ARC1 file open CKPT file identify latch free DBW0 process startup CPA1_USER db file sequential read ARC0 enqueue CPA1_USER log file sync SYS db file sequential read CKPT latch free latch free ARC0 process startup LGWR enqueue RECO file open CPA1_USER file open SYS file open CPA1_USER file open ANAND file open ANAND db file sequential read SYS log file sync SMON buffer busy waits ARC1 enqueue LGWR process startup latch free RECO latch free 위의모든리포트를통해, 우리는향상을위해다음영역을볼수있다.: 1 - Event waits for parallel query dequeues. 우리는데이터베이스객체를위해 degree of parallelism 의 기본값을점검하고시스템레벨에서는 parallelism 을 turn-off 할필요가있다.

11 2 - Event waits for "db file sequential reads." 이것은인덱스의세그먼트헤더경합에서기인할가능성이대부분이지만, 디스크경합때문일수도있다. 인덱스의 freelists 의수를늘려주는것으로부터시작할것이다. 만약 waits 이그래도잔존하면, 인덱스를여러디스크스핀들에걸쳐있도록할필요가있다. 이것들은매우일반적인 wait 상태들이지만, 때때로파라미터나객체특성변경으로고쳐질수있다. 몇가지가능한해답은다음을포함한다.: 옵티마이저의성향을인덱스를사용하도록 optimizer_index_cost_adj의값을 10 이하로조정해서불필요한 full-table scan을줄이도록한다. parallel_threads_per_cpu 의값을확인하고 parallel query 를줄이도록조정한다. o 인덱스헤더의세그먼트헤더경합 /waits 을점검한다. o 스트레스받는인덱스를위해다중세그먼트헤더를생성한다.(alter index xx storage ( freelists 1) 을이용하여 ) 부하가많은테이블과인덱스는더빠른디스크로분산하던지, 그객체를여러디스크로스트라이프한다. Session Detail Event Waits 일단우리가시스템과백그라운드 waits 에대해알아보았다면, 우리는현재 waiting 하는작업을찾을수있다. 아래스크립트는사용이어려운데왜냐하면 wait 하는바로그순간에수행되어야하기때문이다. 어떤오라클전문가는이스크립트를 60 초간격으로수행해서, 중요한 wait 이발생했을때 로보내도록한다 : wait_detail_process.sql script. Tue Aug 20 page 1 System-wide Wait Analysis Detail Analysis for current wait events Time Avg Avg Max System Event Total Waited Wait Wait Wait ID Prcs Name Waits (in secs) secs secs (in secs) LGWR log file parallel write 415,082 5, ARC0 log file sequential read 108, CKPT control file parallel write 23, CKPT file open 20, DBW0 db file parallel write 17,684 1, CKPT direct path read 9, ARC1 log file sequential read 9, LGWR LGWR wait for redo copy 7, CKPT direct path write 3, SMON db file scattered read 1, CKPT control file sequential read 1,

12 5 SMON db file sequential read CPA1_OWNER db file sequential read LGWR control file sequential read ARC0 control file sequential read LGWR control file parallel write DBW0 control file sequential read LGWR file open CPA1_USER log file sync ARC1 control file sequential read DBW0 file open SMON file open DBW0 direct path read LGWR direct path read DBW0 file identify LGWR direct path write ARC0 control file parallel write ARC0 file identify file open file open file open LGWR log file single write LGWR file identify CPA1_USER log file sync ARC0 file open LGWR imm op LGWR log file sequential read file open RECO db file sequential read CPA1_OWNER latch free CPA1_USER latch free DBW0 latch free SMON latch free SYS latch free CPA1_USER latch free LGWR latch free CPA1_OWNER file open ARC1 control file parallel write CPA1_USER db file sequential read ARC1 file identify CPA1_USER log file sync ARC1 file open CKPT file identify

13 15 latch free DBW0 process startup CPA1_USER db file sequential read ARC0 enqueue CPA1_USER log file sync SYS db file sequential read CKPT latch free latch free ARC0 process startup LGWR enqueue RECO file open CPA1_USER file open SYS file open CPA1_USER file open ANAND file open ANAND db file sequential read SYS log file sync SMON buffer busy waits ARC1 enqueue LGWR process startup latch free RECO latch free 여기에이스크립트의출력물이있다. 여기서는두가지타입의 wait 상태를보아야한다 : CPA1 사용자는 file 116, block 51253에접근하기위해기다리고있다. - 우리는 get_object_by_block_nbr.sql을이용해정확한블럭을볼수있다. IUD_READ_ONLY 사용자는 parallel query deqeue 를기다리고있다. Individual process wait times SID Name Name Wait Time state P1 text Val Text P2 P3 P3 Val Text Val CPA1_USER db file se WAITING file# 116 block# quential r ead 51,253 blocks 1 52 IUD1_READ_ONLY PX Deq Cre WAITING sleeptime/ 268,566,527 passes dit: send senderid

14 blkd 284 qref IUD1_READ_ONLY PX Deq Cre WAITING sleeptime/ 268,566,527 passes dit: send senderid blkd 284 qref IUD1_READ_ONLY PX Deq Cre WAITING sleeptime/ 268,566,527 passes dit: send senderid blkd 284 qref IUD1_READ_ONLY PX Deq Cre WAITING sleeptime/ 268,566,527 passes dit: send senderid blkd 284 qref 0 Event Wait 데이터를사용하는방법들 이것은짧은글이므로, 가장문제를일으키는 wait 이벤트, 즉 db file sequential read wait 이벤트에대한 논의로제한하도록하자. 시스템측면의리포트샘플에서보면 v$system_event 에서가장많은 wait 은 "db file sequential reads" 이다. 이 wait 이벤트는일반적으로인덱스읽기를위한것이다. 만약우리가이 wait 이벤트의자세한목록을 찾아본다면, 스크립트는오라클이그이벤트를위해기다린부분의 file number 와 block number 를리턴하는 것을볼수있다. SID Name Name Wait Time state P1 text Val Text P2 P3 P3 Val Text Val CPA1_USER db file se WAITING file# 116 block# quential r ead 51,253 blocks 1 위에서 CPA1_USER 는 file number 16, block number 을접근하기위해기다렸다는것을볼수있다. 이 주어진정보로, 우리는 dba_extents 와 dba_data_files 을통해 waits 을일으킨 data file 을볼수있다. 아래는 file number 에대한 file name 을보여주는간단한스크립트다 : accept filenbr prompt 'Enter the file#: ' select tablespace_name,

15 file_name from dba_data_files where file_id = &&filenbr ; 스크립트를실행하면 "db file sequential reads" wait 을일으키는파일의이름을빠르게볼수있다 : Enter the file#: 10 TABLESPACE_NAME FILE_NAME USERS C: ORACLE ORADATA DIOGENES USERS02.DBF wait 의원인을적절히파악하기위해우리는파일명이상을필요로한다. 우리는 get_object_by_block_nbr.sql 을이용하여대상블럭에있는모든객체를볼수있다 : set pages 999 set verify off set lines 80 col c1 heading 'Segment Name' format a40 col c2 heading 'Segment Type' format a10 col c3 heading 'First Block of Segment' format 999,999,999,999 col c4 heading 'Last Block of Segment' format 999,999,999,999 accept filenbr prompt 'Enter the file number: ' accept inblock prompt 'Enter the block number: ' select segment_name c1, segment_type c2, block_id c3 -- block_id+blocks c4 from dba_extents where &filenbr = file_id &inblock >= block_id &inblock <= block_id+blocks ;

16 여기서우리는 wait 이벤트의정확한원인이 IDX_EVENTCASE_STATUS_OVERAGE 인덱스라는것을볼수있다.. Enter the file number: 116 Enter the block number: First Segment Segment Block Name Type of Segment IDX_EVENTCASE_STATUS_OVERAGE INDEX 51,205 그렇다면, 왜세션이인덱스접근을기다리고있을까? 가장일반적인이유는 freelist 부족때문이다. 오라클 비트맵 freelists( 자동 extent 관리 ) 에앞서, 세그멘트헤더경합은만약여러작업이동시에같은인덱스를 갱신하려고경쟁한다면발생할수있다. 아래스크립트는이객체의 freelists 의수를쉽게점검할수있다 : clear columns col c1 heading 'Table Name' format a20 col c2 heading 'Table Freelists' format 99 col c3 heading 'Index Name' format a20 col c4 heading 'Index Freelists' format 99 select distinct t.table_name c1, t.freelists c2, index_name c3, i.freelists c4 from dba_tables t, dba_indexes i where t.table_name = i.table_name i.index_name = 'IDX_EVENTCASE_STATUS_OVERAGE' 물론, 여기에는인덱스를포함한디스크에높은디스크 enquque 와같은다른문제가있을수있다. 하지만, 우리는언제나먼저 freelists 를추가하려고노력해야한다. 만약 freelists 를늘리는것이 wait 이슈를해결하지못한다면, 그때인덱스는더빠르거나덜바쁜디스크로옮겨지거나여러디스크에걸쳐스트라이프되어야한다. 또한, waiting 작업에대한 SID 를알고있기때문에, SID 를이용해 v$session 과 v$sql 을조인해서 wait 상태를만드는 SQL 문을볼수있다. 기타 wait 분석기법들

17 이글은 trace 이벤트에대한언급없이완성될수없을것이다 접근에의한 wait 이벤트튜닝을지지하는사람들은 접근법을사용하는것이오라클 waits 이벤트를분석하는데최고이고가장쉬운방법이라고말할것이다. 결론오라클 wait 이벤트분석은매우복잡하고이것은가장도전적인관점의데이터베이스튜닝중한관점이다. 노력이많으면보답도크고, 특별이디스크 I/O 경합문제의경우혹은덜최적화된파라미터를가지는객체의경우의데이터베이스에서더욱그러하다.

목 차

목 차 Oracle 9i Admim 1. Oracle RDBMS 1.1 (System Global Area:SGA) 1.1.1 (Shared Pool) 1.1.2 (Database Buffer Cache) 1.1.3 (Redo Log Buffer) 1.1.4 Java Pool Large Pool 1.2 Program Global Area (PGA) 1.3 Oracle