Cause of 'enq:TM contention' wait event in oracle

Question

While monitoring wait event in v$session, i observed number of sessions with “waiting” state and the event being “enq: TM contention”.

Queries like following are running from different sessions and none of tables has any foreign key constraint.

INSERT /* APPEND */ INTO tabA SELECT /*+ PARALLEL(t,4) */<select list> FROM tabX t;   
INSERT /* APPEND */ INTO tabA SELECT /*+ PARALLEL(t,4) */<select list> FROM tabY t;   
INSERT /* APPEND */ INTO tabA SELECT /*+ PARALLEL(t,4) */<select list> FROM tabZ t; 
etc

What is the cause of this wait event.

Answer 1

This happens because Several sessions in your database are taking a very long time to process some insert statements. As a result, the “active” sessions count is very high and the database is unable to accept new session connections.

Solution:

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.

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

Since in your case there is no Foreign key constraints on tables, you can check on the below points:

1) Check if any of the related tables has any disabled foreign keys. If found please enable them. Incase you get issue while enabling then check for the blocking session as below and kill them.

SQL> select a.sid, a.serial#
    from v$session a, v$locked_object b, dba_objects c 
    where b.object_id = c.object_id 
    and a.sid = b.session_id
    and OBJECT_NAME='EMP';

   SID    SERIAL#
   ----   --------
   753     8910

Then kill this blocking session.

SQL> ALTER SYSTEM KILL SESSION '753,8910';

session killed.

Hopefully after that you will be able to enable the foreign keys,if any and then the contention issue will be solved.

Answer 2

Here you find a brief explanation and some examples.

An append hint instructs Oracle to take out a TM lock in exclusive mode 6 (“enq: TM – contention” wait event).