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

Oracle ASM Header Backup -- Update

This post will be an update to a post that I made on Thursday, December 09, 2010 about Oracle ASM Header Backups.  

There are multiple ways to skin this cat.  Another option for backing up your ASM file headers is to use "kfed".  Yes, I know, Oracle Support doesn't recommend that we use it.  However, if you ever need to recover the header, you will be very glad you did.  Below is a partial example of a backup using kfed:

/u01/app/oracle/product/12.1.0/grid_1/bin/kfed read ${ASM_DEV_DIR}/${i} text=${HDR_BACKUP_DIR}/${i}.${TODAY}.bkp

Utilizing "kfed read" will allow you to recover the header in case of corruption.  If you have header corruption and your other methods of header backup (like md_backup and dd's) do not work, then you can tell Oracle Support that you have a kfed file of the header from before the corruption.

As with any corruption, make sure you open an SR with Oracle Support before attempting any repair yourself.

For more information, review the information in My Oracle Support (MOS) regarding kfed.