Re: 8i to 9i migration, performance issues

You're right.

I don't automatically believe that everything on Metalink is correct, so I've been trying to cross-check all the relevant information.

I think I came to my conclusion on the basis of a particular set of tests on 9.0.1 (which I can't run any more), and a note which said:

    "be careful, unnesting is not costed in 8i." Alas, classic dodo argument, I extrapolated this to "... but it is in 9.0".

I've also done a little extra work with 10.1 in the last couple of hours, and from a few 10053 traces, it looks as if exactly the same is true of 10g - apart from the cosmetic bits and extra numbers, the 10g traces on unnesting look identical to the 9i traces, which suggests that 10g does not check filtering against unnesting costs.

Thanks for the correction.

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


"Paul" <pkelley_at_coat.com> wrote in message
news:473377b4.0403300744.4d535155_at_posting.google.com...


> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

news:<c32kh5$dm$1_at_sparta.btinternet.com>...


> > Notes in-line.

> >

> >

> > One of the disabled features is subquery unnesting,

> > and one of the bugs in the 9.2 optimizer is the fact

> > that the costing for unnested subqueries is extremely

> > high (fixed in 10.1), so unnesting happens pretty

> > much everywhere it can.  This means you can end up

> > with hideously inefficient unnests, where a filtered

> > subquery would be much better.

>

> From metalink:

>

> "Please note that the decision to unnest a subquery is not costed in

> Oracle9i. The decision to unnest a subquery is taken based on a set of

> heuristics (rules) before the query is optimized."