RE: Plan stability with rule based optimzier
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 12 Feb 2005 13:42:01 -0800
>sorry -- I was trying to say two things at the same time.
>The CHOOSE hint only kicks in CBO if statistics are available,
>because otherwise it would default back to RULE, making the
>hint useless.
>Therefore I assumed the presence of object statistics.
>So if the intent is to deviate from RULE behavior for a specific SQL
>statement,
>using ALL_ROWS or FIRST_ROWS(n) is the preferred method.
I hate to disagree, but it is not be entirely true that CHOOSE kicks in CBO
only if Stats are available. As per my (limited) understanding, the CBO is
"forced" whenever the following operations or objects are involved,
regardless of OPTIMIZER_MODE. (Cut-n-paste from 9iR2 Tuning guide, ch 1.
n Partitioned tables and indexes
n Index-organized tables
n Reverse key indexes
n Function-based indexes
n SAMPLE clauses in a SELECT statement
n Parallel query and parallel DML
n Star transformations and star joins
n Extensible optimizer
n Query rewrite with materialized views
n Enterprise Manager progress meter
n Hash joins
n Bitmap indexes and bitmap join indexes
n Index skip scans
If these operations force the CBO and Stats are not present, then it uses
the default shown below (Tuning guide, ch 3)
Table Statistic Default Value Used by Optimizer
Cardinality num_of_blocks * (block_size - cache_layer) /
avg_row_len
Average row length 100 bytes
Number of blocks ?? (I think it should be able to pick this
up from TAB$)
Remote cardinality 2000 rows
Remote average row length - 100 bytes
There is a similar list for Indexes.
And since the OP did not mention whether this is 9i or 8i, we cannot rely on
Dynamic Sampling to be present or to collect missing stats. I believe it is
purely for this "missing stats" and the need for such operations that
DynSampling has been introduced. Don't get me wrong - I am willing to be
corrected on this!
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Fear connects you to the Negative, but Faith connects you to the Positive! I
Jn 4:18
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
