Re: Better cardinality estimate when dialing optimizer_features_enable back
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 25 Nov 2009 07:13:04 -0500
Few notes:
According to Alberto Del'Era's article
"NewDensity is not stored anywhere in the data dictionary,
but it is computed at query optimization time by the CBO
(note that density is still computed by dbms_stats using
the old formula, but then it is ignored by the CBO).
The NewDensity formula is based mainly on some
histogram-derived figures; using the same
names found in 10053 traces:"
These findings are from my QA system where I just did a gather_table_stats
on this table using only the default parameters for dbms_stats. In other
words, I only passed in ownname and tabname.
The value in user_tab_columns.density for letter_bin.prty_id
is 0.000430560539754607.
Take a look at the screenshot http://bit.ly/6AwFE6
The left-hand-side is OFE 10.2.0.4 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.
Additionally, look at the highlighted Histogram section-HtBal vs Frequency -
could that be a clue?
And finally, I uploaded a few examples of using the dynamic_sampling hint
using the simple-case query to
http://www.neilkodner.com/dynamic_sampling_results.txt
Looks like dynamic_sampling didn't kick in until level 6.
By the way, I appreciate everyone's interest in this. It made my morning
when I woke up and saw a few messages written in the middle of the night!
On Wed, Nov 25, 2009 at 2:25 AM, Randolf Geist
<info@xxxxxxxxxxxxxxxxxxxxx>wrote:
> > Would it help that I have optimizer_dynamic_setting at 2 at the
> > database level? I tried using a dynamic_sampling hint with a value of
> > 4; that didn't make a difference on the cardinality estimate.
>
> As pointed out by Robert - I wouldn't recommend changing the global level,
> for two reasons:
>
> - Potentially affects other queries that might change their execution plan
> not for the better
> - Dynamic Sampling gets only get activated under certain circumstances if
> statistics have been gathered
>
> There is a difference between the meaning of the level of Dynamic Sampling
> between Session/Cursor and Table level.
>
> For testing purposes I would recommend using it on Table level as already
> outlined - this means explicitly mentioning the table alias in the
> DYNAMIC_SAMPLING hint. If you don't specify the table alias this will be
> Dynamic Sampling on cursor level - level 4 very likely would not activate
> Dynamic Sampling at all with your statistics in place and the predicates
> used.
>
> So if you want to test this:
>
> - Use the DYNAMIC_SAMPLING hint with the table alias:
> DYNAMIC_SAMPLING(LETTER_BIN, <level>). This will activate Dynamic Sampling
> on the alias LETTER_BIN even with statistics in place to check for
> selectivity estimates
> - Check the EXPLAIN PLAN output -> In the "Notes" section you need to see
> "Dynamic Sampling used..."
>
> If it doesn't change the estimate, increase the level step by step, at last
> with level 10 the estimates need to get close to reality, obviously this is
> not reasonable, but it confirms that it works in general.
>
> For further details you can run a 10053 optimizer trace which will tell you
> the details about the Dynamic Sampling performed and the decisions of the
> optimizer to use or discard the results of the sampling.
>
> Robert: According to the provided information Dynamic Sampling is here not
> the culprit - the majority of time is spent on the actual execution, not the
> parse step.
>
> Regards,
> Randolf
>
> Oracle related stuff blog:
> http://oracle-randolf.blogspot.com/
>
> Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
> http://www.apress.com/book/view/1430226684
>
> http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
> ______________________________________________________
> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://movieflat.web.de
>
>
Other related posts:
