| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Keep CBO plan stable(plan stability)
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,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of zhu chao
Sent: Thursday, October 13, 2005 17:01
To: lex.de.haan_at_naturaljoin.nl
Cc: oracle list
Subject: 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.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 13 2005 - 10:29:01 CDT
 |
 |