SQL> l
1* select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
SQL> /
Enter value for event: enq: TM - contention
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: 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>

这个等待表示等待TM锁的时间。 TM锁用于协调许多基表/分区操作的活动

Finding Blockers:
SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request
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;

Troubleshooting 'enq: TM - contention' waits："enq：TM - 争用"故障排除等待
The 'TM' DML (Table Manipulation) Enqueue is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. For more details see:Document 34664.1 TM Lock "DML Enqueue"
当通过INSERT，UPDATE，DELETE，MERGE，SELECT使用FOR UPDATE子句或LOCK TABLE语句修改表时，通过事务获取'TM'DML（表操作）Enqueue。 DML操作需要表锁来代表事务保留对表的DML访问，并防止与事务冲突的DDL操作。有关更多详细信息，请参阅：文档34664.1 TM Lock"DML入队"

Potential Causes:潜在原因
1. Missing Foreign Key (FK) index on the FK constraint columns in the Child tables
如果发生争用，那么最常见的原因是缺少Child表中FK约束列上的外键（FK）索引

2.Parallel DML：并行DML
If parallel DML is being used then 'TM' lock contention can also occur when parallel DML is being used while other DML is being performed on same objects. Parallel DML will acquire TM enqueues on the partitions involved (share mode) as well as the entire table (row exclusive). No other DML against affected partitions will be allowed until the PDML transaction completes. In this case sessions waiting on the TM enqueues are either attempting to perform PDML or are waiting for another session performing PDML.
如果正在使用并行DML，则在使用并行DML的同时，在同一对象上执行其他DML的同时，也会发生'TM'锁定争用。并行DML将在涉及的分区（共享模式）以及整个表（独占行）上获取TM排队。在PDML事务完成之前，不会允许其他DML针对受影响的分区。在这种情况下，等待TM排队的会话要么尝试执行PDML，要么正在等待另一个执行PDML的会话。
解决：
     安排PDML在安静的时候发生
     安排系统安静时的PDML活动，以避免影响用户。
     使用自定义的并行DML脚本来分割负载
     有时可以通过控制哪些分区将通过单个会话而不是单个PDML命令同时接收DML来避免争用。这涉及到以某种方式分割工作负载，并在多个会话中执行DML。

ITPub博客

enq: TM - contention

最新文章