Grokbase › Groups › Oracle › oracle-l › December 2011
FAQ
Oracle EE 10.2.0.5

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?

Search Discussions

  • Bobak, Mark at Dec 16, 2011 at 3:19 pm ⇧
    Z­š‡^ŠËay¹hrH§‚Ǭ²*'†‰]Šx-…éhrH§ü‡â·û!¢Wbžæ¡×ÿ–Š$~ŠÝŠ·œ¶Zv)ìz»lÿó®Džž×ë¢i²2‹h®‰Z4H_ÿÿàj'?Óÿÿÿüêâ‚)Ú–g¬±¨ÿÿÿüZè›ôšÚÚç$z¿ìmç$z¾½ÛŸà™¨¥ýÊ&ý:ÿ¢¶œ•ïåþŠÚrWÿ—÷ëyéb²Ûÿ¢¸ÿIéíüZâü7œ×¯öÓ]uãýøÿ^Æ1?´Óý4JæãyËÿzzÿLÇ(ž×§¶*':¶œ•á×OöÿOùYèZ½æÞzw±¥êâzw"ž
    ^®*³^­ì±çš–‹h}éêý3¢{^žØ¨Ÿ¢¶ÏºËh™êìž‹bqê^­ú+™©Üy× ­§Z¶*'üg§z¶¥—(­rWš®Ê'ŠÛ(Â|"¶§×I¢žë^²Šì£ôáz‰åÉÆ®±âÿ½æÞzv›•ëh–‡µè§›+j·!zÏÛ¢Ø`¢ˆ%{óKø¬jz'þ)Ý{~ŠÞŠ ä{/Ó†+"±×ŸŠx­z\§¢ÛayÆ®±è§¢êÜjÇÿ"¯zí…§Zžéâ×±y×è­è žG²Šz/z½²yªìjwoz¸Ÿ‰çb¶Ÿûi¢š^vênìiyË^v\ÿ‡¬j|¨é'£
    jz-…êé¢Ë"n)b·'è­Æ®²)ඬÁ¨­ÿÿÒjwrN¶§³6©ü‰Üÿÿá¶Úÿÿü0Ã÷ëyéb²Ûÿ¢¸?Áæéj¿¢¶œ•ïå
  • Tim Gorman at Dec 16, 2011 at 4:34 pm ⇧
    Sandra,

    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
    question.

    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...

    -Tim
    On 12/16/2011 7:32 AM, Sandra Becker wrote:
    Oracle EE 10.2.0.5

    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?
    --
    http://www.freelists.org/webpage/oracle-l
  • CRISLER, JON A at Dec 16, 2011 at 6:12 pm ⇧
    I think the following can also cause this problem-

    Alter table validate structure cascade; (i.e you don't use the online option)
    Alter index rebuild (again not using the online option).

    Also, this contention might not be a big deal in a single instance db but grow to be a big deal in RAC.
    Are you running RAC ?

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Tim Gorman
    Sent: Friday, December 16, 2011 11:16 AM
    To: oracle-l@freelists.org
    Subject: Re: enq: TM contention

    Sandra,

    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
    question.

    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...

    -Tim
    On 12/16/2011 7:32 AM, Sandra Becker wrote:
    Oracle EE 10.2.0.5

    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?
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Dec 16, 2011 at 9:31 pm ⇧
    Also, direct load insert, i.e., INSERT /*+ APPEND */, will take TM enqueue in X mode.

    -Mark

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of CRISLER, JON A
    Sent: Friday, December 16, 2011 1:11 PM
    To: tim@evdbt.com; oracle-l@freelists.org
    Subject: RE: enq: TM contention

    I think the following can also cause this problem-

    Alter table validate structure cascade; (i.e you don't use the online option) Alter index rebuild (again not using the online option).

    Also, this contention might not be a big deal in a single instance db but grow to be a big deal in RAC.
    Are you running RAC ?

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Tim Gorman
    Sent: Friday, December 16, 2011 11:16 AM
    To: oracle-l@freelists.org
    Subject: Re: enq: TM contention

    Sandra,

    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 question.

    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...

    -Tim
    On 12/16/2011 7:32 AM, Sandra Becker wrote:
    Oracle EE 10.2.0.5

    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?
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 16, '11 at 2:33p
activeDec 16, '11 at 9:31p
posts5
users4
websiteoracle.com

4 users in discussion

Bobak, Mark: 2 posts Tim Gorman: 1 post CRISLER, JON A: 1 post Sandra Becker: 1 post

Content

People

Support

Translate

site design / logo © 2022 Grokbase