Database Reference
In-Depth Information
SUBPARTITION_NAME NUM_DISTINCT GLOBAL_STATS
----------------- ------------ ------------
Q1_SP1
20 YES
Q1_SP2
28 YES
Q1_SP3
25 YES
Q1_SP4
27 YES
■
Object statistics at the table/index level can only be derived from the underlying partitions if all of those
underlying partitions have object statistics in place. The same is also true for deriving partition statistics from subpartition
statistics. in addition, be aware that the dbms_stats package doesn't replace global statistics with derived statistics. both
cases can be reproduced with the
global_stats.sql
script.
Caution
In summary, global statistics are more accurate than derived statistics, but require more time and resources to
be gathered. Derived statistics might sometimes be enough. In practice, therefore, for big tables it's important to find
a good balance between the required accuracy and the time and resources needed to achieve it. For this reason, the
next two sections describe techniques that are available to manage object statistics for tables large enough to preclude
the repeated gathering of full, global statistics.
Incremental Statistics
As discussed in the preceding section, the ability to gather global statistics comes with pros and cons. The main pro is
the accuracy of the object statistics at the table level and, if subartitioning is used, at the partition level. The main con
is the time and resources that are needed to gather them.
The goal of incremental statistics is to offer the same accuracy by lowering the time and resources required to
gather object statistics. How is that possible? The key idea is to leverage additional information (called synopses)
stored in the data dictionary during the gathering of object statistics at the partition level, to accurately derive object
statistics at the table level.
The following requirements have to be fulfilled to take advantage of incremental statistics:
•
You're running version 11.1 or later.
incremental
preference is set to
TRUE
(the default is
FALSE
) :
•
For the table being processed, the
dbms_stats.set_table_prefs(ownname => user,
tabname => 't',
pname => 'incremental',
pvalue => 'TRUE');
publish
preference is set to
TRUE
(this is the default value).
•
For the table being processed, the
estimate_percent
parameter is set to
dbms_stats.auto_sample_size
(this is the default value).
•
For the table being processed, the
sysaux
tablespace.
The gathering itself is performed as usual—for example, through a call to the
gather_table_stats
procedure of the
dbms_stats
package. The only thing to be careful about is that, to take advantage of incremental statistics, synopses need
to be present at the partition level. As a result, after setting the
incremental
preference, you have to gather new object
•
Additional space is available in the
