Oracle中诊断阻塞session的方法

楼主

2012-06-15, 11:28 上午

weilm

等级: 80级
注册: 2012年4月17日
区域: 广东深圳
经验: 1,183
积分: 1,163
精华: 0
发贴: 158
排名: 28

Oracle中诊断阻塞session的方法

　由于锁的机制，当某一条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 dbo.* from v$locked_object lo ,dba_objects dbo where lo.object_id = dbo.object_id and lo.xidusn=0

　　通过查询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

　　在通过v$session可以查到session相关的信息，被阻塞的status一般为ACTIVE,还可以通过sql_address联合v$sql找到被阻塞的SQL语句。

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

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

　　这时候DBA可以联系造成阻塞的session结束事务或者根据情况用命令终止session

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

　　session 142会收到如下错误，而session139往下执行后续步骤。

　　ERROR:

　　ORA-03114: not connected to ORACLE

世界上最远的距离是知与行的距离

分享按钮

IP 地址: 已登录来自: 已登录