Enqueues are locks that coordinate access to database resources. This event indicates that the session is waiting for a lock that is held by another session.
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TM enqueue:
The most common reason for waits on TM locks tend to involve foreign key constraints where the constrained columns are not indexed. Index the foreign key columns to avoid this problem.
This wait indicates time spent waiting for a TM lock. TM locks are used to coordinate activities for many base table / partition operations.
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. And these locks on the child table are full table locks .
When other sessions try to modify this child table they see a full table lock on the child table and thus have to wait for the lock to be released. This wait is denoted as enq: TM contention.
Now to resolve these waits we have to ensure that those full table locks are avoided and the easiest way to do it is by indexing the unindexed foreign key constraint
select * from (
select ct.table_name, co.column_name, co.position column_position
from user_constraints ct, user_cons_columns co
where ct.constraint_name = co.constraint_name
and ct.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;
Wait Time:
Oracle waits for the TM lock until it is either granted or the request times out. Deadlock detection occurs for TM locks.
Finding Blockers:
You can find current blockers by querying GV$LOCK like this:
SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request
FROM
( SELECT p2, p3, 'TM-'||substr(p2raw,-8)||'-'||lpad(p3,8,'0') TM
FROM v$session_wait
WHERE event='enq: TM - contention'
and state='WAITING'
) W,
gv$lock L
WHERE l.type(+)='TM'
and l.id1(+)=w.p2
and l.id2(+)=w.p3
ORDER BY tm, lmode desc, request desc
;
Systemwide Waits:
"enq: TM - contention" waits are generally related to the application code being executed and do not indicate a problem with the DB itself.
Possible things to check:
1) Use ADDM reports to identify SQLs and sessions involved in high TM waits at systemwide level.
2) Is systemwide wait time is due to a few session/s or are waits widespread?
eg: few long waits V many short waits ?
Drill down into individual sessions showing high waits to understand which application code , which objects, and which SQL/s are involved in the waits.
3) ASH reports for sessions showing high "enq: TM - contention" wait time may help.
Reducing Waits / Wait times:
Reducing waits typically involves altering application code to avoid the contention scenario/s. ADDM can help advise on this sort of wait . In particular be sure to check if the waits look to be due to unindexed foreign key constraints as this is a very common scenario.
