Date: Sat, 15 Sep 2007 14:05:28 -0700 (PDT)
From: David Aldridge <david@david-aldridge.com>
Subject: Re: Causes of CF (Control file) Enqueues on Index Creation?
To: alberto.dellera@gmail.com
Cc: T.Koppelaars@centraal.boekhuis.nl, Oracle List <oracle-l@freelists.org>
In-Reply-To: <4ef2fbf50709150315l6f7bfdb8v97e35662a00f4ea0@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1811778295-1189890328=:41968"
Message-ID: <892015.41968.qm@web801.biz.mail.mud.yahoo.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: david@david-aldridge.com
--0-1811778295-1189890328=:41968
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Ah, that's very interesting. I can't imagine why the parallel dml would lock the table though.
  
Alberto Dell'Era <alberto.dellera@gmail.com> wrote:
  On 9/15/07, David Aldridge wrote:
> There's a fresh complication on this -- there are actually eight
> simultaneous processes inserting into the table, so changing to a direct
> path insert causes a big table locking problem.

An insert /*+ append */ in a partitioned table acquires a mode 6 lock on
each partition, even if you insert in only one partition (the process first
locks all partitions, then starts inserting). See attached test case (9.2.0.8):

SID TYPE NAME LMODE REQUEST
---------- ------ ------------------------------ ---------- ----------
11 TM TABLE PARTITION T P0 6 0
11 TM TABLE PARTITION T P1 6 0
11 TM TABLE T 3 0

If you can make each process insert in its own partition, a simple fix
is to specify the partition explicitly:
insert /*+ append */ into t partition (Pk) ... select ...
In this case, only Pk is locked exclusively and you can insert
simultaneously from the eight sessions (see test case, uncomment
the "partition (...)" clauses if you want to check it yourself ;)

HTH
Alberto
-- 
Alberto Dell'Era
"the more you know, the faster you go"


--0-1811778295-1189890328=:41968
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<div>Ah, that's very interesting. I can't imagine why the parallel dml would lock the table though.</div>  <div><BR><B><I>Alberto Dell'Era &lt;alberto.dellera@gmail.com&gt;</I></B> wrote:</div>  <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">On 9/15/07, David Aldridge <DAVID@DAVID-ALDRIDGE.COM>wrote:<BR>&gt; There's a fresh complication on this -- there are actually eight<BR>&gt; simultaneous processes inserting into the table, so changing to a direct<BR>&gt; path insert causes a big table locking problem.<BR><BR>An insert /*+ append */ in a partitioned table acquires a mode 6 lock on<BR>each partition, even if you insert in only one partition (the process first<BR>locks all partitions, then starts inserting). See attached test case (9.2.0.8):<BR><BR>SID TYPE NAME LMODE REQUEST<BR>---------- ------ ------------------------------ ---------- ----------<BR>11 TM TABLE PARTITION T P0 6 0<BR>11 TM TABLE PARTITION T P1 6
 0<BR>11 TM TABLE T 3 0<BR><BR>If you can make each process insert in its own partition, a simple fix<BR>is to specify the partition explicitly:<BR>insert /*+ append */ into t partition (Pk) ... select ...<BR>In this case, only Pk is locked exclusively and you can insert<BR>simultaneously from the eight sessions (see test case, uncomment<BR>the "partition (...)" clauses if you want to check it yourself ;)<BR><BR>HTH<BR>Alberto<BR>-- <BR>Alberto Dell'Era<BR>"the more you know, the faster you go"<BR></BLOCKQUOTE><BR>
--0-1811778295-1189890328=:41968--
--
http://www.freelists.org/webpage/oracle-l