Re: Keep CBO plan stable(plan stability)

I tried level 3. Oracle is 9.2.0.5 , and optimizer_feature_enable=9.2
SQL is not complicated, just like:
select a.col1, col2,b.col3,b.col4 from user_info a, users b where a.id
in (:b1,:b2,...,b25) and a.id=b.id;

There is index on a.id, b.id. Correct plan used a.id index scan and
then NL join a,b.
While wrong plan used a.id index scan, and then b.FTS, and then Hash join.

The key problem I don't understand is, why oracle changed the plan ,
when there is no statistics there. (from your comments, it should read
from segment header for the NLBK, others remaining unchanged). But
seems what the trace file reflect does not show the the correct number
of blocks.

Also I want to know:
If we do keep have the statistics for CBO, and after some time's
running we feel satisfied with current execution plan/database
performance, we don't analyze any table again (to keep the plan
stable, even with data distribution change, we want to reuse current
plan), will CBO keep the plan unchanged afterwords? of course other
things like optimizer related parameter does not change.
My manager want to use stored outline, while it is very troublesome to
maintain outline when there is a lot of database, I am trying to reach
this goal with minimum workload.

Thanks
On 10/13/05, Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx> wrote:
> depends on the version.
>
> in 9i, the default is dynamic sampling at level 1; in 10g, it is level 2
> (because in 10g the RBO is obsoleted.) which level did you try?
>
> how does the actual statement look like? do you have a complicated where
> clause? just trying to guess why the CBO would choose for bitmap
> conversion. sometimes that is quite smart, sometimes it isn't ...
>
> cheers,
>
> Lex.
>
--
Regards
Zhu Chao
www.cnoug.org
--
http://www.freelists.org/webpage/oracle-l

Other related posts: