oracle 阻塞

来源：互联网发布：逆波兰式 java 编辑：程序博客网时间：2024/05/16 14:53

oracle维护过程中我们可能经常会遇到，一些会话被阻塞需要我们去处理，下面就介绍一下与阻塞有关的查询：

1.查看对象被锁定的方式及被锁定时间

SQL> select t2.username,t1.object_id,t1.locked_mode,t2.sid,t2.serial#,t1.process,t2.logon_time2  from v$locked_object t1, v$session t23  where t1.session_id = t2.sid order by t2.logon_time;USERNAME         OBJECT_ID LOCKED_MODE        SID    SERIAL# PROCESS                  LOGON_TIME--------------- ---------- ----------- ---------- ---------- ------------------------ -------------------SCOTT                14822           3        229        209 31785448                 2014-04-09 09:16:19SYS                  14822           3        247        255 3342336                  2014-04-09 09:24:06SYS                  14822           3        169        175 32178516                 2014-04-09 10:17:10

2.查看当前被阻塞的会话，及其执行的sql以及申请的锁

SQL> select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid, b.REQUEST, b.TYPE, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"2  from v$session a, v$lock b, v$sqltext c3  where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value;USERNAME        MACHINE                     SID    REQUEST TY    SERIAL#    Seconds        ID1 SQL--------------- -------------------- ---------- ---------- -- ---------- ---------- ---------- ----------------------------------------------------------------SYS             egisbdb1                    169          6 TX        175       7937     196614 update scott.test set name='haha' where id=1SYS             egisbdb1                    247          6 TX        255       9205     196614 update scott.test set name='haha' where id=1

3.查看哪个会话阻塞了其他的会话

SQL> col block_msg format a50;SQL> select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid||','||d.serial# block_msg, a.block2  from v$lock a,v$lock b,v$session c,v$session d3    where a.id1=b.id14    and a.id2=b.id25    and a.block>06    and a.sid <>b.sid7    and a.sid=c.sid8    and b.sid=d.SID;BLOCK_MSG                                               BLOCK-------------------------------------------------- ----------pts/3 ('229,209') is blocking 169,175                       1pts/3 ('229,209') is blocking 247,255                       1

4.查看异常session

SQL> col username format a15;SQL> col event format a15;SQL> col PROGRAM format a15;SQL> select r.root_sid, s.serial#,2  r.blocked_num, r.avg_wait_seconds,3  s.username,s.status,s.event,s.MACHINE,4  s.PROGRAM,s.sql_id,s.prev_sql_idfrom (select root_sid, avg(seconds_in_wait) as avg_wait_seconds,count(*) - 1 as blocked_num5    6  from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait7  from v$session8  start with blocking_session is null9  connect by prior sid = blocking_session)10  group by root_sid11  having count(*) > 1) r,12  v$session s13  where r.root_sid = s.sid14  order by r.blocked_num desc, r.avg_wait_seconds desc;ROOT_SID    SERIAL# BLOCKED_NUM AVG_WAIT_SECONDS USERNAME        STATUS   EVENT           MACHINE              PROGRAM         SQL_ID        PREV_SQL_ID---------- ---------- ----------- ---------------- --------------- -------- --------------- -------------------- --------------- ------------- -------------229        209           2       5801.66667 SCOTT           ACTIVE   SQL*Net message egisbdb1             sqlplus@egisbdb 0bkus3mb1v6s7 0ck8r21198b5qto client                           1 (TNS V1-V3)

--该SQL语句即是根据v$session的字段blocking_session统计阻塞树根阻塞session的计数以及平均阻塞时间、并进行排序，排名最前的往往是异常session

0 0