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

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Sun, 16 Sep 2007 00:19:10 +0200
Message-ID: <4ef2fbf50709151519u69b40688j8c7519fd03f65526@mail.gmail.com>

On 9/15/07, David Aldridge <david_at_david-aldridge.com> 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"
--
http://www.freelists.org/webpage/oracle-l

Received on Sat Sep 15 2007 - 17:19:10 CDT