查询堵塞进程的几种SQL--

  逛论坛看到一篇关于blocking session的帖子 归纳一下几种查询

①
with vw_lock AS (SELECT * FROM v$lock)
select
a.sid,
'is blocking',
(select 'sid:'||s.sid||' object:'||do.object_name||' rowid:'||
    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
    ||' sql_id:'||s.sql_id
   from v$session s, dba_objects do
    where s.sid=b.sid
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID
) blockee,
b.sid,b.id1,b.id2
from vw_lock a, vw_lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

②SELECT /*+ ORDERED USE_HASH(H,R) */ 
          H.SID HOLD_SID, 
          R.SID WAIT_SID, 
          decode(H.type, 
        'MR', 'Media Recovery', 
        'RT', 'Redo Thread', 
        'UN', 'User Name', 
        'TX', 'Transaction', 
        'TM', 'DML', 
        'UL', 'PL/SQL User Lock', 
        'DX', 'Distributed Xaction', 
        'CF', 'Control File', 
        'IS', 'Instance State', 
        'FS', 'File Set', 
        'IR', 'Instance Recovery', 
        'ST', 'Disk Space Transaction', 
        'TS', 'Temp Segment', 
        'IV', 'Library Cache Invalidation', 
        'LS', 'Log Start or Switch', 
        'RW', 'Row Wait', 
        'SQ', 'Sequence Number', 
        'TE', 'Extend Table', 
        'TT', 'Temp Table', 
        'TC', 'Thread Checkpoint', 
              'SS', 'Sort Segment', 
              'JQ', 'Job Queue', 
              'PI', 'Parallel operation', 
              'PS', 'Parallel operation', 
              'DL', 'Direct Index Creation', 
        H.type) type, 
      decode(H.lmode, 
        0, 'None',   1, 'Null', 
        2, 'Row-S (SS)',   3, 'Row-X (SX)', 
        4, 'Share',        5, 'S/Row-X (SSX)', 
        6, 'Exclusive',    to_char(H.lmode)) hold, 
       decode(r.request,   0, 'None', 
        1, 'Null',   2, 'Row-S (SS)', 
        3, 'Row-X (SX)',   4, 'Share', 
        5, 'S/Row-X (SSX)',6, 'Exclusive', 
        to_char(R.request)) request, 
      R.ID1,R.ID2,R.CTIME 
    FROM V$LOCK H,V$LOCK R 
    WHERE H.BLOCK = 1 AND R.REQUEST > 0 AND H.SID <> R.SID 
      and H.TYPE <> 'MR' AND R.TYPE <> 'MR' 
      AND H.ID1 = R.ID1 AND H.ID2 = R.ID2 AND H.TYPE=R.TYPE 
      AND H.LMODE > 0 AND R.REQUEST > 0 ORDER BY 1,2;


③

select /*+ rule */ l.block blk_status, l.inst_id, l.sid, /* s.serial#, */ p.spid, s.status, s.sql_hash_value hash_value, s.program, s.username, s.last_call_et, /* s.sql_id,s.sql_child_number c_n,*/ l.lmode, l.type, l.request, l.ctime   from (select inst_id,                sid,                type,                id1,                id2,                lmode,                request,                ctime,                decode(block, 0, 'blocked', 1, 'blocking', 2, 'global') block           from gv$lock          where (id1, id2, type) in                (select id1, id2, type from gv$lock where request > 0)) l,        gv$session s,        gv$process p where l.sid = s.sid    and l.inst_id = s.inst_id    and s.inst_id = p.inst_id    and s.paddr = p.addr    and s.username is not null order by 1, inst_id, status;

④

现在有v$wait_chains接口了 Basic Information: SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains; Top 100 wait chain processes: 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 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 #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters, 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3, 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, '',blocker_chain_id) blocker_chain FROM v$wait_chains wc, v$instance i WHERE wc.instance = i.instance_number (+) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 10 ) ) ORDER BY chain_id, num_waiters DESC) WHERE ROWNUM < 101; Final Blocking Session in 11.2: 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 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 fblocker_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 #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| ' from Instance '||blocker_instance BLOCKER_PROC, 'Number of waiters: '||num_waiters waiters, 'Final Blocking Process: '||decode(p.spid,null,'', p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 'Program: '||p.program image, 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3, 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw, 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null, '',blocker_chain_id) blocker_chain FROM v$wait_chains wc, gv$session s, gv$session bs, gv$instance i, gv$process p 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 = p.inst_id (+) and bs.paddr = p.addr (+)) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 10 ) ) ORDER BY chain_id, num_waiters DESC) WHERE ROWNUM < 101; Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1)

11g upgrade Infinity I/O SAN Bootcamp O'Reilly.Mastering.Perl,Intermediate.Perl mysql...... oracle on linux!!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26526320/viewspace-2121361/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26526320/viewspace-2121361/