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