Oracle Wait - enq: TM - contention

Oracle Waits.  Aren't they fun?  Here is an MOS document that has lots of good information regarding enq: TM - contention waits:  WAITEVENT: "enq: TM - contention" Reference Note (Doc ID 1980175.1)

Below are Copy and Pastes from the MOS doc:

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:

• 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 ?

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.

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 ;-)