Shulou(Shulou.com)06/01 Report--

If the online business suddenly gives an alarm, check the ASH report immediately and find that enq: TM-contention

From the TOP SQL of each indicator, it is the insert and update of the same table.

It is confirmed that there is an error in impdp data (append) and remap_table, due to the large amount of online business, a large number of insertions and update lead to slow response. The application returns to normal after the process is killed.

So busy systems are more cautious when using IMPDP to import append in line tables!

It is not difficult to find that the data show that the TM lock is applied for in the following scenarios:

1. In OPS (early RAC), LGWR would use ID1=0 & ID2=0 to apply for the queue lock to check whether DML_LOCKS is all zero or none zero in all instances.

two。 When a single table or partition needs to do different table / partition operations, ORACLE needs to coordinate these operations, so you need to apply for the queue lock.

3. Enable reference constraint referential constraints

4. Modify constraints from DIASABLE NOVALIDATE to DISABLE VALIDATE

5. Rebuild IOT

6. You may need to apply for the queue lock when you create a view or modify an ALTER view

7. When analyzing table statistics or validate structure

8. Some PDML parallel DML operations

9. All operations that may call the kkdllk () function

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.