From oracle-l-bounce@freelists.org Wed Apr 21 15:38:54 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3LKcso18350 for ; Wed, 21 Apr 2004 15:38:54 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3LKcs618345 for ; Wed, 21 Apr 2004 15:38:54 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6497E72D740; Wed, 21 Apr 2004 15:30:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15513-32; Wed, 21 Apr 2004 15:30:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A624772EEC4; Wed, 21 Apr 2004 15:30:20 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 21 Apr 2004 15:28:44 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ED70072E37A for ; Wed, 21 Apr 2004 15:27:23 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14247-68 for ; Wed, 21 Apr 2004 15:27:23 -0500 (EST) Received: from carbon.btinternet.com (carbon.btinternet.com [194.73.73.92]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BD34872E453 for ; Wed, 21 Apr 2004 15:26:55 -0500 (EST) Received: from [81.132.50.98] (helo=Primary) by carbon.btinternet.com with smtp (Exim 3.22 #25) id 1BGOUL-0005nY-00 for oracle-l@freelists.org; Wed, 21 Apr 2004 21:38:33 +0100 Message-ID: <021201c427e0$9dbbbaa0$7102a8c0@Primary> From: "Jonathan Lewis" To: References: <20040421193216.DDPB19374.lakermmtao06.cox.net@smtp.central.cox.net> Subject: Re: Re: performance when inserting into child tables Date: Wed, 21 Apr 2004 21:38:30 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3499 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Did you tell us a version ? Two thoughts: One: As you insert into the child, a parent ID has to be checked. If the parent index is large and the child inserts are randomly ordered, you may be losing a lot of time because recently checked parent IDs have been flushed and have to be re-read. Option 1: can you order by the parent id before you insert Option 2: can you move the parent index into a KEEP pool that has been sized to hold the entire parent. Two: I've never checked this, but when you do the insert/select on the child, it is possible that the normal array insert optimization (which reduces the volume of undo and redo significantly) cannot take place because of the intervening integrity test on each row. In this case, it isn't the child insert that is slow, it's the parent insert that's quick. (I'll check this some time, and try to remember to report back). Of course, there's always the possibility that the child insert is relatively slower because there are more indexes on the child than there were on the parent; or because the child table is larger, and the 'cyclic flushing' that I mentioned for the parent index is actually happening on the child index as it is updated. As Jared said - run a 10046 trace at level 8: this will tell you which blocks are being waited on: most likely to be parent index leaf blocks (thought 1) or leaf blocks from other child indexes (note above). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar ----- Original Message ----- From: To: Sent: Wednesday, April 21, 2004 8:32 PM Subject: Re: Re: performance when inserting into child tables I checked v$session_event and though my log file switch and db file scattered read waits are high, I wouldn't expect them to be any higher than when I ran this array insert against the parent table. I have no enqueue waits as expected. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------