비

오라클 lock 체크 스크립트

· 2007-06-29 (금) 17:54:54 · 4893
오라클에서 lock체크 스크립트 입니다.
작업 시간이 오래걸리는 spid 찾아주는 쿼리문 입니다.
 

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;
[이 게시물은 관리자님에 의해 2011-10-31 16:47:36 Oracle에서 이동 됨]
댓글을 작성하시려면 로그인이 필요합니다.

프로그램

8,188건
+
제목 글쓴이 날짜 조회
07-07-31 조회 4,662
07-07-30 조회 5,104
07-07-30 조회 5,566
07-07-30 조회 5,483
07-07-29 조회 3,900
07-07-29 조회 3,631
07-07-29 조회 4,573
07-07-29 조회 4,425
07-07-28 조회 4,632
07-07-26 조회 4,534
07-07-25 조회 6,206
07-07-24 조회 6,470
07-07-21 조회 4,575
07-07-21 조회 3,925
07-07-21 조회 4,136
07-07-21 조회 3,446
07-07-21 조회 3,288
07-07-21 조회 3,235
07-07-20 조회 4,337
07-07-20 조회 3,482
07-07-20 조회 3,555
07-07-20 조회 3,244
07-07-20 조회 3,694
07-07-20 조회 4,424
07-07-18 조회 5,401
07-07-18 조회 4,602
07-07-16 조회 3,200
07-07-16 조회 3,127
07-07-13 조회 4,106
07-07-12 조회 2,822
07-07-09 조회 3,216
07-07-07 조회 3,452
07-07-07 조회 3,356
07-07-07 조회 3,485
07-07-06 조회 5,298
07-07-06 조회 3,017
07-07-06 조회 5,715
07-07-06 조회 4,268
07-07-06 조회 3,448
07-07-06 조회 4,485
07-07-06 조회 4,102
07-07-05 조회 4,100
07-07-05 조회 6,863
07-07-04 조회 3,474
07-07-02 조회 4,627
07-06-30 조회 4,509
07-06-29 조회 4,894
07-06-29 조회 4,435
07-06-28 조회 3,624
07-06-23 조회 3,274
07-06-21 조회 3,740
07-06-20 조회 7,418
07-06-20 조회 4,027
07-06-20 조회 5,763
07-06-19 조회 3,219
07-06-19 조회 1.1만
07-06-17 조회 3,675
07-06-16 조회 2,783
07-06-15 조회 4,178
07-06-12 조회 4,142
07-06-11 조회 4,026
07-06-11 조회 3,310
07-06-05 조회 3,645
07-06-05 조회 4,200
07-06-05 조회 4,214
07-06-05 조회 5,321
07-06-04 조회 7,377
07-05-31 조회 3,275
07-05-30 조회 3,364
07-05-29 조회 3,067
07-05-29 조회 2,788
07-05-29 조회 3,802
07-05-29 조회 2,887
07-05-29 조회 2,697
07-05-29 조회 5,935
07-05-29 조회 2,957
07-05-29 조회 2,987
07-05-29 조회 3,000
07-05-29 조회 3,111
07-05-29 조회 4,798
07-05-29 조회 2,796
07-05-29 조회 2,662
07-05-29 조회 2,479
07-05-29 조회 3,015
07-05-29 조회 3,056
07-05-28 조회 4,973
07-05-26 조회 3,271
07-05-20 조회 4,297
07-05-18 조회 6,045
07-05-18 조회 4,106
07-05-17 조회 9,227
07-05-17 조회 5,268
07-05-17 조회 3,931
07-05-16 조회 2,676
07-05-16 조회 3,820
07-05-15 조회 6,244
07-05-14 조회 6,422
07-05-13 조회 3,262
07-05-11 조회 5,572
07-05-10 조회 5,317