wait event 정보찾기

오라클이 갑자기 느려졌을때 문제의 SQL찾기 

V$SESSION_WAIT 뷰로부터 실시간 Wait Event 정보얻기

======================================================================

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;

=====================================================

Enqueue type별 누적 Wait현황을 확인

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

/

=====================================================

set timing off

col user format a8

col event format a20 

col sid_serial format a10 

col wait format 99999

col program format a8 trunc

col machine format a8

set linesize 250

col p1_val format a15 heading 'P1'

col p2_val format a15 heading 'P2'

col p3_val format a15 heading 'P3'

prompt Session Informatiln

select /*+ no_merge */ b.username "user", p.spid, a.sid||':'||b.serial# sid_serial, a.event,a.p1text||':'||a.p1 p1_val,

a.p2text||':'||a.p2 p2_val , a.p3text||':'||a.p3 p3_val,

-- a.seconds_in_wait wait,

b.program, b.sql_hash_value

from v$session_wait a, v$session b, v$process p

where a.sid > 6 and a.event not like '%Net%'

and p.ADDR=b.paddr

and a.event not like '%timer%'

and a.event not like '%ipc%'

and a.event not like '%virtual%'

and a.event not like '%slave wait%'

and a.event not like '%wakeup time manager%'

and a.sid = b.sid

order by logon_time

/