비

오라클 lock 체크 스크립트

· 2007-06-29 (금) 17:54:54 · 4926
오라클에서 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건
+
제목 글쓴이 날짜 조회
07-10-19 조회 3,462
07-10-19 조회 3,222
07-10-19 조회 3,172
07-10-19 조회 2,876
07-10-19 조회 4,452
07-10-19 조회 5,818
07-10-19 조회 6,097
07-10-19 조회 3,482
07-10-19 조회 4,946
07-10-17 조회 4,596
07-10-17 조회 5,985
07-10-16 조회 4,609
07-10-15 조회 5,573
07-10-13 조회 5,245
07-10-09 조회 4,949
07-10-09 조회 4,283
07-10-09 조회 4,213
07-10-09 조회 4,771
07-10-09 조회 3,536
07-10-09 조회 3,318
07-10-09 조회 6,791
07-10-09 조회 3,942
07-10-09 조회 3,676
07-10-09 조회 3,995
07-10-09 조회 3,368
07-10-09 조회 4,407
07-10-08 조회 1.1만
07-10-05 조회 3,153
07-10-05 조회 4,079
07-10-03 조회 4,192
07-10-03 조회 3,396
07-10-03 조회 4,241
07-10-03 조회 5,262
07-10-03 조회 4,244
07-10-03 조회 3,644
07-10-03 조회 7,330
07-10-03 조회 4,910
07-10-03 조회 7,581
07-10-03 조회 3,679
07-10-03 조회 3,621
07-10-03 조회 3,627
07-10-03 조회 4,663
07-10-03 조회 4,615
07-10-03 조회 3,312
07-10-03 조회 5,174
07-10-03 조회 2,976
07-10-03 조회 2,646
07-10-03 조회 2,699
07-10-03 조회 2,906
07-10-03 조회 2,914
07-10-03 조회 4,730
07-10-02 조회 3,620
07-10-02 조회 3,150
07-10-02 조회 3,488
07-10-02 조회 5,792
07-10-02 조회 3,524
07-10-02 조회 4,150
07-10-02 조회 3,166
07-10-02 조회 4,832
07-10-02 조회 3,077
07-10-02 조회 8,909
07-10-02 조회 4,566
07-10-01 조회 2,530
07-10-01 조회 3,729
07-09-28 조회 4,756
07-09-28 조회 3,628
07-09-27 조회 3,526
07-09-27 조회 4,035
07-09-27 조회 5,078
07-09-27 조회 4,680
07-09-27 조회 3,817
07-09-27 조회 3,227
07-09-27 조회 3,280
07-09-27 조회 3,915
07-09-27 조회 3,250
07-09-15 조회 4,187
07-09-15 조회 2,905
07-09-13 조회 3,423
07-09-13 조회 2,946
07-09-13 조회 3,380
07-09-11 조회 3,615
07-09-09 조회 3,551
07-08-28 조회 3,494
07-08-28 조회 3,368
07-08-23 조회 4,671
07-08-22 조회 2,866
07-08-21 조회 4,652
07-08-21 조회 3,232
07-08-16 조회 6,505
07-08-07 조회 3,412
07-08-04 조회 3,539
07-08-04 조회 2,909
07-08-04 조회 3,320
07-08-04 조회 4,879
07-08-04 조회 2,722
07-08-04 조회 2,928
07-08-01 조회 5,565
07-08-01 조회 4,978
07-07-31 조회 4,439
07-07-31 조회 4,690