비

오라클 lock 체크 스크립트

· 2007-06-29 (금) 17:54:54 · 4915
오라클에서 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건
+
제목 글쓴이 날짜 조회
08-06-15 조회 3,196
08-06-15 조회 2,426
08-06-14 조회 2,760
08-06-14 조회 3,665
08-06-14 조회 3,353
08-06-12 조회 2,232
08-06-11 조회 2,278
08-06-11 조회 2,369
08-06-11 조회 7,397
08-06-11 조회 7,129
08-06-08 조회 3,301
08-06-07 조회 2,235
08-06-05 조회 2,370
08-06-05 조회 3,336
08-06-05 조회 2,703
08-06-05 조회 2,360
08-06-05 조회 2,379
08-06-04 조회 2,298
08-06-03 조회 2,297
08-06-03 조회 5,372
08-06-03 조회 3,604
08-06-02 조회 2,901
08-06-02 조회 4,040
08-06-01 조회 2,655
08-05-31 조회 2,359
08-05-31 조회 2,369
08-05-31 조회 2,460
08-05-30 조회 2,325
08-05-29 조회 2,477
08-05-29 조회 3,745
08-05-27 조회 5,580
08-05-26 조회 2,421
08-05-26 조회 5,328
08-05-25 조회 5,418
08-05-24 조회 2,480
08-05-23 조회 3,492
08-05-23 조회 2,903
08-05-23 조회 4,660
08-05-22 조회 2,661
08-05-21 조회 5,006
08-05-21 조회 4,785
08-05-21 조회 3,929
08-05-20 조회 4,526
08-05-20 조회 4,115
08-05-20 조회 3,341
08-05-20 조회 4,609
08-05-20 조회 3,954
08-05-20 조회 4,842
08-05-19 조회 2,389
08-05-16 조회 3,516
08-05-15 조회 2,578
08-05-15 조회 2,406
08-05-15 조회 2,397
08-05-14 조회 2,338
08-05-13 조회 2,792
08-05-13 조회 4,195
08-05-13 조회 4,616
08-05-09 조회 4,384
08-05-08 조회 3,146
08-05-08 조회 5,292
08-05-08 조회 2,649
08-05-07 조회 2,343
08-05-07 조회 2,495
08-05-07 조회 2,305
08-05-01 조회 2,619
08-05-01 조회 2,969
08-04-29 조회 3,811
08-04-28 조회 3,634
08-04-26 조회 2,660
08-04-26 조회 2,409
08-04-26 조회 3,684
08-04-25 조회 4,101
08-04-23 조회 4,536
08-04-22 조회 4,496
08-04-22 조회 3,641
08-04-22 조회 5,204
08-04-22 조회 4,585
08-04-22 조회 6,966
08-04-22 조회 4,026
08-04-21 조회 3,174
08-04-21 조회 3,226
08-04-21 조회 3,154
08-04-21 조회 2,982
08-04-21 조회 4,086
08-04-20 조회 3,250
08-04-20 조회 2,933
08-04-20 조회 3,461
08-04-19 조회 2,369
08-04-18 조회 3,079
08-04-18 조회 4,414
08-04-17 조회 3,649
08-04-17 조회 3,092
08-04-17 조회 3,312
08-04-17 조회 2,601
08-04-17 조회 2,994
08-04-17 조회 2,478
08-04-17 조회 3,029
08-04-17 조회 3,094
08-04-17 조회 2,286
08-04-17 조회 4,575