概述

最近运维人员在去掉了一些外键索引的时候，隔天发现数据库多了很多告警，提示enq:TM-contention，下面记录一下问题发生及解决的过程。

1、监控异常

隔天我从数据库监控系统发现XX数据库多了很多这类型等待事件，询问时才说是觉得索引很多，所以去掉了一些看起来没什么作用的索引。

2、ASH监控

@?/rdbms/admin/ashrpt.sql

输出结果如下：

从ASH报告可以看到，TM锁的争用很多。

3、查看涉及对象

查一下当时等待事件的p1,p2,p3的值

select ash.SAMPLE_TIME,
 ash.EVENT,
 ash.SESSION_ID,
 ash.BLOCKING_SESSION,
 ash.P1TEXT,
 ash.P1,
 ash.P2TEXT,
 ash.p2,
 ash.p3text,
 ash.p3,
 ash.SESSION_STATE,
 ash.SQL_OPNAME,
 ash.SQL_ID
--ash.*
 from v$active_session_history ash
 where ash.SAMPLE_TIME >
 to_date('xx 06:00:00', 'yyyymmdd HH24:MI:SS')
 and ash.SAMPLE_TIME <
 to_date('xx 07:00:00', 'yyyymmdd HH24:MI:SS')
 and ash.WAIT_CLASS <> 'Idle'
 and ash.EVENT like 'enq: TM - contention'
 order by sample_time desc;

可以看到红色的p2的值为产生TM争用的对象id，经过查证，这些object均是session正在更新的表的子表，而且通过v$sql查看update语句均更改了主表的主键，问题到这里已经很明朗了，由于外键没加索引，导致了主表在更新主表主键或删除主表记录时对子表的锁定，而且这张主表被大量的子表引用，此时子表上也同时进行事务处理，所以造成了更新主表的session 不时hang住。

--具体object：ORDER_MOVEMENT

 select * from dba_objects where object_id='176647'

4、检测未加索引的外键语句

通过对所有子表的外键加索引，消除了争用，检测未加索引的外键语句：

 SELECT TABLE_NAME,
 CONSTRAINT_NAME,
 CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
 NVL2(CNAME3, ',' || CNAME3, NULL) ||
 NVL2(CNAME4, ',' || CNAME4, NULL) ||
 NVL2(CNAME5, ',' || CNAME5, NULL) ||
 NVL2(CNAME6, ',' || CNAME6, NULL) ||
 NVL2(CNAME7, ',' || CNAME7, NULL) ||
 NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
 FROM (SELECT B.TABLE_NAME,
 B.CONSTRAINT_NAME,
 MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
 MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
 MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
 MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
 MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
 MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
 MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
 MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
 COUNT(*) COL_CNT
 FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
 SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
 SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
 POSITION
 FROM USER_CONS_COLUMNS) A,
 USER_CONSTRAINTS B
 WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 AND B.CONSTRAINT_TYPE = 'R'
 GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 WHERE COL_CNT > ALL
 (SELECT COUNT(*)
 FROM USER_IND_COLUMNS I
 WHERE I.TABLE_NAME = CONS.TABLE_NAME
 AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
 CNAME6, CNAME7, CNAME8)
 AND I.COLUMN_POSITION <= CONS.COL_CNT
 GROUP BY I.INDEX_NAME);

这是摘自TOM大师的语句，外键不加索引也是导致死锁的常见原因之一，因此对于主表经常进行更新删除操作的情况，外键一定要加索引。

5、添加相关外键的索引

当外键无索引时，更新删除主表的数据会造成子表的锁定，如果此时子表上有事务，那么进行更新删除的session变会等待，等待事件就是enq: TM - contention

外键与 TM enqueue lock 的主要问题是 在早期版本中(9i之前) 当 子表child table上 的外键没有索引时 ， 若发生 父表 parent table 上记录被delete 或 update时 ， 会在child table上加 share lock， 这会 阻塞 child table 上的DML。

但是从 9i以后的当 子表child table上 的外键没有索引时， 父表parent table上的delete 、update 只在 实际这个DML执行的过程中要求share (TM lmode=4) lock，而不会在整个事务中 都要求保持 child table上的 share lock。

结论

从这个例子我们可以看到没有建相应外键索引会导致的一系列问题，外键过多会导致索引相应增加，而索引相应增加会导致表做增删改操作需维护多张表，所以一张表不是很有必要的话是不建议建太多外键的。后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~