Re: Causes for "Enq: TM - contention" on a table with conventional path insert?
Date: Thu, 15 May 2008 15:26:42 -0500
- Is it possible to modify insert statement to specify partition name ? using bind variables, it may be possible to deduce partition name. insert into target_table partition (pname) ..
This can be done using dynamic SQL.
2. Are you sure insert statement is executing serially? No parallelism involved here? I have seen few parallel DML operations acquiring exclusive lock on the table. Trace or explain plan to see whether paralellism in play or not?
The Pythian Group :www.pythian.com
On Thu, May 15, 2008 at 3:17 PM, David Aldridge <david_at_david-aldridge.com> wrote:
> 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.