Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 18130 invoked from network); 14 Sep 2007 21:48:04 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 14 Sep 2007 21:48:03 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 32309742A77; Fri, 14 Sep 2007 22:09:20 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20628-05-2; Fri, 14 Sep 2007 22:09:20 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F4067444A9; Fri, 14 Sep 2007 22:09:19 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 14 Sep 2007 21:24:18 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 62682743C82 for ; Fri, 14 Sep 2007 21:24:18 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04166-06-5 for ; Fri, 14 Sep 2007 21:24:18 -0400 (EDT) Received: from web812.biz.mail.mud.yahoo.com (web812.biz.mail.mud.yahoo.com [209.191.90.85]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id D747D7444BC for ; Fri, 14 Sep 2007 21:24:17 -0400 (EDT) Received: (qmail 66233 invoked by uid 60001); 15 Sep 2007 02:03:00 -0000 X-YMail-OSG: pwttmTYVM1kialiQxOwT6HjgVg9DKHY4QwjwCSxcJbqMIKwUzwf0oclORMMMup8zAkaxy7r01QxAzB2oOHgLvgaBT5jnEj8q5Wc1X1R6AXsbzg8- Received: from [66.193.86.210] by web812.biz.mail.mud.yahoo.com via HTTP; Fri, 14 Sep 2007 19:03:00 PDT Date: Fri, 14 Sep 2007 19:03:00 -0700 (PDT) From: David Aldridge Subject: RE: Causes of CF (Control file) Enqueues on Index Creation? To: david@david-aldridge.com, T.Koppelaars@centraal.boekhuis.nl, Oracle List In-Reply-To: <583540.70389.qm@web806.biz.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1446616220-1189821780=:65550" Message-ID: <486947.65550.qm@web812.biz.mail.mud.yahoo.com> X-archive-position: 1589 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: david@david-aldridge.com Precedence: normal Reply-to: david@david-aldridge.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain --0-1446616220-1189821780=:65550 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit There's a fresh complication on this -- there are actually eight simultaneous processes inseeting into the table, so changing to a direct path insert causes a big table locking problem. I thought I could alleviate that with a parallel direct path load (ie. load to new extents instead of above the HWM) but that didn't get rid of the locking for some reason -- maybe there is a lock held by the query coordinator in each case that causes the contention. So here're my other desperate stabs at quick workarounds -- an upgrade to 10g and/or moving each partition into it's own TS. My feeling is that either of these would help reduce the pq slaves fighting over locks, the former because of a achange in the checkpointing mechanism with 10g fromTS-based checkpoints to segment-based checkpoints, and the latter because it would pretty much mean the same thing. All thoughts appreciated. David Aldridge wrote: If the load were modified to be direct path, and I've no idea why it isn't already to be honest, then my intuition tells me that would remove the possibility of delayed block cleanout occuring ... does that sound correct? I can't think why the blocks would need to be cleaned following direct path insert. "Koppelaars, Toon" wrote: v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} 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@freelists.org [mailto:oracle-l-bounce@freelists.org] 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 :( --0-1446616220-1189821780=:65550 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
There's a fresh complication on this -- there are actually eight simultaneous processes inseeting into the table, so changing to a direct path insert causes a big table locking problem. I thought I could alleviate that with a parallel direct path load (ie. load to new extents instead of above the HWM) but that didn't get rid of the locking for some reason -- maybe there is a lock held by the query coordinator in each case that causes the contention.
 
So here're my other desperate stabs at quick workarounds -- an upgrade to 10g and/or moving each partition into it's own TS. My feeling is that either of these would help reduce the pq slaves fighting over locks, the former because of a achange in the checkpointing mechanism with 10g fromTS-based checkpoints to segment-based checkpoints, and the latter because it would pretty much mean the same thing.
 
All thoughts appreciated.

David Aldridge <david@david-aldridge.com> wrote:
If the load were modified to be direct path, and I've no idea why it isn't already to be honest, then my intuition tells me that would remove the possibility of delayed block cleanout occuring ... does that sound correct? I can't think why the blocks would need to be cleaned following direct path insert.

"Koppelaars, Toon" <T.Koppelaars@centraal.boekhuis.nl> wrote:
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@freelists.org [mailto:oracle-l-bounce@freelists.org] 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 :(


--0-1446616220-1189821780=:65550-- -- http://www.freelists.org/webpage/oracle-l