Some people look at Statistics Gathering as taking vitamins: nice to have. And it’s wrong.
You shouldn’t base your tuning strategy on dynamic sampling alone, or luck. Some people freak out because once they generated stats for some system and it all went south. That’s the same as saying that because cars crash and people get killed we shouldn’t drive at all.
You have to use stats. Period.
So my small contribution to make your life easier is to give 5 tips if you are using Oracle 11g:
1) Run all queries against empty tables to populate column usage (histograms will be gathered based on this column usage).
2) Run stats after data has been loaded but before indexes are created (the creation of the index gathers stats on them automatically).
3) Find (business) correlations between columns and inform the optimizer about these correlations by the means of creating Column Groups. Also use this technique instead of function-based indexes.
4) Always gather stats with AUTO_SAMPLE_SIZE so the new stats gathering algorithm of Oracle 11g can kick in.
5) If you use partition tables you *must* use synopsis so the global stats can be derived much much much faster in an incremental fashion.
I guess 1, 2 and 4 don’t offer any doubts.
Regarding 3 and 5 here’s the usual techie yada yada:
3) Column Groups are created by the means of Extended Optimizer Statistics a new procedure of the DBMS_STATS package: CREATE_EXTENDED_STATS.
See an example in this very good article.
5) Synopsis are created when you enable incremental stats at the table or system level:
table level: SQL> exec DBMS_STATS.SET_TABLE_PREFS(‘SCOTT’,'TABLE_XYZ’,'INCREMENTAL”TRUE’);
system level: SQL> exec DBMS_STATS.SET_GLOBAL_PREFS(‘INCREMENTAL”TRUE’);
This will avoid rescaning partitions that haven’t changed since the last stats generation, making it faster.
So remember: ASICS it’s a matter of survival, not an option.