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 15:16:56 +0200
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 enqueue.
________________________________
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"
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:
