Re: Causes of CF (Control file) Enqueues on Index Creation?

From: David Aldridge <david_at_david-aldridge.com>
Date: Sat, 15 Sep 2007 14:05:28 -0700 (PDT)
Message-ID: <892015.41968.qm@web801.biz.mail.mud.yahoo.com>

Ah, that's very interesting. I can't imagine why the parallel dml would lock the table though.   

Alberto Dell'Era <alberto.dellera_at_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"


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Sep 15 2007 - 16:05:28 CDT