Re: Better cardinality estimate when dialing optimizer_features_enable back

From: Neil Kodner <>
Date: Wed, 25 Nov 2009 10:29:30 -0500
Message-ID: <>

Well look what I found while poking around sys.wri$_optstat_histhead_history (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 <>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 +
> > 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:
> Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
> ______________________________________________________
> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
> Jetzt freischalten unter

Received on Wed Nov 25 2009 - 09:29:30 CST

Original text of this message