DBMS_STATS

http://www.dbspecialists.com/files/presentations/dbms_stats.html

The author has given nice ways to verify what are "good" statistics meaning we should verify after stats what the distinct number of values for columns show versus their actual distinct numbers that we can determine by

"select count (distinct col_name) from table_name;"

As close these 2 values are (actual queried result versus after stats results) 

will determine how accurate or not the stats collection is. 

There are 2 queries given that can be utilized to see the distinct number of values as computed by stats collection.

Example (for indexed columns)

 
select
  ind.table_name,
  ind.uniqueness,
  col.index_name,
  col.column_name,
  ind.distinct_keys,
  ind.sample_size
from
  ALL_ind_columns  col,
  ALL_INDEXES      ind
where
  ind.table_owner = 'TRACKSTAGE2_OWNER'
    and
  --ind.table_name in ('FILE_HISTORY','PROP_CAT') and
  col.index_owner = ind.owner 
    and
  col.index_name = ind.index_name
    and
  col.table_owner = ind.table_owner
    and
  col.table_name = ind.table_name
order by
  col.table_name,
  col.index_name,
  col.column_position; 

There are few parameters in the method to gather table stats and index stats, most crucial to performance tuning perspective being:

estimate_percent (in percentage 5 is to mean 5 percent of data to pick for sampling)

method_opt ('for all columns' or 'for all indexed columns' with a size followed by a integer or better is to give SKEWONLY Or AUTO) See below for explanation on these two.

 

block_sample (true/false. true means instead of using "rows" for estimate percent, like 5 meaning 5 percent of rows in the table, if block_sample is given true it picks up 5 percent of data blocks. It doesnt really seem to be faster and doesn't result in better accuracy so not much use of it..)

cascade (true means indexes of the corresponding table will also have their stats gathered)

What is SKEWONLY?

If we choose to say for all columns size Skewonly basically histograms will be created for columns that Oracle deems necessary for (those columns that have variance in kind of values).. however in 9i this had a bug that for primary key also it was creating histograms. This is intended to be able to help CBO to decide if a full/index scan is required for the table in question when such such column is in the join list.