Re: Better cardinality estimate when dialing optimizer_features_enable back
Date: Wed, 25 Nov 2009 15:09:47 +0100
Message-Id: <947862699_at_web.de>
> 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-lReceived on Wed Nov 25 2009 - 08:09:47 CST