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

先说结论：
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>
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)