oracle session阻塞,Oracle中诊断阻塞的session

最新推荐文章于 2023-08-03 14:53:28 发布

amy fine

最新推荐文章于 2023-08-03 14:53:28 发布

阅读量385

点赞数

文章标签： oracle session阻塞

由于锁的机制，当某一条DML或者DDL SQL语句执行被阻塞的时候，需要找出是什么原因导致这条SQL被阻塞了，下面介绍两种常用的诊断方法：

假设有这样一个表：table t(id int primary key,val int)，数据为：

id val

1 1

2 2

[oracle@10g ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 16:48:03 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t (id int primary key,val int);

Table created.

SQL> insert into t values (1,1);

1 row created.

SQL> insert into t values (2,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID VAL

---------- ----------

1 1

2 2

1.在第一个Session，这里把它叫做Session A，做了如下的update语句，没有提交或者回滚：

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

1 row updated.

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为138的是被阻塞的session：

SQL> conn / as sysdba

Connected.

SQL> select * from v$locked_object;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE

------ ------- ------ --------- ---------- --------------- ------------ ------- -----------

0 0 0 54364 138 SCOTT oracle 25455 3

7 24 322 54364 143 SCOTT oracle 25338 3

通过v$locked_object和dba_objects关联查询出被阻塞session的对象：

SQL> select dbo.*

2 from v$locked_object lo, dba_objects dbo

3 where lo.object_id = dbo.object_id

4 and lo.xidusn = 0;

OWNER OBJECT_NAM SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S

----- ---------- -------------- --------- -------------- ----------- --------- --------- ------------------- ------- - - -

SCOTT T 54364 54364 TABLE 28-AUG-13 28-AUG-13 2013-08-28:16:48:53 VALID N N N

通过查询v$lock可以看到是哪一个session阻塞了哪一个session:143阻塞了138

SQL> select blkingsess.sid blockingsid, blkedsess.sid blockedsid

2 from v$lock blkingsess,

3 (select * from v$lock where request != 0) blkedsess

4 where blkingsess.id1 = blkedsess.id1

5 and blkingsess.id2 = blkedsess.id2

6 and blkingsess.sid != blkedsess.sid;

BLOCKINGSID BLOCKEDSID

----------- ----------

143 138

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

SQL> select sid, serial#, status, sql_address

2 from v$session

3 where sid in (143, 138);

SID SERIAL# STATUS SQL_ADDRESS

--- ------- -------- ----------------

138 5914 ACTIVE 000000008358A478

143 7044 INACTIVE 000000007AA1DA68

SQL> select sql_id,sql_text from v$sql where address='000000008358A478';

SQL_ID SQL_TEXT

------------- -------------------------------

cgxjac574kd3q update t set val = 4 where id=1

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

SQL> alter system kill session '143,7044';

System altered.

其中143为sid，7044为serial#，session 143会收到如下错误，session 138可以执行后续步骤.

ERROR:

ORA-03114: not connected to ORACLE

方法二：

查看等待事件会发现存在锁表：

SQL> select sid, event

2 from v$session_wait

3 where event not like 'SQL%'

4 and event not like 'rdbms%';

SID EVENT

---------- ----------------------------------------------------------------

135 jobq slave wait

138 enq: TX - row lock contention

140 wait for unread message on broadcast channel

149 Streams AQ: waiting for time management or cleanup tasks

150 Streams AQ: qmn slave idle wait

153 Streams AQ: qmn coordinator idle wait

164 smon timer

170 pmon timer

8 rows selected.

查看此锁的持有者与等待着：

SQL> column sess format a15

SQL> SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess,

2 id1,

3 id2,

4 lmode,

5 request,

6 type

7 FROM V$LOCK

8 WHERE (id1, id2, type) IN

9 (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

10 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY

----------- ------ --- ----- ------- --

Holder: 143 458776 322 6 0 TX

Waiter: 138 458776 322 0 6 TX

查出此锁持有者session的session id，机器，程序等信息；

SQL> select s.sid,

2 s.serial#,

3 s.program,

4 s.machine,

5 s.username,

6 s.last_call_et,

7 s.logon_time,

8 sw.event

9 from v$session s, v$session_wait sw

10 where s.sid = sw.sid

11 and s.sid = 143;

SID SERIAL# PROGRAM MACHINE USERNAME LAST_CALL_ET LOGON_TIM EVENT

--- ------- ----------------------------------- --------------- ---------- ------------ --------- ------------------------------

143 7044 sqlplus@10g.localdomain (TNS V1-V3) 10g.localdomain SCOTT 3500 28-AUG-13 SQL*Net message from client