Those who have used the DBMS_STATS API will know that you can specify the proportion of rows the database will sample to calculate optimizer statistics. The parameter ESTIMATE_PERCENT controls this.
Oracle’s collateral recommends that you use the default ESTIMATE_PERCENT. In other words, leave the parameter unset (if you have not overridden the default) or explicitly set it to DBMS_STATS.AUTO_SAMPLE_SIZE.
Nevertheless, I have heard that some people use a value of 100 to sample all rows. I can understand the motivation to do this because, after all, what is better than reading all rows and checking all values? The first thing to realize is that AUTO_SAMPLE_SIZE reads all rows too! The clever part is that it optimizes how the number of distinct values (NDV) is calculated. It uses an algorithm that avoids the need to remember or sort a list of specific column values (which can otherwise be a considerable overhead if the number of distinct values is high). The auto algorithm saves both time and system resources. Yes, the resulting NDV is an approximation, but the accuracy is very high.
There are other reasons to use the default setting:
- Hybrid and top frequency histograms require you to use auto sample size. If you don’t, the database will create less accurate height-balanced histograms.
- Incremental statistics gathering requires auto sample size.
In summary: you should use auto sample size unless you have a very specific reason not to.
More information on auto sample size can be found here.