Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: performance when inserting into child tables

Re: Re: performance when inserting into child tables

From: Jonathan Lewis <>
Date: Wed, 21 Apr 2004 21:38:30 +0100
Message-ID: <021201c427e0$9dbbbaa0$7102a8c0@Primary>

Did you tell us a version ?

Two thoughts:

    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.

    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).


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland 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

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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Wed Apr 21 2004 - 15:38:54 CDT

Original text of this message