Re: Causes for "Enq: TM - contention" on a table with conventional path insert?
- From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
- To: riyaj.shamsudeen@xxxxxxxxx
- Date: Thu, 15 May 2008 19:45:02 -0600
It may not be as complicated as that. Check the documentation on
multi-table inserts.
I have no idea whether or not a multi-table insert will avoid the
bottleneck, but the O.P. might at least find that it provides a convenient
way to encode which rows should be inserted into which partitions. Of
course, if there are hundreds of partitions, the coding could drive you
insane, and I am pretty sure that somewhere there is a maximum length for a
SQL statement. (40,000 bytes?)
Just a thought...
On Thu, May 15, 2008 at 2:26 PM, Riyaj Shamsudeen <
riyaj.shamsudeen@xxxxxxxxx> wrote:
> 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?
>>
>
>
--
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs
Other related posts:
