Re: Re: performance when inserting into child tables
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Apr 2004 10:34:09 +0100
Mladen,
I like to call it "The Scientific Method", others
may call it "Guess and Check".
There are usually ways to build test cases that
test a hypothesis without the need to walk the
code path.
In this particular case it should be quite easy:
Hypothesis:
An array insert into a child table does not
take advantage of the multi-row optimisation
on undo and redo.
Test case:
Create starting data set
Insert 10,000 rows into 'child' table at about
5 rows per parent when the FK constraint
has not been defined. Check quantity of redo,
undo, and cache buffers chains latching.
Repeat when FK constraint has been enabled.
Compare results to see if there are any significant
differences.
Based on results:
Repeat tests with (e.g.)
more / less child rows per parent
sorted child rows
randomly distributed child rows
In passing, I did some of these tests some time ago
because of a comment in the 9i manuals (or on metalink)
about optimisation for FK checks, and found that
a) Under 8i, every child row is checked against its parent
b) Under 9i, Oracle SEEMS to remember parents it has
previously checked, and doesn't check them again. This
reduces latching and CPU, of course. I haven't investigate
further to figure out what other side effects it may have
(e.g. does it pin index leaf blocks in exclusive mode !)
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: "Mladen Gogala" <mladen@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 21, 2004 10:10 PM
Subject: Re: Re: performance when inserting into child tables
On 04/21/2004 04:38:30 PM, Jonathan Lewis wrote:
> 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).
Jonathan, just how will you set about checking that assumption? The only
difference, as far as I can see would be a different path through the
oracle executable. Are you linking with a profiler or running things
through the dbx? The only tool that I have that can potentially apply
is tracing with the event 10053. Do you expect the SQL execution path
different in the optimizer? I'm extremely curious about the method for
checking things like that. Despite all my years of experience, I wasn't
able to come up with anything that would allow me to analyze path through
the oracle executable itself, although God knows that I tried some
rather nasty things, some of which have included the "dis" command, but
I didn't become any smarter.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
