Re: Keep CBO plan stable(plan stability)

you might want to throw in a DYNAMIC_SAMPLING hint, at level 2, and see what happens with the execution plans and the parsing time. at least, you will not need to gather any statistics...

by the way, note that query transformations take place before the optimizer is called -- that is, query transformations happen regardless whether you run RBO or CBO. another possible reason why RBO plans change. Another well-known reason for different RBO plans is the order in which certain indexes are created.

cheers,

Lex.

> Hi,
> Thanks Wolfang/Karen/Lex for your answers!
> Let me explain with more detail.
> The major application is still using Rule Based Optimizer, as you
> see our optimizer_mode is rule.
> For that specific SQL, As RBO inable to use in-list efficiently, we
> added the no_expand hint. it works fine in oracle 8.1.7 for several
> years. But after we deploy the application in oracle 9.2, it seems
> have some trouble. I am suggesting we analyze the underlying two
> tables, but as this is the key tables of our application,
> Management/Other DBA are very cautious to this change. Without the
> hint, oracle use concat, which make big use of shared pool. We don't
> like it.
>
> --The plan could change every time it is hard parsed.
> Could you please explain more about this? What kind of factor can
> lead to the plan change? The table size(from segment header, the NBLK?
> anything else?)

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