RE: Plan stability with rule based optimzier

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Sat, 12 Feb 2005 13:42:01 -0800
Message-ID: <960A9B249C99994AA3C7FCA016A0DA89CB792A@ussccem08.corp.hds.com>

>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 **

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

Received on Sat Feb 12 2005 - 16:45:01 CST