Oracle中診斷阻塞的session

由于鎖的機(jī)制,當(dāng)某一條DML或者DDL SQL語句執(zhí)行被阻塞的時(shí)候,需要找出是什么原因?qū)е逻@條SQL被阻塞了,下面介紹一下通常的診斷方法:

假設(shè)有這樣一個(gè)表: table t(id int primary key,val int);數(shù)據(jù)為:

1,在一個(gè)Session,這里把它叫做Session A,做了如下的update語句,沒有提交或者回滾.
SQL> update t set val = 3 where id=1;

2,在一另一個(gè)Session,這里把它叫做Session B,做了如下的update語句,Session B會(huì)被阻塞.
 

SQL> update t set val = 4 where id=1;

但有活動(dòng)事務(wù)對(duì)對(duì)象加鎖的時(shí)候,會(huì)在v$locked_object視圖中有記錄如object_id,session_id等,通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN字段都為空.下圖中session_id為139的是被阻塞的session.
 

select dbo.* from  v$locked_object lo ,dba_objects dbo  where lo.object_id = dbo.object_id and lo.xidusn=0

 

通過查詢v$lock可以看到是哪一個(gè)session阻塞了哪一個(gè)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 

在通過v$session可以查到session相關(guān)的信息,被阻塞的status一般為ACTIVE,還可以通過sql_address聯(lián)合v$sql找到被阻塞的SQL語句.

select sid,serial#,status,sql_address from v$session where sid in(139,142)

select * from V$sql where address='6BE7D33C'
 

這時(shí)候DBA可以聯(lián)系造成阻塞的session結(jié)束事務(wù)或者根據(jù)情況用命令終止session

alter system kill session '142,7'; 其中142為sid,7為serial#

session 142會(huì)收到如下錯(cuò)誤,而session139往下執(zhí)行后續(xù)步驟.
 

ERROR:
ORA-03114: not connected to Oracle

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]