Re: Better cardinality estimate when dialing optimizer_features_enable back
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
- Date: Wed, 25 Nov 2009 15:09:47 +0100
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
