Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Keep CBO plan stable(plan stability)

RE: Keep CBO plan stable(plan stability)

From: Lex de Haan <>
Date: Thu, 13 Oct 2005 17:26:41 +0200
Message-Id: <>

so it is the IN-list cost versus the estimated costs of a full table scan that matter.
if the problem is caused by the change from NL to HASH join, a simple USE_NL hint would be sufficient.
in 9i, you can also use the (undocumented) hints CARDINALITY and SELECTIVITY to tell the optimizer
how many rows to expect and how selective the WHERE clause is, to overrule the (apparently wrong) assumptions.

about your last question: the CBO is quite "intelligent" and therefore unpredictable -- unless you specify a sufficient set of hints to force a ceertain behavior. by the way, a stored outline is nothing else but a set of hints :-)

kind regards,


Steve Adams Seminar

-----Original Message-----

From: [] On Behalf Of zhu chao
Sent: Thursday, October 13, 2005 17:01
Cc: oracle list
Subject: Re: Keep CBO plan stable(plan stability)

I tried level 3. Oracle is , 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 in (:b1,:b2,...,b25) and;

There is index on, Correct plan used index scan and then NL join a,b.
While wrong plan used 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.

-- Received on Thu Oct 13 2005 - 10:29:01 CDT

Original text of this message