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

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" http://www.akadia.com/download/documents/session_wait_events.pdf 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 :(



Other related posts: