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

  • From: "Koppelaars, Toon" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx>
  • To: <david@xxxxxxxxxxxxxxxxxx>, "Oracle List" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Sep 2007 14:48:32 +0200

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

Other related posts: