Sampling in Statistics Gathering

Gathering statistics on any object involves a full table scan and sorts which uses more resources. To reduce the resource spent for gathering statistics use sampling.

The ESTIMATE_PERCENT attribute of DBMS_STATS package is used to set the sample size. Oracle recommends to set the value to be AUTO_SAMPLE_SIZE. This value lets oracle to decide the best sample size.

SQL> DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', DBMS_STATS.AUTO_SAMPLE_SIZE);

Thanks