Re: Causes for "Enq: TM - contention" on a table with conventional path insert?
Date: Thu, 15 May 2008 13:37:25 -0700 (PDT)
Yes, including the partition name would be the best solution, and would also allow me to use direct path insers. However, it also would require some awkward recoding, UAT, performance testing, tedious documenting for release notes, migration notes etc.. I love documenting stuff as much as the next guy, but I'd rather not if I don't have to ;)
Parallel insert would imply direct path, and would require the explicit enabling of parallel dml. There's nothing in the code to permit that right now.
- Original Message ---- From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com> To: david_at_david-aldridge.com Cc: Oracle List <oracle-l_at_freelists.org> Sent: Thursday, May 15, 2008 4:26:42 PM Subject: Re: Causes for "Enq: TM - contention" on a table with conventional path insert?
- 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:
Event: enq: TM - contention
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?Received on Thu May 15 2008 - 15:37:25 CDT