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

On 9/15/07, David Aldridge <david_at_david-aldridge.com> 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




application/zip attachment: insert_append_part.zip