Re: Poorly performing query and 10053 trace

Yes, with optimizer_goal=choose:

• when ALL tables in a query have no statistics, or vice versa - if NONE of the tables in a query has statistics => RBO
• when AT LEAST ONE table in the query has statistics => CBO with statistics defaults for the tables where there are none. Since some of these statistics are derived from the segment size this can lead to plan changes due to table growth even if the gathered statistics on those tables which have any do not change, nor any of the other optimizer parameters.

At 05:48 PM 1/12/2006, Boris Dali wrote:

>Wasn't it the opposite - if at least one table
>analyzed in a join, than the cbo kicks in and made-up
>stats used for non-analyzed tables?
>
>
> > >> this query accesses 2 analyzed tables
> > >> and 3 unanalyzed tables
> > If I recall correctly in 817 if some of the tables
> > have no statistics
> > Oracle uses RULE based optimizer.
> > For RULE based optimizer the order in which indexes
> > have been created
> > some times play trumendios role.
> > Can you confirm the that query parsed in the RULE
> > mode?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l