Re: Better cardinality estimate when dialing optimizer_features_enable back

http://bit.ly/75oNst (screenshot)

Could this have been a function of sample size changing? Could parallel have anything to do with it?

On Wed, Nov 25, 2009 at 9:09 AM, Randolf Geist <info_at_sqltools-plusplus.org>wrote:

> > Is this actually progress? I now get a cardinality estimate of 1300
> > regardless of how many occurrences a prty_id is actually represented
> > in letter_bin. For instance, I produced a list of prty_ids that are
> > in letter_bin 12 times, all 12 received cardinality estimates of 1300.
> > Did setting density invalidate my statistics?
> >
> > Truthfully, I have not tested the results against prty_ids with low
> > letter_bin counts.
>
> This is expected behaviour: All you have changed is how unpopular values
> are treated - since almost every value in your histogram is unpopular this
> estimate is going to apply to most of the values.
>
> I would expect you see exactly the same with OFE set to 10.1 without
> manipulating the DENSITY of the column.
>
> > A few follow-up issues:
> > 1-do I need to re-analyze my table or index after setting density?
>
> No - see above and below
>
> > 1a-will this density value get wiped out next time I gather
> > statistics?
>
> Yes, that is an issue - you probably want to lock the statistics so that
> the default gather job - if enabled - doesn't get in your way and re-gather
> manually the statistics adding an extra step that sets the DENSITY to the
> value present in the dictionary after gathering (simply GET_COLUMN_STATS +
> SET_COLUMN_STATS).
>
> > 2-Is there a certain way I should be gathering statistics? I've been
> > subscribing to the 'leave it to the defaults' theory as much as I can.
>
> I doubt that there is a setting that will help you with this issue without
> manual intervention since the Oracle histogram is simply not capable of the
> granularity required for your case.
>
> Note that the DYNAMIC_SAMPLING is still another option - adding overhead
> for each parse which corresponds probably to an execute in your particular
> case - every statement of that batch job very likely needs to get full
> parsed.
>
> As you've found out, only at higher levels the sampling result is accepted
> by the optimizer - however the overhead is not necessarily an issue. Just an
> option to keep in mind which might help if you find out that the potential
> overestimation now taking place poses again issues.
>
> 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
>
>

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