Object Statistics - Troubleshooting Oracle Performance - page 251

SUBPARTITION Q4_SP1 SUBPARTITION 1795 46 116 14:52:22
SUBPARTITION Q4_SP2 SUBPARTITION 2194 46 116 14:52:22
PARTITION Q4 PARTITION 3989 92 116 14:52:22
TABLE TABLE 17786 432 117 14:54:40

As the example shows, the object statistics associated to partitions (or subpartitions) that experience any
modification are considered stale. From version 12.1 onward, there is a preference, incremental_staleness ,
that lets you control this behavior. With the default value, NULL , the behavior is the same as in the previous version
(any modification makes a partition stale). If you set the value use_stale_percent , the object statistics associated
to partitions (or subpartitions) are considered stale only when the number of modifications crosses the threshold
set through the stale_percent preference. In addition, with the value use_locked_stats , you can define that object
statistics associated to partitions (or subpartitions) with locked statistics are never considered stale. Note that
use_stale_percent and use_locked_stats can be enabled at the same time. Here's an example:

dbms_stats.set_table_prefs(ownname => user,
tabname => 't',
pname => 'incremental_staleness',
pvalue => 'use_stale_percent, use_locked_stats');

Only in version 12.1 can the dbms_stats package create synompses on nonpartitioned tables (for that purpose,
the incremental_level preference has to be set to table ). As a result, only in version 12.1 can partition exchange take
advantage of incremental statistics.

■ The Oracle Support note How To Collect Statistics On Partitioned Table in 10g and 11g (1417133.1) provides a
list of the most important bugs and patches related to incremental statistics. Check the note to know whether the version
you're using requires particular attention, and check any further notes referenced by the first one.

Tip

Copying Statistics
In situations where partitions are frequently added and their content changes significantly over time, keeping a
representative set of partition level statistics requires very frequent gatherings. These frequent gatherings represent
significant overhead in terms of resource utilization. In addition, under normal conditions, it's not good to leave a
recently added partition without object statistics. Doing so leads to dynamic sampling, a feature covered in Chapter
9. To cope with such issues, the dbms_stats package, through the copy_table_stats procedure, provides the
functionality to copy object statistics from one partition or subpartition to another. Note that the copy takes care of
column statistics as well as dependent objects like subpartitions and local indexes.
The following command illustrates how to perform a copy (a full example is provided in the copy_table_stats.sql
script). The ownname and tabname parameters specify the table the command is executed on. The srcpartname and
dstpartname parameters specify the source and destination partition (or subpartition), respectively:

dbms_stats.copy_table_stats(ownname => user,
tabname => 't',
srcpartname => 'p_2014_q1',
dstpartname => 'p_2015_q1',
scale_factor => 1);