Re: enq: TM contention

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 16 Dec 2011 09:15:45 -0700


Get to the session level, either looking at the "live" GV$SESSION view 
or at the "historical" GV$ACTIVE_SESSION_HISTORY view or at the 
"archive" DBA_HIST_ACTIVE_SESS_HISTORY view.  Look at sessions waiting 
on the event, and look at the SQL statement (via the SQL_ID and 
DBMS_XPLAN) being executed.  That alone should provide a huge amount of 
insight into what is going on.

My guess, just to throw out a hypothesis:  there is a long-running 
CREATE INDEX command running that is blocking INSERT/UPDATE/DELETE 
commands against the table?

Essentially, a exclusive "TM" enqueue is taken by a session to while an 
object's basic structure is being modified, while a shared "TM" enqueue 
(along with a "TX" enqueue) is taken by a DML command to prevent DDL 
from being performed during the transaction.

So, if you try to CREATE INDEX on a table which is being modified by a 
transaction, you immediately get ORA-00054 ("resource is busy") because 
the CREATE INDEX could not obtain the exclusive "TM" enqueue because the 
active transactions were holding shared "TM" enqueues on the table in 

Conversely, if a long-running CREATE INDEX is running and people attempt 
to perform DDL, the requested shared "TM" enqueue will queue up waiting 
for the exclusive "TM" enqueue to finish, hence your "enq: TM - 
contention" waits by sessions.

Examining the session-level views mentioned above may prove me 
completely 180-degrees wrong, but that's probably the general situation.

Hope this helps...


On 12/16/2011 7:32 AM, Sandra Becker wrote:
> Oracle EE
> We have been experiencing periods where we see a lot of enq: TM
> contention waits.  Customers notice performance degradation.
> Generally it clears on its own within 10 minutes or so.  The only
> cause I've been able to locate in my searches (both google&  MOS) is a
> non-indexed foreign key.  This is definitely not the cause in our
> case.  I haven't had an unindexed foreign key in over 2 years and
> verified it hadn't popped up unexpectedly.
> Does anyone know of another possibility for causing this wait?

Other related posts: