Oracle 10g dynamic sampling helps CBO cardinality
estimates
June 18, 2005
Oracle professional
Jeff Moss has
published an article showing how Oracle10g dynamic sampling might
help in cases of missing histograms when using built-in functions on
date range scans, noting research by noted Oracle pr0fessional,
Wolfgang Breitling:
http://oramossoracle.blogspot.com/2005/11/scd2s-and-their-affect-on-cbo-part-ii.html
I wrote a while ago in a post
here that the CBO has no way
of knowing how many rows it will return for a given fixed date when
applied to the FROM_DATE / TO_DATE of an SCD2 table…well, as I said
I would in the article, I caught up with Wolfgang Breitling at the
UKOUG after he did a presentation on Histograms and was able to
discuss this problem with him further, whereupon he suggested that
using dynamic sampling at level 4 or above might allow the CBO to
determine the selectivity for combinations of FROM_DATE / TO_DATE in
the target SCD2 table…
This suggests that setting dynamic sampling to
level 4 may remove the tedium of identifying and maintaining
histograms for columns referenced in complex WHERE clause predicates
to estimate the cardinality returned from a table. This has
important ramifications for helping the CBO join tables together in
the fastest order.
Here is a portion of his excellent test and
evidence for Oracle 10g dynamic sampling in lieu of histograms:
jeff[28/5]@XE> SELECT
COUNT(1)
2 FROM jeff_dynamic_sampling
3 WHERE TO_DATE('15-JAN-2005','DD-MON-YYYY') BETWEEN from_date AND
NVL(to_date,TO_DATE('31-DEC-9999','DD-MON-YYYY'));
COUNT(1)
----------
13
1 row selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2444057389
--------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 13 3 (0) 00:00:01
1 SORT AGGREGATE 1 13
* 2 TABLE ACCESS FULL
JEFF_DYNAMIC_SAMPLING 1 13 3 (0) 00:00:01
|
