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

On 10 Aug 2005 05:26:28 -0700, mccmx@hotmail.com wrote:

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


IIRC Peoplesoft is a so-called 'Database Agnostic' application, and
requires RBO.

Apart from that Database Agnostic applications usually only support
the Worst Common Denominator of versions supported.


--
Sybrand Bakker, Senior Oracle DBA

> IIRC Peoplesoft is a so-called 'Database Agnostic' application, and
> requires RBO.
>
> Apart from that Database Agnostic applications usually only support
> the Worst Common Denominator of versions supported.
>

That doesn't actually answer my question....

If you read my post again you'll see that I am actually concerned about
the CBO's ability to adequately optimize SQL involving subqueries. I
suspect that most applications out there (whether DB agnostic or not)
make some use of subqueries.

I have some doubts about the CBO's accuracy for subquery plans. I am
having to hint a large amount of SQL to avoid these inaccuracies. What
I am asking is whether things have improved in 10g.

Matt

mccmx@hotmail.com wrote:
> > IIRC Peoplesoft is a so-called 'Database Agnostic' application, and
> > requires RBO.
> >
> > Apart from that Database Agnostic applications usually only support
> > the Worst Common Denominator of versions supported.
> >
>
> That doesn't actually answer my question....
>
> If you read my post again you'll see that I am actually concerned about
> the CBO's ability to adequately optimize SQL involving subqueries. I
> suspect that most applications out there (whether DB agnostic or not)
> make some use of subqueries.
>
> I have some doubts about the CBO's accuracy for subquery plans. I am
> having to hint a large amount of SQL to avoid these inaccuracies. What
> I am asking is whether things have improved in 10g.
>

I think the point is that most people are not having problems with CBO
and subquery performance in 9i, or at least that is my experience. Most
performance problems, particularly those associated with database
agnostic applications, come from bad schema design and inefficiently
constructed queries rather than the CBO itself. Appropriate statistics
also play a big part, as can the optimizer init parameters. I believe
PS with CBO has these set to attempt to emulate RBO plans.

Without a real example and explain plan, and preferably a TKPROF
output, it is not really possible to tell.

--
MJB

> I think the point is that most people are not having problems with CBO
> and subquery performance in 9i, or at least that is my experience. Most
> performance problems, particularly those associated with database
> agnostic applications, come from bad schema design and inefficiently
> constructed queries rather than the CBO itself.

Agreed.

> Appropriate statistics
> also play a big part, as can the optimizer init parameters. I believe
> PS with CBO has these set to attempt to emulate RBO plans.
>

True. One problem we had was that AUTO_SAMPLE_SIZE has a bug which
grossly underestimates certain stats for skewed data.

> Without a real example and explain plan, and preferably a TKPROF
> output, it is not really possible to tell.

I wanted to avoid posting plans because I am seeing this behaviour for
a large number of queries. It was more of a general question.

Thanks for the feedback.

Matt

<mccmx@hotmail.com> wrote in message
news:1123676788.751389.225390@g44g2000cwa.googlegr oups.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

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

Thanks for the feedback...

Oracle support have just verified this. I have done some basic tests
under 10g which seem to indicate that the Optimizer is much better at
resolving subquery plans than in previous releases...

Matt