Re: Re: performance when inserting into child tables

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 22 Apr 2004 10:34:09 +0100
Message-ID: <006401c4284c$fc8051b0$7102a8c0@Primary>

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

Under 8i, every child row is checked against its parent
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_at_wangtrading.com> To: <oracle-l_at_freelists.org> 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_at_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
-----------------------------------------------------------------

Received on Thu Apr 22 2004 - 04:30:38 CDT