由于IMPDP...APPEND引起的 enq: TM – contention

線上業務突然告警，立刻檢查ASH 報告發現 enq: TM – contention

從各個指標的TOP SQL 看都是同一個表的insert 和update 。

經確認是在impdp 數據（append ）的時候，remap_table 的時候出錯，因線上業務量大， 大量的插入和update 導致反應變慢。進程殺掉后應用恢復正常。

所以業務繁忙的系統在線表使用IMPDP 導入append 時謹慎再謹慎??！

不難發現， 資料上顯示， TM 鎖在下列場景中被申請：

1. 在 OPS( 早期的 RAC) 中 LGWR 會以 ID1=0 & ID2=0 去申請該隊列鎖來檢查 DML_LOCKS 在所有實例中是全 0 還是全非 0 。

2. 當一個單表或分區需要做不同的表 / 分區操作時， ORACLE 需要協調這些操作，所以需要申請該隊列鎖。

3. 啟用參考約束 referential constraints

4. 修改約束從 DIASABLE NOVALIDATE 到 DISABLE VALIDATE

5. 重建 IOT

6. 創建視圖或者修改 ALTER 視圖時可能需要申請該隊列鎖

7. 分析表統計信息或 validate structure 時

8. 一些 PDML 并行 DML 操作

9. 所有可能調用 kkdllk() 函數的操作

TROUBLESHOOTING STEPS

Brief Definition:

This note covers issues where the Guided Resolution Tool indicates contention on "TM" enqueues with waits for 'enq: TM - contention' .

Problem Confirmation:

The list below is a set of data that can be used to confirm that this is the correct solution based upon the symptoms observed:

The time spent actively in the local database is significant

Only certain sessions, queries or jobs are experiencing slowness (not throughout the database)

'enq: TM - contention' is a significant component of the DB time

'CPU Busy Time' is not greater than 80% of the 'CPU total time'

This confirmation data relates to information that can most easily be found in an Automatic Workload Repository (AWR) report from the period in question. For information about collecting and interpreting AWR reports see:

Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point

Document 1359094.1 How to Use AWR Reports to Diagnose Database Performance Issues

Troubleshooting 'enq: TM - contention'  waits

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"

Potential Causes:

Missing Foreign Key (FK) index on the FK constraint columns in the Child tables

If contention is occurring, then the most common reason is missing Foreign Key (FK) index on the FK constraint columns in the Child tables.    See:

Document 33453.1 Locking and Referential Integrity

Document 223303.1 Correction to 9.2.0 foreign key constraint locking behavior, per documentation Bug:2546492

Document 70120.1 Locking Behavior During Index Creation or Index Rebuild

Document 1343365.1 Increase in TM enqueue in 11g release

Document 1317447.1 Direct Path Insert - APPEND HINT and TM Enqueue LOCK Behavior

This is an application design issue and cannot be fixed without creating the missing FK indexes. The following scripts help to identify missing FK indexes.

Document 1019527.6 Script to Check for Foreign Key Locking Issues for a Specific User

Document 1039297.6 Script: To list Foreign Key Constraints

Note: A change to Foreign Key locking behavior was introduced in 11.1.0.6 onwards when a fix for an unpublished bug was implemented where DML against a child table takes an SX mode DML lock on the parent table rather than an SS Mode DML Lock. Details of the fix can be found in the following article:

Document 5909305.8 Bug 5909305 - Change to DML (TM) lock modes for foreign key constraints

Parallel 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.

To resolve this contention either:

Schedule the PDML to occur during a quiet time

Schedule the PDML activity when the system is quiet to avoid impacting users.

Use a custom parallel DML script to split the load

Sometimes its possible to avoid contention by controlling which partitions are going to concurrently receive DML through individual sessions rather than a single PDML command. This involves splitting the workload in some way and performing the DML across several sessions.

Document 1475340.1 Resolving Issues Where Lock Contention for 'enq: TM - contention' Wait Event (TM Enqueue Contention) Using Parallel DML

For more general information on locks and locking see:

Document 1392319.1 Master Note: Locks, Enqueues and Deadlocks

Also See:

Document 1476083.1 Resolving Issues Where Lock Contention for 'enq: TM - contention' Wait Event (TM Enqueue Contention) Occurs During ANALYZE INDEX VALIDATE STRUCTURE

Measuring Success

Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.