由于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.