I'll try to cover all these things in my Dynamic Sampling series on "AllThingsOracle.com".
Hope this helps,
Nikolay Savvinov wrote:Hi Nikolay,
However, assuming clustering_factor = 0 looks wrong to me. Wouldn't assuming clustering_factor = num_table_blocks be more reasonable?
Randolf Geist wrote:I don't want to contradict myself too often here and don't want to beat a dead horse either, but after having a closer look at it the observed behaviour could be called something bug-like. The point here is that the costing is handled inconsistently in the case where table stats are missing but index stats have been gathered.Randolf Geist wrote:Thinking about it again I'm not so sure this can be called a bug - after all the clustering factor of the index in this case is 0, so the resulting cost of the table access according to the formula will be 0...
In this case it looks like the optimizer simply (more or less) ignores the cost estimate for the table access (a bug I would say which I could reproduce on 220.127.116.11 and 18.104.22.168).
This is clearly an inconsistent behaviour because by default Dynamic Sampling is not supposed to override existing statistics. This is only to be done when the DYNAMIC_SAMPLING_EST_CDN hint is explicitly added for a particular table.
** Dynamic sampling updated index stats.: IND_T6_C2, blocks=244