Preferences for Gathering Statistics
The DBMS_STATS.GATHER_*_STATS procedures can be called at various levels to gather statistics for an entire database or for individual objects such as tables. When the
GATHER_*_STATS procedures are called, several of the parameters are often allowed to default.
The supplied defaults work well for most of the objects in the database, but for some objects or schemas the defaults need to be changed. Instead of running manual jobs for each of these objects, Oracle Database 11g allows you to set values (called preferences) for individual objects, schemas, or databases, or to change the default values with the global-level command.
Preferences for Gathering Statistics (continued)
When the various gather procedures execute, they retrieve the object-level preferences that were set for each object. You can view the object-level preferences in the DBA_TAB_STAT_PREFS view. Any preferences that are not set at the object level will be set to the global-level preferences. You can see the global preferences by calling the DBMS_STATS.GET_PREFS procedure for each
• INCREMENTAL is used to gather global statistics on partitioned tables in an incremental way.• METHOD_OPT determines the columns and histogram parameters that are used to gather column statistics.
• GRANULARITY determines the granularity of statistics to collect (which is pertinent only if the table is partitioned).
Oracle Database 11g: Administration Workshop I 12 - 12