Automating Statistics Sample Size with dbms_stats
The higher the quality of the schema statistics will result in
higher probability that CBO will choose the optimal execution
plan. Unfortunately, doing a complete analysis of every rows of
every table in a schema could take days and most shops must
sample their database to get CBO statistics.
The goal of estimating the sample size is to take a large enough
sample of the database to provide top quality data for the CBO
while not adversely impacting server resources. Now that how
the dbms_stats option
works has been introduced, it will be useful see how to specify
an adequate sample size for
dbms_stats.
In earlier releases, the DBA had to guess what percentage of the
database provided the best sample size and sometimes under
analyzed the schema. Starting with Oracle9i Database, the
estimate_percent argument was added to
dbms_stats to allow Oracle to
automatically estimate the best percentage of a segment to
sample when gathering statistics. A sample invocation follows
below:
estimate_percent => dbms_stats.auto_sample_size
After collecting statistics with an automatic sample size, the
accuracy of the automatic statistics sampling can be verified by
looking at the sample_size
column on any of
these data dictionary views:
§
dba_object_tables
§
dba_tab_col_statistics
§
dba_tab_partitions
§
dba_tab_subpartitions
§
dba_part_col_statistics
§
dba_subpart_col_statistics
§
dba_tables
§
dba_tab_cols
§
dba_tab_columns
§
dba_all_tables
§
dba_indexes
§
dba_ind_partitions
§
dba_ind_subpartitions
In practice, the
auto_sample_size option of
dbms_stats generally
chooses a sample_size
from five to 20
percent when using automatic sampling, depending on the size of
the tables and the distribution of column values.
Too small a sample can impact the CBO, so one should always make
sure to take a statistically significant sample size for every
object in the schema. The next section will introduce some
methods DBA’s use to ensure that their SQL optimizer always has
great schema statistics.
SEE CODE DEPOT FOR FULL SCRIPTS
