Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Causes of CF (Control file) Enqueues on Index Creation?
In a packaged procedure the table is truncated, then a single conventional path insert ... select from ... is executed and commited. The session then exits. A separate session connects to create the index.
I was incorrect before -- this is actually a global index against the partitioned table.
Thanks Toon -- your book is making my brain hurt, by the way.
"Koppelaars, Toon" <T.Koppelaars_at_centraal.boekhuis.nl> wrote:
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} >> “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 :(
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 12 2007 - 10:03:43 CDT