RE: enq: TM contention
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "JC1706@xxxxxxx" <JC1706@xxxxxxx>, "tim@xxxxxxxxx" <tim@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Dec 2011 16:30:07 -0500
Also, direct load insert, i.e., INSERT /*+ APPEND */, will take TM enqueue in X
mode.
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of CRISLER, JON A
Sent: Friday, December 16, 2011 1:11 PM
To: tim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tim Gorman
Sent: Friday, December 16, 2011 11:16 AM
To: oracle-l@xxxxxxxxxxxxx
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?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
