oracle oracle中'enq:TM争用'等待事件的原因
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41425837/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cause of 'enq:TM contention' wait event in oracle
提问by Ram Limbu
While monitoring wait event in v$session, i observed number of sessions with “waiting” state and the event being “enq: TM contention”.
在监视 v$session 中的等待事件时，我观察到具有“等待”状态且事件为“enq：TM 争用”的会话数。
Queries like following are running from different sessions and none of tables has any foreign key constraint.
像下面这样的查询从不同的会话运行，并且没有一个表有任何外键约束。
INSERT /* APPEND */ INTO tabA SELECT /*+ PARALLEL(t,4) */<select list> FROM tabX t;
INSERT /* APPEND */ INTO tabA SELECT /*+ PARALLEL(t,4) */<select list> FROM tabY t;
INSERT /* APPEND */ INTO tabA SELECT /*+ PARALLEL(t,4) */<select list> FROM tabZ t;
etc
What is the cause of this wait event.
这个等待事件的原因是什么。
采纳答案by Aleksej
回答by XING
This happens because Several sessions in your database are taking a very long time to process some insert statements. As a result, the “active” sessions count is very high and the database is unable to accept new session connections.
发生这种情况是因为数据库中的多个会话需要很长时间来处理某些插入语句。结果，“活动”会话计数非常高，数据库无法接受新的会话连接。
Solution:
解决方案：
The enq: TM – contention event is usually due to missing foreign key constraints on a table that's part of an Oracle DML operation. Once you fix the problem by adding the foreign key constraint to the relevant table, the enq: TM – contention event will go away.
enq: TM – 争用事件通常是由于缺少表上的外键约束，该表是 Oracle DML 操作的一部分。通过向相关表添加外键约束来解决问题后，enq: TM – 争用事件将消失。
The waits on the enq: TM – contention event for the sessions that are waiting to perform insert operations are almost always due to an unindexed foreign key constraint.. This happens when a dependent or child table's foreign key constraint that references a parent table is missing an index on the associated key. Oracle acquires a table lock on a child table if it's performing modifications on the primary key column in the parent table that's referenced by the foreign key of the child table. Note that these are full table locks (TM), and not row-level locks (TX)—thus, these locks aren't restricted to a row but to the entire table. Naturally, once this table lock is acquired, Oracle will block all other sessions that seek to modify the child table's data. Once you create an index in the child table performing on the column that references the parent table, the waits due to the TM contention will go away.
enq 上的等待：TM – 等待执行插入操作的会话的争用事件几乎总是由于未索引的外键约束。当依赖或子表的引用父表的外键约束丢失时会发生这种情况关联键上的索引。如果 Oracle 正在对子表的外键引用的父表中的主键列执行修改，则它会在子表上获取表锁。请注意，这些是全表锁 (TM)，而不是行级锁 (TX)——因此，这些锁不限于一行而是整个表。自然地，一旦获得了这个表锁，Oracle 将阻止所有其他试图修改子表数据的会话。
Since in your case there is no Foreign key constraints on tables, you can check on the below points:
由于在您的情况下，表上没有外键约束，您可以检查以下几点：
1) Check if any of the related tables has any disabledforeign keys. If found please enable them. Incase you get issue while enabling then check for the blocking session as below and kill them.
1) 检查任何相关表是否有任何disabled外键。如果找到，请启用它们。如果您在启用时遇到问题，请检查如下阻止会话并杀死它们。
SQL> select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';
SID SERIAL#
---- --------
753 8910
Then kill this blocking session.
然后终止这个阻塞会话。
SQL> ALTER SYSTEM KILL SESSION '753,8910';
session killed.
Hopefully after that you will be able to enable the foreign keys,if any and then the contention issue will be solved.
希望之后您将能够启用外键（如果有），然后争用问题将得到解决。
