Re: CBO Subquery Limitations

<mccmx_at_hotmail.com> wrote in message
news:1123676788.751389.225390_at_g44g2000cwa.googlegroups.com...
> Oracle 9.2.0.6 EE on Windows
>
> I'm finding that the CBO has some serious limitations when it comes to
> evaluating SQL with subqueries in 9i.
>
> It appears that it will Unnest subqueries whenever possibe without any
> real analysis as to whether it is a good thing to do. This is because
> unnesting is chosen (or not) at the query transformation stage in the
> parse process and it is therefore not costed.
>
> The 'Pushing' of subqueries (i.e. evaluating them at the earliest
> opportunity) also appears to be uncosted. By default Oracle will not
> push them.
>
> I'm currently administering a Peoplesoft HRMS application which makes
> heavy use of correlated subqueries (i.e. nearly every pice of SQL), and
> I'm finding that I have to hint nearly evey SQL statement to get the
> best plan. Overall the performance of the system is very poor because
> the optimizer is choosing a significantly inferior plan in 90% of the
> cases.
>
> As far as I know, Unnesting has become a costed operation in 10g.
>
> Does anyone know if this is true of Pushing subqueries..? (Oracle
> support were unable to provide me with the info since development don't
> make this information publicly available.)
>
> Has anyone 'seen' an improvement in performance for SQL with subqueries
> when moving to 10g..?
>
> Thanks
>
> Matt
>

Unnesting of queries is generally cost-driven in 10g, but there are still some classes of query where transformation occurs without costing.

Pushing of subqueries is currently not a cost-based option. It doesn't happen unless you hint it.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005