2006/7/17, Nirmalya Das <nirmalya@xxxxxxx>:
I thought the "stats" collection is one of the most important events to help CBO finding the best QEP... ... Is Column Histograms creating the trouble? Should I turn it off?
Since I didn't see someone replied to this question...
The answer is... it depends. Statistics is supposed to help CBO to make a more educated guess on optimal execution plan. Collecting aggregated stats causes loss of information even though CBO will probably be OK in most of cases unless your application does something really stupid. However, it's those rare cases when CBO makes a mistake - those are disastrous. If you don't know your application, data, and usage patterns than you might have to deal with each exceptional case separately.
Now, having this auto gather stats will give you the chance to either reproduce cases you fixed or create new ones (in this regard name this job auto mess stats). On the other hand disabling the job might cause troubles to lazy (or busy) DBA becuase (1) new objects won't be analized and expensive dynamic sampling will be used and (2) significant changes in data pattern won't be reflected in statistics possibly causing increase in real execution cost.
So unless you are very lazy or very busy and can't spend any time on this database, you should consider removing this job and setup new procedure to keep statistics valid. Note that if you want to have it done properly, you will need to really spend some time on your application to investigate data usage and change patterns. This might include removing all the histograms to start with and collect them only when it's required (one approach might be to fix certain rare problems). Prerequisite to your action should actually be some time to spend on education and quite a few people will support me if I suggest to grab Jonathan's book "Cost-Based Oracle Fundamantals". If you don't have access to the book, consider having a close look at Papers section on the website of Wolfgang Breitling who replied to you earlier in this thread.
-- Best regards, Alex Gorbachev
http://blog.oracloid.com -- //www.freelists.org/webpage/oracle-l
