Re: Better cardinality estimate when dialing optimizer_features_enable back

> 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


Other related posts: