线上业务突然告警，立刻检查ASH 报告发现 enq: TM – contention
从各个指标的TOP SQL 看都是同一个表的insert 和update 。
经确认是在impdp 数据（append ）的时候，remap_table 的时候出错，因线上业务量大， 大量的插入和update 导致反应变慢。进程杀掉后应用恢复正常。
所以业务繁忙的系统在线表使用IMPDP 导入append 时谨慎再谨慎！！
不难发现， 资料上显示， TM 锁在下列场景中被申请：
2. 当一个单表或分区需要做不同的表 / 分区操作时， ORACLE 需要协调这些操作，所以需要申请该队列锁。
This note covers issues where the Guided Resolution Tool indicates contention on "TM" enqueues with waits for 'enq: TM - contention' .
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"
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 18.104.22.168 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
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
Document 1476083.1 Resolving Issues Where Lock Contention for 'enq: TM - contention' Wait Event (TM Enqueue Contention) Occurs During ANALYZE INDEX VALIDATE STRUCTURE
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/69976387/viewspace-2709479/，如需转载，请注明出处，否则将追究法律责任。