先说结论：

enq: TM - contention 这个等待事件出现一般都有下面几种情况：

1 子表没有索引，这个最常见。

2 并行 DML

3 如果发生在 RAC 集群里，gc问题也可能造成相关等待事件

4 利用lock table 主动获取TM锁

## 第一部分：子表没有索引测试

创建测试表：

CREATE TABLE T2001

( supplier_id number(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

);

INSERT INTO T2001 VALUES (1, 'Supplier 1', 'Contact 1');

INSERT INTO T2001 VALUES (2, 'Supplier 2', 'Contact 2');

INSERT INTO T2001 VALUES (3, 'Supplier 3', 'Contact 3');

INSERT INTO T2001 VALUES (4, 'Supplier 4', 'Contact 4');

INSERT INTO T2001 VALUES (5, 'Supplier 5', 'Contact 5');

COMMIT;

CREATE TABLE T4001

( product_id number(10) not null,

product_name varchar2(50) not null,

supplier_id number(10) not null,

CONSTRAINT fk_supplier

FOREIGN KEY (supplier_id)

REFERENCES T2001(supplier_id)

ON DELETE CASCADE

);

INSERT INTO T4001 VALUES (1, 'Product 1', 1);

INSERT INTO T4001 VALUES (2, 'Product 2', 1);

INSERT INTO T4001 VALUES (3, 'Product 3', 2);

INSERT INTO T4001 VALUES (4, 'Product 4', 4);

COMMIT;

测试1

session 1: DELETE T2001 WHERE supplier_id = 1; --不提交

session 2: DELETE T2001 WHERE supplier_id = 2; -- (现象HANG住)

session 3: DELETE T4001 WHERE product_id = 2; --(现象HANG住)

杀掉会话1 后先执行的是会话3,2等待会话3执行完后再执行。

查询锁和阻塞情况：

SQL>

select INST_ID,

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

CTIME,

BLOCK,

DECODE(BLOCK, 0, '', 'blocker') blocker,

DECODE(request, 0, '', 'waiter') waiter

from gv$lock

where (ID1, ID2, TYPESQL> 2 3 4 5 6 7 8 9 10 11 12 13 ) in

(select ID1, ID2, TYPE from gv$lock where request > 0)

order by blocker; 14 15

INST_ID SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK BLOCKER WAITER

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

1 11 TM 10316098 0 3 0 108 1 blocker

1 382 TM 10316098 0 0 3 82 0 waiter

1 136 TM 10316098 0 0 5 89 0 waiter

SQL> col spid format a10

col PROCESS format a10

col machine format a25

col PROGRAM format a25

col type format a10

SELECT p.spid,s.sid,s.MACHINE,s.PROCESS,s.PROGRAM,s.type,s.BLOCKING_SESSION

from gv$process p, gv$session s

where p.ADDR = s.PADDR

AND s.sid in

(select c.sid

from (select distinct a.blocking_instance, a.BLOCKING_SESSION

from gv$session a

where blocking_session is not null) b

join gv$session c

on b.blocking_instance = c.SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 INST_ID

and b.blocking_session = c.SID and c.BLOCKING_SESSION is null and c.BLOCKING_SESSION_STATUS != 'VALID'

);

11 12

SPID SID MACHINE PROCESS PROGRAM TYPE BLOCKING_SESSION

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

27267 11 zc 27266 sqlplus@zc (TNS V1-V3) USER

测试2：

session 1: DELETE T4001 WHERE product_id = 2; --不提交

session 2: DELETE T2001 WHERE supplier_id = 2; (现象HANG住)

阻塞情况：

select INST_ID,

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

CTIME,

BLOCK,

DECODE(BLOCK, 0, '', 'blocker') blocker,

DECODE(request, 0, '', 'waiter') waiter

from gv$lock

where (ID1, ID2, TYPE) in

(select ID1, ID2, TYPE from gv$lock where request > 0)

order by blocker;

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15

INST_ID SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK BLOCKER WAITER

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

1 11 TM 10316098 0 3 0 19 1 blocker

1 136 TM 10316098 0 0 5 12 0 waiter

SQL>

col spid format a10

col PROCESS format a10

col machine format a25

col PROGRAM format a25

col type format a10

SELECT p.spid,s.sid,s.MACHINE,s.PROCESS,s.PROGRAM,s.type,s.BLOCKING_SESSION

from gv$process p, gv$session s

where p.ADDR = s.PADDR

AND s.sSQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 id in

(select c.sid

from (select distinct a.blocking_instance, a.BLOCKING_SESSION

from gv$session a

where blocking_session is not null) b

join gv$session c

on b.blocking_instance = c 5 6 7 8 9 10 .INST_ID

and b.blocking_session = c.SID and c.BLOCKING_SESSION is null and c.BLOCKING_SESSION_STATUS != 'VALID'

); 11 12

SPID SID MACHINE PROCESS PROGRAM TYPE BLOCKING_SESSION

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

27267 11 zc 27266 sqlplus@zc (TNS V1-V3) USER

测试3

create index T40011 on T4001(supplier_id);

session 1: DELETE T4001 WHERE product_id = 2; --不提交

session 2: DELETE T2001 WHERE supplier_id = 2; 正常删除

## 第二部分并行insert

开启多个session执行下面的sql

SQL> insert /*+append no logging*/ into test select * from test ;

1162896 rows created.

查询阻塞情况：

SQL> select INST_ID,

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

CTIME,

BLOCK,

DECODE(BLOCK, 0, '', 'blocker') blocker,

DECODE(request, 0, '', 'waiter') waiter

from gv$lock

where (ID1, ID2, TYPE) 2 3 4 5 6 7 8 9 10 11 12 13 in

(select ID1, ID2, TYPE from gv$lock where request > 0)

order by blocker;

14 15

INST_ID SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK BLOCKER WAITER

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

1 11 TM 73885 0 6 0 33 1 blocker

1 382 TM 73885 0 0 6 18 0 waiter

1 136 TM 73885 0 0 6 19 0 waiter

SQL>

SQL> select sid,event from v$session where sid in (382,136);

SID EVENT

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

136 enq: TM - contention

382 enq: TM - contention

## 第三部分：利用lock table 主动获取TM锁或者异常的 DDL

利用lock table 主动获取TM锁

利用lock table...语句有意获取TM锁时，可能发生TM锁争用。

session 1: update test set object_id=1 where owner='ZC';

session 2: lock table test in exclusive mode;

如上所示，会话1 上因update以sub-exclusive模式获得TM锁的状态下，会话2利用lock table命令试图以exclusive模式获得TM锁，如果发生争用，则等待enq:TM-contention事件。

SQL> select INST_ID,

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

CTIME,

BLOCK,

DECODE(BLOCK, 0, '', 'blocker') blocker,

DECODE(request, 0, '', 'waiter') waiter

from gv$lock

where (ID1, ID2, TYPE) 2 3 4 5 6 7 8 9 10 11 12 13 in

(select ID1, ID2, TYPE from gv$lock where request > 0)

order by blocker;

14 15

INST_ID SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK BLOCKER WAITER

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

1 11 TM 73885 0 3 0 93 1 blocker

1 136 TM 73885 0 0 6 86 0 waiter

SQL> select sid,event from v$session where sid=136;

SID EVENT

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

136 enq: TM - contention

## 第三部分：解决方案

如果是第一种情况，根据下面的sql进行查询，查询出哪些外键上面没有索引，建立相应的索引

如果子表有多个父表，我们需要列出子表的所有父表的相关信息：

select

co.owner c_owner,

co.table_name c_table_name,

cc.column_name c_column_name,

co.constraint_name cons_constraint_name,

co.constraint_type,

cpc.table_name p_table,

cpc.column_name p_column,

co.r_constraint_name p_pk,

cc.position,

co.status,

co.validated

from

dba_constraints co,

dba_cons_columns cc,

DBA_CONS_COLUMNS cpc

where

co.owner = cc.owner

and co.table_name = cc.table_name

and co.constraint_name = cc.constraint_name

and co.constraint_type='R'

and co.r_constraint_name=cpc.constraint_name

and co.r_owner=cpc.owner

and co.TABLE_NAME = 'T4001';

列出某个用户下所有缺少索引的子表

SELECT *

FROM (SELECT c.table_name, cc.column_name, cc.position column_position

FROM dba_constraints c, dba_cons_columns cc

WHERE c.constraint_name = cc.constraint_name

and c.owner = cc.owner

AND c.constraint_type = 'R'

AND c.owner = upper('ZC')

and cc.owner = upper('ZC')

MINUS

SELECT i.table_name, ic.column_name, ic.column_position

FROM dba_indexes i, dba_ind_columns ic

WHERE i.index_name = ic.index_name

AND I.owner = upper('ZC')

and IC.table_owner = upper('ZC'))

ORDER BY table_name, column_position;

列出所有的缺失外键索引的表

select

acc.OWNER "Owner",

acc.CONSTRAINT_NAME "Constraint",

acc.table_name "Table",

acc.COLUMN_NAME "Column",

acc.POSITION "Position"

from

dba_cons_columns acc, dba_constraints ac

where

ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME and ac.CONSTRAINT_TYPE = 'R'

and acc.OWNER not in ( 'ANONYMOUS', 'AURORA$', 'AURORA', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'DVF', 'DVSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORAWSM', 'ORDPLUGINS', 'ORDSYS', 'OSE', 'OUTLN', 'PERFSTAT', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'REPADMIN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TRACESVR', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST', 'WKUSER', 'WMSYS', 'XDB','APEX_030200','GSMADMIN_INTERNAL','ORDDATA' )

and acc.OWNER = ac.OWNER

and not exists ( select 'TRUE' from dba_ind_columns b

where b.TABLE_OWNER = acc.OWNER

and b.TABLE_NAME = acc.TABLE_NAME

and b.COLUMN_NAME = acc.COLUMN_NAME

and b.COLUMN_POSITION = acc.POSITION)

union all

select ' ',null,null,null,null from dual

order by 1 ;

第二种情况：

建议避免并行的insert 类型的dml

第三种情况：

建议关闭DRM或者分析私网是否存在问题

第四种情况：

避免相关操作

参考：

WAITEVENT: "enq: TM - contention" Reference Note (Doc ID 1980175.1)

Resolving Issues Where 'enq: TM - contention' Waits are Occurring (Doc ID 1905174.1)

enq: TX - contention when _lm_drm_disable is set to 2(Doc ID 2818792.1)

Script to Check for Foreign Key Locking Issues for a Specific User (Doc ID 1019527.6)

Script to check for Missing Indexes for Foreign Keys (Doc ID 16428.1)

ITPub博客

oracle 等待事件enq: TM – contention 处理

最新文章