Re: Causes of CF (Control file) Enqueues on Index Creation?
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: alberto.dellera@xxxxxxxxx
- Date: Sat, 15 Sep 2007 14:05:28 -0700 (PDT)
Ah, that's very interesting. I can't imagine why the parallel dml would lock
the table though.
Alberto Dell'Era <alberto.dellera@xxxxxxxxx> 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"
Other related posts:
