Check_oracle_health 之阻塞会话数

Check_oracle_health 之阻塞会话数

---

sql语句：

SELECT COUNT(*) FROM DBA_BLOCKERS;

或
select INST_ID,
       SID,
       TYPE,
       ID1,
       ID2,
       LMODE,
       REQUEST,
       CTIME,
       BLOCK,
       DECODE(BLOCK, 0, '', 'blocker') blocker,
       DECODE(request, 0, '', 'waiter') waiter
  from gv$lock
 where (ID1, ID2, TYPE) in
       (select ID1, ID2, TYPE from gv$lock where request > 0)
 order by blocker;

执行结果示例如下：
INST_ID     SID TYPE        ID1        ID2 LMODE REQUEST      CTIME BLOCK BLOCKER        WAITER
------- ------- ---- ---------- ---------- ----- ------- ---------- ----- -------------- ------------
   1      70 TX        65539    2630730     6       0       2520     1 blocker
   1      86 TX        65539    2630730     0       4       1654     0                waiter
稍等一会输入/、回车，再次执行上面的SQL，如阻塞情况不变，则确认SID =70的会话是阻塞会话


分析处理：
使用sql developer执行了更新记录操作后，既没有commit也没有rollback,网络就断开了，造成表或记录被锁住，待到超时后才会被解开，那样都会造成应用操作被阻塞。

可以以Oralce管理员权限用户登录Oracle数据，查询到被锁的对象，然后杀除指定的会话。

用下面的语句查询被锁的对象，可以带上更多约束条件，如schemaname等更精确的匹配。

SELECT a.object_id, a.session_id, b.object_name, c.sid, c.serial#
  FROM v$locked_object a, dba_objects b, v$session c
 WHERE a.object_id = b.object_id
   AND a.SESSION_ID = c.sid(+)
   --AND schemaname = 'Unmi'
   ORDER BY logon_time;

杀Seesion的SQL语句语法如下：

alter system kill session 'sid, serial#'   
 如上面查出来的一条记录的sid是53, serial#为663,就执行以下的语句

    1.alter system kill session '53,663'    

如果要一次性杀死多个会话，一个一个填写sid和serial#十分的繁琐，应该在查询被锁对象的同时拼凑出多条的杀会话语句，以分号分隔，一起复制下来，然后就可以批量的执行了。

拼凑kill语句的方式如下，下面加了一个过滤条件和一个排序，杀除真正关心的表，并且着重注意超时时间过长的会话。

SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||
       ''';',
       a.object_id,
       a.session_id,
       b.object_name,
       c.sid
  FROM v$locked_object a, dba_objects b, v$session c
 WHERE a.object_id = b.object_id
   AND a.SESSION_ID = c.sid(+)
   --AND schemaname = 'Unmi'
 ORDER BY logon_time;


Oracle中查找阻塞与被阻塞SID的方法
在Oracle中,会经常遇到阻塞与被阻塞的情况.
查找阻塞与被阻塞的方法主要有下面几种:
一.通过查找v$lock和v$locked_object
这是最常用的也是最直接的方法
SQL> select sid,block from v$lock where block=1;

       SID      BLOCK
---------- ----------
      1571          1
      1856          1
      2274          1
      1005          1


SQL> select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=1571);

 OBJECT_ID SESSION_ID
---------- ----------
     74301          8
     74301        577
     74301        720
     74301        856
     74301        863
     74301       1005
     74301       1280
     74301       1571
     74301       1856
     74301       2274
     74301       2421

 OBJECT_ID SESSION_ID
---------- ----------
     74301       4122

12 rows selected.

由上面第一条语句可以看出,SID=252的session阻塞了其他的session
由第二条语句可以得出,SID=252的session阻塞了SID=269的session

二.通过查找dba_waiters和dba_blockers


SQL>  select * from dba_blockers;

HOLDING_SESSION
---------------
           4122
            856
           1571
           2274
           1005
           1856

6 rows selected.

SQL>

比如 4122 属于阻塞会话，阻塞了被的会话


SQL>  select waiting_session,holding_session from dba_waiters;

WAITING_SESSION HOLDING_SESSION
--------------- ---------------
           4122             856
           2421             856
            856             856
            863            1005
           4122            1571
           2421            1571
            856            1571
              8            1856
            720            2274
           4122            4122
           2421            4122

WAITING_SESSION HOLDING_SESSION
--------------- ---------------
            856            4122

12 rows selected.

SQL>
4122 阻塞的会话是856，856属于holding会话



三.在Oracle 10G中可以通过v$session中的blocking_session字段查找
SQL> select sid,blocking_session from v$session where blocking_session is not null;

       SID BLOCKING_SESSION
---------- ----------------
         8             1856
       720             2274
       856             1571
       863             1005
      2421             1571
      4122             1571

6 rows selected.

SQL>
 

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

转载于:http://blog.itpub.net/29500582/viewspace-1352680/