How to collect Histograms to help the optimizer to make better decisions

Histograms allow Oracle to make much better performance decisions. Another use for histograms, referred to as “table order join” histograms, is to help Oracle decide the order in which tables will be joined.  This helps the CBO know the size of the result sets or “cardinality” to properly determine the correct order in which to do joins.

To collect histograms for a column, a command similar to the following could be used:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(user, ‘ACCOUNTS’, method_opt => ‘for columns type 5′)