Re: Better cardinality estimate when dialing optimizer_features_enable back

From: Randolf Geist <>
Date: Wed, 25 Nov 2009 13:39:26 +0100
Message-Id: <>

> The value in user_tab_columns.density for letter_bin.prty_id is 0.
> 000430560539754607.
> Take a look at the screenshot
> The left-hand-side is OFE and shows density used as 4.0482e-
> 06.
> Compare that with the ofe 10.1.0 version on the right and you'll see
> it uses 4.3056e-04, which is stored in the data dictionary.
> Having said that, I'm not sure what to set the density of the column
> to-I'd appreciate some help with this.

As I said previously - you should set it to the value already shown in the dictionary for DENSITY which would be in this case the 4.3056e-04. The other value 4.0482e-06 is the NewDensity that is not visible from the dictionary.

Setting it to the value should revert to the previous handling of density - picking up the one from the dictionary.

Of course you could set it to different values then - if it helps to come up with a reasonable estimate for most of your queries.

> Additionally, look at the highlighted Histogram section-HtBal vs
> Frequency - could that be a clue?

I think is is a side effect of the different optimizer code paths.

> And finally, I uploaded a few examples of using the dynamic_sampling
> hint using the simple-case query to
> Looks like dynamic_sampling didn't kick in until level 6.

Possibly the sampling results are rejected with lower sampling levels - there are some sanity checks applied. This can be seen from the 10053 optimizer trace file.

It would be interesting to see the EXPLAIN PLAN results with more complex queries - in particular those that used an suboptimal plan so far.


Oracle related stuff blog:

Co-author of the forthcoming "OakTable Expert Oracle Practices" book:

Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.!
Received on Wed Nov 25 2009 - 06:39:26 CST

Original text of this message