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。

enq: TM - contention

相關文章