Re: CBO Subquery Limitations

mccmx_at_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