Well look what I found while poking around sys.wri$_optstat_histhead_history 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@xxxxxxxxxxxxxxxxxxxxx>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 > >