Re: 8i to 9i migration, performance issues

From: VC <boston103_at_hotmail.com>
Date: Tue, 30 Mar 2004 18:40:32 GMT
Message-ID: <Aajac.139090$Cb.1489695@attbi_s51>

Hello,

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

Whatever the 'set of heuristics' Oracle implements in 9i is, it sure creates some atrocious execution plans. 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).

Rgds. Received on Tue Mar 30 2004 - 12:40:32 CST