Re: 8i to 9i migration, performance issues

Pls. see below:

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:c4ckgs$pee$1_at_hercules.btinternet.com...
>
> Notes in-line
>
> --
> 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
>
>
> "VC" <boston103_at_hotmail.com> wrote in message
> news:Aajac.139090$Cb.1489695_at_attbi_s51...
> > > 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."
> >
> > Whatever the 'set of heuristics' Oracle implements in 9i is, it sure
> creates
> > some atrocious execution plans.
>
> I think the heuristics may be little more than:
> if the result set is the same, do it.
>
> There was an enhancement in 9.0 that added
> code to recognise increasing numbers of
> options - (I thought it was talking cost, but in
> fact I now realise that it must have been talking
> about patterns).
>
> > We had to add an ugly hack (WHERE
ROWNUM
> >
> > 0) to quite a few of our queries in order to prevent Oracle from
> un-nesting
> > them ( the hint does not always work).
>
> I did find a note that said you should put the
> unnest hint in the main query - I believe it is
> wrong (though I now hesitate about making a
> firm claim, given my last error on unnesting).
> Whenever I've wanted to unnest, or no_unnest,
> I've always put the hint in the subquery that I
> want to fix, and it's never failed me yet.

I should have been more precise in my wording. The no_unnest hint does sort of work if placed in the subquery but the resulting plan, for more-or-less complexx queries, is not as good as with the rownum>0 trick The rownum trick creates a plan identical to what we had under 8i.

In case you wanted to see the query, I've sent it to you via e-mail.

Rgds.

>
Received on Tue Mar 30 2004 - 16:28:54 CST