비

오라클 lock 체크 스크립트

· 2007-06-29 (금) 17:54:54 · 4927
오라클에서 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,187건
+
제목 글쓴이 날짜 조회
09-10-03 조회 3,153
08-02-03 조회 3,465
08-02-03 조회 3,404
08-01-30 조회 4,912
08-01-29 조회 2,790
08-01-27 조회 3,924
08-01-27 조회 2,858
08-01-27 조회 3,134
08-01-26 조회 8,686
08-01-26 조회 3,242
08-01-24 조회 5,293
08-01-23 조회 3,631
08-01-21 조회 3,675
08-01-21 조회 3,417
08-01-21 조회 3,433
08-01-18 조회 3,951
08-01-18 조회 4,594
08-01-18 조회 6,746
08-01-18 조회 4,033
08-01-17 조회 4,017
08-01-17 조회 3,837
08-01-16 조회 3,098
08-01-15 조회 3,485
08-01-14 조회 6,736
08-01-12 조회 4,335
08-01-12 조회 3,903
08-01-12 조회 4,614
08-01-12 조회 3,854
08-01-12 조회 4,146
08-01-11 조회 7,179
08-01-10 조회 3,911
08-01-08 조회 3,454
08-01-08 조회 3,930
08-01-06 조회 2,719
08-01-04 조회 1.1만
08-01-02 조회 5,803
07-12-28 조회 4,735
07-12-24 조회 3,155
07-12-21 조회 4,854
07-12-17 조회 3,630
07-12-14 조회 5,004
07-12-13 조회 6,435
07-12-10 조회 5,078
07-12-10 조회 3,957
07-12-10 조회 4,356
07-12-08 조회 3,715
07-12-07 조회 4,054
07-12-07 조회 3,179
07-12-06 조회 4,924
07-12-04 조회 3,498
07-12-04 조회 3,646
07-12-03 조회 3,407
07-12-03 조회 4,443
07-12-02 조회 3,271
07-11-30 조회 4,011
07-11-30 조회 5,239
07-11-30 조회 5,822
07-11-29 조회 3,688
07-11-27 조회 4,143
07-11-27 조회 5,919
07-11-27 조회 4,023
07-11-26 조회 3,135
07-11-23 조회 4,605
07-11-23 조회 4,333
07-11-22 조회 5,228
07-11-21 조회 3,456
07-11-21 조회 3,586
07-11-21 조회 1.2만
07-11-20 조회 4,963
07-11-16 조회 4,815
07-11-13 조회 4,482
07-11-12 조회 4,204
07-11-10 조회 5,586
07-11-08 조회 6,748
07-11-08 조회 5,075
07-11-07 조회 4,540
07-11-07 조회 4,993
07-11-06 조회 3,541
07-11-06 조회 3,911
07-11-05 조회 5,235
07-11-02 조회 4,119
07-11-01 조회 5,583
07-10-31 조회 3,276
07-10-30 조회 3,738
07-10-29 조회 3,728
07-10-26 조회 5,055
07-10-25 조회 5,132
07-10-25 조회 3,922
07-10-24 조회 3,920
07-10-24 조회 4,238
07-10-24 조회 4,095
07-10-24 조회 3,362
07-10-24 조회 3,480
07-10-23 조회 6,126
07-10-23 조회 5,511
07-10-22 조회 4,246
07-10-22 조회 3,626
07-10-22 조회 4,154
07-10-22 조회 3,746
07-10-19 조회 4,806