Oracle等待事件之enq: TM – contention

发布时间：2020-08-09 12:23:21 作者：水逸冰
来源：ITPUB博客阅读：180

执行DML 期间，为防止对与DML 相关的对象进行修改，执行DML 的进程必须对该表获得TM 锁。

P1 = name|mode

P2 = object #

P3 = table/partition

SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name like 'enq: TM - contention';

NAME PARAMETER1 PARAMETER2 PARAMETER3

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

enq: TM - contention name|mode object # table/partition

解决办法：可以通过下面的sql 查询blocker

SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request,l.block

FROM

( SELECT p2, p3, 'TM-'||substr(p2raw,-8)||'-'||lpad(p3,8,'0') TM

FROM v$session_wait

WHERE event='enq: TM - contention'

and state='WAITING'

) W,

gv$lock L

WHERE l.type(+)='TM'

and l.id1(+)=w.p2

and l.id2(+)=w.p3

ORDER BY tm, lmode desc, request desc

;

一般发生TM 争用的情况如下：

l DDL 动作

l Lock table tab_name in share mode;lock table tab_name in exclusive mode ；

l INSERT /*+ APPEND */ INTO

l SQL*Loader 的direct path load

l 外键约束无索引

模拟enq: TM – contention

会话1 ：

SYS@cdbtest1(CDB$ROOT)> lock table t in exclusive mode;

Table(s) Locked.

会话2 ：

SYS@cdbtest1(CDB$ROOT)> update t set object_id=100 where object_id=100;

查询等待事件

SYS@cdbtest1(CDB$ROOT)> select inst_id, event#, event,count(*) from gv$session

2 where wait_class# <> 6

3 group by inst_id, event#,event

4 order by 1,4 desc;

INST_ID EVENT# EVENT COUNT(*)

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

1 278 enq: TM - contention 1

1 414 SQL*Net message to client 1

查询blocker

SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request,l.blocklmode, l.request,l.block

FROM

2 3 ( SELECT p2, p3, 'TM-'||substr(p2raw,-8)||'-'||lpad(p3,8,'0') TM

4 FROM v$session_wait

5 WHERE event='enq: TM - contention'

6 and state='WAITING'

7 ) W,

8 gv$lock L

9 WHERE l.type(+)='TM'

10 and l.id1(+)=w.p2

11 and l.id2(+)=w.p3

12 ORDER BY tm, lmode desc, request desc

13 ;

TM OBJECT_ID INST_ID SID LMODE REQUEST BLOCK

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

TM-00013866-00000000 79974 1 113 6 0 1

TM-00013866-00000000 79974 1 91 0 3 0

阻塞者是113 ，被阻塞者是91.

Oracle等待事件之enq: TM – contention

模拟enq: TM – contention

相关阅读