Re: Causes for "Enq: TM - contention" on a table with conventional path insert?
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: Mark Brinsmead <pythianbrinsmead@xxxxxxxxx>, riyaj.shamsudeen@xxxxxxxxx
- Date: Thu, 15 May 2008 19:09:07 -0700 (PDT)
Hmmm, I think that the logical possibility of an insert into any partition
would defeat the purpose, and the lock would be held at the table level.
----- Original Message ----
From: Mark Brinsmead <pythianbrinsmead@xxxxxxxxx>
To: riyaj.shamsudeen@xxxxxxxxx
Cc: david@xxxxxxxxxxxxxxxxxx; Oracle List <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 15, 2008 9:45:02 PM
Subject: Re: Causes for "Enq: TM - contention" on a table with conventional
path insert?
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:
