Oracle中診斷阻塞session的方法 blocking error

it_newbalance發表於2012-07-11
OracleSessionBloCError

由於鎖的機制,當某一條DML或者DDL SQL語句執行被阻塞的時候,需要找出是什麼原因導致這條SQL被阻塞了,下面介紹一下通常的診斷方法:

 
假設有這樣一個表: table t(id int primary key,val int);資料為:
 
id   val
1    1
2     2
1,在一個Session,這裡把它叫做Session A,做了如下的update語句,沒有提交或者回滾.
SQL> update t set val = 3 where id=1;  
 
2,在一另一個Session,這裡把它叫做Session B,做了如下的update語句,Session B會被阻塞.
SQL> update t set val = 4 where id=1;
 
但有活動事務對物件加鎖的時候,會在v$locked_object檢視中有記錄如object_id,session_id等,通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN欄位都為空.下圖中session_id為139的是被阻塞的session.
 
select * from v$locked_object;

Oracle中診斷阻塞session的方法 blocking error
 
select dbo.* from  v$locked_object lo ,dba_objects dbo  where lo.object_id = dbo.object_id and lo.xidusn=0

Oracle中診斷阻塞session的方法 blocking error
 
通過查詢v$lock可以看到是哪一個session阻塞了哪一個session:142阻塞了139
 
with blkedsess as (select * from v$lock where request !=0)
select  blkingsess.sid blockingsid, blkedsess.sid blockedsid
from v$lock blkingsess,blkedsess
where blkingsess.id1 = blkedsess.id1 
and blkingsess.id2 = blkedsess.id2
and blkingsess.sid != blkedsess.sid 

Oracle中診斷阻塞session的方法 blocking error
 
在通過v$session可以查到session相關的資訊,被阻塞的status一般為ACTIVE,還可以通過sql_address聯合v$sql找到被阻塞的SQL語句.  www.2cto.com  
 
select sid,serial#,status,sql_address from v$session where sid in(139,142)

Oracle中診斷阻塞session的方法 blocking error
 
select * from V$sql where address='6BE7D33C'
 
or
select sql_text, sql_fulltext, sql_id from v$sqlarea where sql_id='6BE7D33C';
 
or
select sql_text from v$sqltext where sql_id = '6BE7D33C';
 

Oracle中診斷阻塞session的方法 blocking error
 
這時候DBA可以聯絡造成阻塞的session結束事務或者根據情況用命令終止session
 
alter system kill session '142,7'; 其中142為sid,7為serial#
 
session 142會收到如下錯誤,而session139往下執行後續步驟.
ERROR:
ORA-03114: not connected to ORACLE

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

相關文章