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.
Column Groups are created by the means of Extended Optimizer Statistics a new procedure of the DBMS_STATS package: CREATE_EXTENDED_STATS.
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 faster in an incremental fashion.
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.