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

  • From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
  • To: "Koppelaars, Toon" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx>, Oracle List <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Sep 2007 08:03:43 -0700 (PDT)

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" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx> 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 


    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 :(

Other related posts: