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

  • From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
  • To: T.Koppelaars@xxxxxxxxxxxxxxxxxxxx, Oracle List <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Sep 2007 10:51:50 -0700 (PDT)

If the load were modified to be direct path, and I've no idea why it isn't 
already to be honest, then my intuition tells me that would remove the 
possibility of delayed block cleanout occuring ... does that sound correct? I 
can't think why the blocks would need to be cleaned following direct path 

"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);}             
   If delayed block cleanout causes writes to the controlfile, then this might 
be your problem.
  When the index is created then the blocks are reread from disk and at that 
moment are cleaned out and all the parallel slaves are fighting for the CF 
  From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of David Aldridge
Sent: woensdag 12 september 2007 14:32
To: Oracle List
Subject: Causes of CF (Control file) Enqueues on Index Creation?

    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: