CBO Subquery Limitations

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 Received on Wed Aug 10 2005 - 07:26:28 CDT