查詢前100個含有根阻塞程式的Hang Chain程式指令碼

raysuen發表於2017-06-16
AI指令碼
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
select * from
(
    select
        'Current Process: '||osid W_PROC,
        'SID: '||i.INSTANCE_NAME INSTANCE,
        'INST #: '||wc.INSTANCE INST,
        'Blocking Process: '||decode(wc.BLOCKER_OSID,null,'<none>',wc.BLOCKER_OSID)||' from Instance '||wc.BLOCKER_INSTANCE BLOCKER_PROC,
        'Number of waiters: '||wc.NUM_WAITERS waiters,
        'Final Blocking Process: '||decode(ps.spid,null,'<none>',ps.spid)||' from Instance '||s.FINAL_BLOCKING_INSTANCE FBLOCKER_PROC,
        'Program: '||ps.program image,
        'Wait Event: '||wc.WAIT_EVENT_TEXT wait_event,
        'P1: '||wc.p1 p1,
        'P2: '||wc.p2 p2,
        'P3: '||wc.p3 p3,
        'Seconds in Wait: '||wc.IN_WAIT_SECS Seconds,
        'Seconds Since Last Wait: '||wc.TIME_SINCE_LAST_WAIT_SECS sincelw,
        'Wait Chain: '||CHAIN_ID||' : '||CHAIN_SIGNATURE chain_signature,
        'Blocking Wait Chain: '||decode(wc.BLOCKER_CHAIN_ID,null,'<none>',wc.BLOCKER_CHAIN_ID) blocker_chain
    from v$wait_chains wc, gv$instance i,gv$session s,gv$session bs,gv$process ps
    where     wc.INSTANCE=i.INSTANCE_NUMBER(+)
        and (wc.instance=s.inst_id(+) and wc.sid=s.sid(+) and wc.sess_serial#=s.serial#(+))
        and (s.FINAL_BLOCKING_INSTANCE=bs.inst_id(+) and s.FINAL_BLOCKING_SESSION=bs.sid(+))
        and (bs.inst_id = ps.inst_id(+) and bs.paddr = ps.addr(+))
        and (wc.num_waiters > 0 or (wc.blocker_osid is not null and in_wait_secs > 10))
    order by wc.CHAIN_ID,NUM_WAITERS desc
)
where rownum < 101;

Current Process: 117931                            SID: orcl            INST #: 1
Blocking Process: <none> from Instance             Number of waiters: 2
Final Blocking Process: <none> from Instance
Program:                                                  Wait Event: SQL*Net message from client
P1: 1650815232   P2: 1            P3: 0           Seconds in Wait: 1375
Seconds Since Last Wait:
Wait Chain: 1 : 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain: <none>

Current Process: 122878                            SID: orcl            INST #: 1
Blocking Process: 117931 from Instance 1           Number of waiters: 0
Final Blocking Process: 117931 from Instance 1
Program: oracle@bigdata111 (TNS V1-V3)                    Wait Event: enq: TX - row lock contention
P1: 1415053318   P2: 655366       P3: 908         Seconds in Wait: 695
Seconds Since Last Wait:
Wait Chain: 1 : 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain: <none>

Current Process: 118052                            SID: orcl            INST #: 1
Blocking Process: 117931 from Instance 1           Number of waiters: 0
Final Blocking Process: 117931 from Instance 1
Program: oracle@bigdata111 (TNS V1-V3)                    Wait Event: enq: TX - row lock contention
P1: 1415053318   P2: 655366       P3: 908         Seconds in Wait: 1357
Seconds Since Last Wait:
Wait Chain: 2 : 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain: 1

程式122878和118052被117931程式阻塞，程式122878和118052都在等待enq: TX - row lock contention等待事件。
等待鏈中的根程式為117931，殺掉該程式將解除Hang等待鏈。

wait_chain_finalblocking.sh
#!/bin/bash
# by ray
# 2017-06-16
#v0.1


. ~/.bash_profile

sqlplus -s /nolog <<-RAY
conn  / as sysdba
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
select * from
(
    select
        'Current Process: '||osid W_PROC,
        'SID: '||i.INSTANCE_NAME INSTANCE,
        'INST #: '||wc.INSTANCE INST,
        'Blocking Process: '||decode(wc.BLOCKER_OSID,null,'<none>',wc.BLOCKER_OSID)||' from Instance '||wc.BLOCKER_INSTANCE BLOCKER_PROC,
        'Number of waiters: '||wc.NUM_WAITERS waiters,
        'Final Blocking Process: '||decode(ps.spid,null,'<none>',ps.spid)||' from Instance '||s.FINAL_BLOCKING_INSTANCE FBLOCKER_PROC,
        'Program: '||ps.program image,
        'Wait Event: '||wc.WAIT_EVENT_TEXT wait_event,
        'P1: '||wc.p1 p1,
        'P2: '||wc.p2 p2,
        'P3: '||wc.p3 p3,
        'Seconds in Wait: '||wc.IN_WAIT_SECS Seconds,
        'Seconds Since Last Wait: '||wc.TIME_SINCE_LAST_WAIT_SECS sincelw,
        'Wait Chain: '||CHAIN_ID||' : '||CHAIN_SIGNATURE chain_signature,
        'Blocking Wait Chain: '||decode(wc.BLOCKER_CHAIN_ID,null,'<none>',wc.BLOCKER_CHAIN_ID) blocker_chain
    from v\$wait_chains wc, gv\$instance i,gv\$session s,gv\$session bs,gv\$process ps
    where     wc.INSTANCE=i.INSTANCE_NUMBER(+)
        and (wc.instance=s.inst_id(+) and wc.sid=s.sid(+) and wc.sess_serial#=s.serial#(+))
        and (s.FINAL_BLOCKING_INSTANCE=bs.inst_id(+) and s.FINAL_BLOCKING_SESSION=bs.sid(+))
        and (bs.inst_id = ps.inst_id(+) and bs.paddr = ps.addr(+))
        and (wc.num_waiters > 0 or (wc.blocker_osid is not null and in_wait_secs > 10))
    order by wc.CHAIN_ID,NUM_WAITERS desc
)
where rownum < 101;
exit;
RAY




注：內容摘錄自：羅敏 <<感悟oracle核心技術>>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2140829/，如需轉載，請註明出處，否則將追究法律責任。

相關文章