There is the Oracle-supplied package DBMS_STATS
that will calculate statistics for a table (entire schema or the entire database), which is considered the replacement for the older (legacy) compute statistics
command for calculating the cost of a query. Using the DBMS_STATS
package will be your primary method for regulating statistics gathering on any database 10g and above, along with the startup initialization parameters of TIMED_STATISTICS
and STATISTICS_LEVEL
.
11g puts statistics gathering under the larger umbrella of Automatic Maintenance Tasks Management or AutoTask , which is used in conjunction with the resource manager so that jobs (like statistics gathering) are sure to complete successfully within the resource windows. These are the tasks automatically implemented:
Optimizer statistics gathering
Automatic Segment Advisor
SQL Tuning Advisor
The first set of automatic tasks is where we will start in this section-Optimizer Statistics Gathering, which calls the...