The 10g default "For all columns size auto"

It is wasteful to create a histogram on a column that is never referenced by a SQL statement.  The default value of  "for all columns size auto" is the Oracle default and this option will analyze histograms on what the optimizer considers to be "important columns". 

The optimizer does this by examining your current workload when you run the dbms_stats command, interrogating all current SQL in the library cache to see which columns might benefit from a histogram.

Oracle column histograms are important for several reasons:

• The CBO needs information about the result set size from multi-table joins to determine the optimal join order for tables.
   
• Oracle needs to know about heavily skewed column values when deciding about whether to use an index vs. a full-table scan.

Here are details about the new automatic histogram generation features in r2:

The method_opt=?SKEWONLY?  dbms_stats Option

The first is the ?skewonly? option which very time-intensive because it examines the distribution of values for every column within every index.  If dbms_stats discovers an index whose columns are unevenly distributed, it will create histograms for that index to aid the cost-based SQL optimizer in making a decision about index vs. full-table scan access.  For example, if an index has one column that is in 50% of the rows, a full-table scan is faster than and index scan to retrieve these rows.

Histograms are also used with SQL that has bind variables and SQL with cursor_sharing enabled.  In these cases, the CBO determines if the column value could affect the execution plan, and if so, replaced the bind variable with a literal and performs a hard parse.