Re: Causes of CF (Control file) Enqueues on Index Creation?
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: "David Aldridge" <david@xxxxxxxxxxxxxxxxxx>
- Date: Sun, 16 Sep 2007 00:19:10 +0200
On 9/15/07, David Aldridge <david@xxxxxxxxxxxxxxxxxx> wrote:
> Ah, that's very interesting. I can't imagine why the parallel dml would lock
> the table though.
Because the Query Coordinator locks all the partitions exclusively
(see log at the bottom).
I understand what you are thinking - the parallel processes could
in principle insert into temporary segments, and only at the end
lock the table/partitions and attach the temporary segments
to the partitions segments.
I agree that it would be a nice enhancement (and btw, what a joy would be
to have a command such as ALTER TABLE APPEND SEGMENT ;)
HTH
Alberto
SQL> create table t (x int)
2 partition by range (x) (
3 partition p0 values less than (1),
4 partition p1 values less than (2)
5 );
Table created.
SQL>
SQL> alter session enable parallel dml;
Session altered.
SQL>
SQL> insert /*+ append parallel(t,2) */ into t /* partition (p0) */ (x) select 0
2 from dual connect by level <= 2;
2 rows created.
SQL>
SQL> col name form a30
SQL> select sid, type, (select object_type||' '||object_name||'
'||subobject_name
2 from dba_objects
3 where object_id = id1) name,
4 lmode, request
5 from v$lock where sid in (select sid from v$session where
username='DELLERA')
6 and type='TM'
7 order by sid, name;
SID TYPE NAME LMODE REQUEST
---------- ------ ------------------------------ ---------- ----------
9 TM TABLE PARTITION T P1 4 0
9 TM TABLE PARTITION T P1 1 0
9 TM TABLE T 3 0
10 TM TABLE PARTITION T P0 6 0
10 TM TABLE PARTITION T P1 6 0
10 TM TABLE T 3 0
13 TM TABLE PARTITION T P0 4 0
13 TM TABLE PARTITION T P0 1 0
13 TM TABLE T 3 0
9 rows selected.
SQL>
SQL> select qcsid, sid from v$px_session;
QCSID SID
---------- ----------
10 10
10 13
10 9
--
Alberto Dell'Era
"the more you know, the faster you go"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
