Post-Install Database Tasks - Oracle Exadata Recipes: A Problem-Solution Approach - page 615

ESTIMATE_PERCENT option, DBMS_STATS.AUTO_SAMPLE_SIZE will determine the
appropriate block sample size to use when gathering statistics. With 11gR2, this often will
yield a low percentage as compared to previous Oracle versions, but in nearly all cases Oracle
will choose an appropriate sample size. In our experience, this is one of the better features
of statistics collection in 11g; with smaller estimate percentages, statistics gathering is more
efficient, has less overhead, and generally runs faster.

• For the

• For the

GRANULARITY option, AUTO will gather statistics on all dependent table partitions and
sub-partitions based on the partitioning type of the table.

• For the

METHOD_OPT option, FOR ALL COLUMNS SIZE AUTO will create histograms on the
appropriate columns, with the appropriate bucket sizes, based on the workload of your
database. It does so whether the column(s) are indexes or not and uses query predicate
information from SYS.COL_USAGE$ to determine which columns to build indexes for.

■ FOR ALL COLUMNS SIZE AUTO assumes your database has had a representative workload run. If not, the first
iteration of statistics collection may not build the correct histogram. When migrating to exadata, it is often considered
good practice to create empty tables, run a sample workload, gather statistics to build the proper histograms, and then
load data before opening the database to the users. this practice may or may not be practical for a number of reasons,
but if you can do it, it will ensure that accurate column statistics and histograms are accurate before users run production
queries on your database.

Note

STALE_PERCENT option, the recommended value of 10 means to re-gather statistics
when the percentage of block changes for a segment exceeds 10% that of the number of blocks
in the segment. DBMS_STATS tracks this by examining information from SYS.MON_MODS$ and
DBA_TAB_MODIFICATIONS .

• For the

NO_INVALIDATE option, DBMS_STATS.AUTO_INVALIDATE generally instructs DBMS_STATS
to invalidate dependent cursors, forcing re-parsing of a depending SQL cursors.

• For the

PUBLISH option, TRUE indicates to publish statistics immediately after collection. You
can choose to defer statistics publishing if you are sensitive to SQL plan changes by setting
PUBLISH to FALSE .

• For the

INCREMENTAL option, DBMS_STATS will only gather statistics on partitions that have
changed since the last statistics collection on the table or table's partition. Depending on
how you maintain your partitions, this can eliminate a great deal of unnecessary work during
statistics gathering by bypassing statistics collection for dormant partitions. For incremental
statistics functionality to work, you need to set PUBLISH to TRUE, GRANULARITY to AUTO , and
ESTIMATE_PERCENT to DBMS_STATS.AUTO_SAMPLE_SIZE .

• For the