Oracles statistics for CBO ( Gathering statistics )

Optimizer statistics for use by the Cost Based Optimizer (CBO)

To achieve a quick delete and recreate of the statistics on an individual table and it's indexes
(adding column statistics for any skewed columns)

for delete

exec dbms_stats.delete_table_stats(ownname=>'user_name',tabname=>'table_name',cascade_indexes=>true);


exec dbms_stats.gather_table_stats(ownname=>'user_name',-
tabname=>'table_name',-
estimate_percent => 100,-
cascade=>true,-
method_opt=>'for all columns size skewonly');

generate statistics with as much statistical accuracy as possible,100% sample sizes are suggested since any reduction in sample size is always a concession to accuracy

100% samples are potentially time consuming and consideration needs to be made to fit the statistics gathering activities within the existing maintenance window

exec dbms_stats.gather_table_stats( -
ownname => ' Schema_name ', -
tabname => ' Table_name ', -
estimate_percent => 100, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE 1' );

method_opt

ESTIMATE_PERCENT: defaults:

9i : "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
11g : DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)


For cases where column data is known to be skewed and column statistics are known to be beneficial, Replace:
method_opt => 'FOR ALL COLUMNS SIZE 1'
with
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
or with
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY'