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>