Re: Causes for "Enq: TM - contention" on a table with conventional path insert?

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_at_david-aldridge.com> To: "Oracle List" <oracle-l_at_freelists.org> 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

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