Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Causes of CF (Control file) Enqueues on Index Creation?

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

From: David Aldridge <>
Date: Wed, 12 Sep 2007 08:03:43 -0700 (PDT)
Message-ID: <>

In a packaged procedure the table is truncated, then a single conventional path insert ... select from ... is executed and commited. The session then exits. A separate session connects to create the index.    

  I was incorrect before -- this is actually a global index against the partitioned table.    

  Thanks Toon -- your book is making my brain hurt, by the way.

"Koppelaars, Toon" <> wrote:

        v\:* {behavior:url(#default#VML);}  o\:* {behavior:url(#default#VML);}  w\:* {behavior:url(#default#VML);}  .shape {behavior:url(#default#VML);}                >> “The table has just been loaded”

  How exactly is this table loaded?           


    We recently noticed, through Quest Performance Analysis, that a particular index creation statement on a newly populated table occasionally suffers from enqueue waits that extend the creation time from a few minutes to over an hour.      

    We used statspack to investigate further and found that at the time of index creation we get a spike in CF enqueue waits.      

    I have read Anjo Kolk's whitepaper "Description of Oracle7 Wait Events and Enqueues" in which he identifies an imposing list of situations in which the CF enqueue is used.      

    The index is a locally partitioned single column uncompressed b-tree built in parallel degree 8 against a range partitioned heap table with 8 partitions. The table has just been loaded when this index is created and it's the first to be created post-load. Other subsequently-built indexes do not appear to have suffered from this problem. the index creation takes place in a new session that starts immediately after a previous session has populated the table, commited and gracefully exited. The issue occurs intermittently, not on every occasion that the table is loaded.      

    I don't have access to the statspack data we extracted at the moment ... I'll post it as soon as I do.      

    Does anyone have any quick thoughts about the relationship between creating an index and a control file lock? I'm really pushing the limits of my internals knowledge here -- OK, actually I have gone past it :(

Received on Wed Sep 12 2007 - 10:03:43 CDT

Original text of this message