Re: Causes for "Enq: TM - contention" on a table with conventional path insert?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 May 2008 08:15:29 +0100
I can't reproduce this in 10.2.0.1
Is the select list anything other than a simple list of columns ?
Are there any LOB columns or ADTs involved ?
How about tracing with 10046 level 8 to see what happens
just before the exclusive lock is acquired - it might give you
some clue.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "David Aldridge" <david@xxxxxxxxxxxxxxxxxx>
To: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 15, 2008 9:17 PM
Subject: Causes for "Enq: TM - contention" on a table with conventional path
insert?
Folks,
I have a situation like this:
10.2.0.2 EE on Sparc 64bit
An insert statement of the form ...
INSERT INTO target_table
(...)
SELECT /*+ ORDERED FULL (t1) FULL(t2) FULL(t3) */
...
FROM t1, t2, t3, t4
WHERE t1.col1 = t2.col2
AND t1.col3 = t3.col4
AND t1.col1 >= :b2
AND t1.col1 < :b1
/
The target table is range partitioned with 8 ranges, and 8 copies of the above
query run simultaneously to load the table using different :b1 and :B2 values.
Each statement loads a single partition but the optimizer has no way of
deducing that fact. (Not my design!)
There are no indexes on the table at the time of load.
There are no constraints on the table at all.
Oracle is serializing the inserts so that only one session can insert into the
target table at a time. I would expect this if I was performing direct path
load, maybe if there were bitmap indexes, or if there were FK's involved
perhaps, but I've ruled out all of those.
V$SESSION_WAIT shows the following:
SID: 394
SEQ#: 79
Event: enq: TM - contention
P1TEXT: name|mode
...
P1RAW: 00000000544D0006 -- ie. a TM Exclusive?
.. and the P2 parameter gives the object_id for the target table in the insert
statement
So I'm a bit at a loss to know what else could cause this, or what tests to
run next time i execute this process in order to get more data to help analyze
it.
Thoughts?
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 269.23.16/1433 - Release Date: 14/05/2008
16:44
--
//www.freelists.org/webpage/oracle-l
Other related posts:
