Below are Copy and Pastes from the MOS doc:
"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:Drill down into individual sessions showing high waits to understand which application code , which objects, and which SQL/s are involved in the waits. ASH reports for sessions showing high "enq: TM - contention" wait time may help.
- Use ADDM reports to identify SQLs and sessions involved in high TM waits at systemwide level. See Note:1680075.1 for instructions on running ADDM.
- Is systemwide wait time is due to a few session/s or are waits widespread? eg: few long waits V many short waits ?
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 ;
Troubleshooting
See the following documents for help troubleshooting issues relating to TM lock waits:Document:1905174.1 Resolving Issues Where 'enq: TM - contention' Waits are Occurring
Document:33453.1 Locking and Referential Integrity
I did a search on this wait event. And, also found some blog posts about this wait happening whenever there are un-indexed foreign key constraints on a table.
But, in my humble opinion, I would use the Oracle MOS document as my guide first before looking for unindexed foreign key constraints.
To find all un-indexed foreign key constraints:
SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM dba_constraints c, dba_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM dba_indexes i, dba_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position;
Happy waiting ;-)
