Re: Causes for "Enq: TM - contention" on a table with conventional path insert?

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Thu, 15 May 2008 16:52:21 -0500
Message-ID: <203315c10805151452n20fa9dd2qa838a8630aa4807c@mail.gmail.com>


HI David
  Do you have a test case for this ? I used following table and inserted using the statement below, but only TM level 3 is acquired. I must be missing something here..

create table t_part
 (n1 number , n2 varchar2(1000) )
partition by range (n1)
( partition p1 values less than (101),

   partition p2 values less than (102),
  partition p3 values less than (302),
  partition p4 values less than (402),
partition p5 values less than (502),
 partition p6 values less than(602),
  partition p7 values less than (maxvalue) )
/
insert into t_part
select object_id, lpad(object_name, 255, 'x') from user_objects /

-- 
Cheers
Riyaj Shamsudeen
The Pythian Group - www.pythian.com
orainternals.wordpress.com

On Thu, May 15, 2008 at 3:37 PM, David Aldridge <david_at_david-aldridge.com>
wrote:


> 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.
>
> Thanks Riyaj
>
> ----- 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?
>
> 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
>
. -- http://www.freelists.org/webpage/oracle-l
Received on Thu May 15 2008 - 16:52:21 CDT

Original text of this message