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