Oracle Enqueues Wait Events 二

enq: TM – contention 原理即解決方法

enq: TM – contention 等待事件通常是由於正在DML操作的的表上的外來鍵約束失效導致。一旦我們找到問題的根源，我們就可以為那些使用外來鍵的表新增外來鍵約束來解決該問題。

(原文：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 – contention等待事件會話有可能是由於正在執行的insert 語句的表的外來鍵沒有相關約束，即當引用父表的依賴表或子表的外來鍵約束缺少關聯鍵上的索引時，會發生這種情況，同時還會當Oracle對父表中由子表的外來鍵引用的主鍵列執行修改，它還將獲得子表的表級鎖。

(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. )

它對父表中由子表的外來鍵引用的主鍵列執行修改。注意，這些是全表鎖(TM)，而不是行級鎖(TX)——因此，這些鎖不是某一行，而是對於整個表。自然，一旦獲得了這個表鎖，Oracle將阻止所有其他試圖修改子表資料的會話。一旦在引用父表的列作為子表的外來鍵列上建立索引，那麼TM爭用而引起的等待就會消失。

(原文：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.)

如果在新增索引的時候報ORA-00054，那麼就需要我們確認當前那個會話阻塞了，和業務確認後把會話kill，再執行：

新增索引有可能的報錯：ORA-00054: resource busy and acquire with NOWAIT specified

確認當前阻塞會話資訊：（11G-19C）

select s.inst_id inst, s.sid, s.serial#, s.username, substr(s.program,1,15) program,  s.sql_id, s.event,machine,OSUSER,round(last_call_et/60,2) lmins ,s.seq#,module
 from gv$session s
where s.type <> 'BACKGROUND'
  and (s.event not like 'SQL*Net mes%' OR S.event LIKE '%dblink%' ) and s.event != 'reliable message'  
  and s.wait_class <> 'Idle'
order by inst_id, s.sql_id, s.inst_id, s.program;