| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: performance when inserting into child tables
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
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.
 |
 |