David 1. 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? Cheers Riyaj Shamsudeen The Pythian Group :www.pythian.com orainternals.wordpress.com On Thu, May 15, 2008 at 3:17 PM, David Aldridge <david@xxxxxxxxxxxxxxxxxx> wrote: > 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? >