enq: TM – contention等待事件


TM enqueue ,这里的TM 是指Table Manipulation,最常见的enqueue 之一, enq: TM – contention 最常见的enqueue 并发争用等待之一。



TM 锁在下列场景中被申请:

  1.  在OPS(早期的RAC)中LGWR会以ID1=0 &  ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0
  2. 当一个单表或分区 需要做不同的表/分区操作时,ORACLE需要协调这些操作,所以需要申请该队列锁。包括:
  3. 启用参考约束 referential constraints
  5. 重建IOT
  6. 创建视图或者修改ALTER视图时可能需要申请该队列锁
  7. 分析表统计信息或validate structure时
  8. 一些PDML并行DML操作
  9. 所有可能调用kkdllk()函数的操作
  10. 太多太多了。。。


减少ENQ: TM – Contention




ID1 ID2的含义

ID1 要么是0(LGWR)  要么是object_number, 即DBA_OBJECTS.OBJECT_ID,对应锁住对象的对象号。Id2 is 0 for a normal table / partition lock and 1 for a partition-wait lock.








Waits on this event typically occur because an index is missing on the column(s) containing a foreign key constraint. In this case Oracle is forced to acquire a TM lock on the child table during DELETE, INSERT and UPDATE statements. However, there are other cases where this can occur, e.g. a LOCK TABLE command is being used.


Review all foreign key constraints to ensure corresponding indexes are in place. Script displays the problem table in the Objects tab for the SQL statement. Also review the Blockers tab to see what the blocker is doing. The following script will show all unindexed columns from foreign key constraints for a specific user and it can also be customized to include only the one table :

SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = ‘R’
AND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
AND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)
ORDER BY table_name, column_position;



1. Bug 4153376 documents a change in the mode that this enqueue takes out for SELECT FOR
UPDATE statements.
2. Bug 5849679 documents an undetected deadlock case with RAC.
3. Bug 6053354 documents a possible area of contention if using indexes that reside in a read-only


扫码关注dbDao.com 微信公众号:

  1. FROM user_indexes i, user_ind_columns icWHERE i.index_name = ic.index_nameAND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)cc ==>ic

    • remove the following line AND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)

  2. Pingback: 【性能调优】Oracle AWR报告指标全解析 – ORACLE数据库数据恢复、性能优化、故障诊断来问问MACLEAN

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569