Causes of CF (Control file) Enqueues on Index Creation?
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: Oracle List <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Sep 2007 05:31:59 -0700 (PDT)
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:
