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.
The preferences specify the parameters that are given to the gather
procedures. The SET_*_PREFS procedures create preference values for any object
that is not owned by SYS or SYSTEM. The expected use is that the DBA will set the
global preferences for any parameters that should be database-wide. These will
be applied for any parameter that is allowed to default.
The SET_DATATBASE_PREFS procedure iterates over all the tables and
schemas in the database setting the specified preference. SET_SCHEMA_PREFS
iterates over the tables in the specified schema. SET_TABLE_PREFS sets the
preference value for a single table.
All object preferences—whether set at the database, schema, or table
level—are held in a single table. Changing the preferences at the schema level
overwrites the preferences that were previously set at the table level.
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 preference.
You can set, get, delete, export, and import those
preferences at the table, schema, database, and global levels. The preference
values are expected to be set from global to table levels, applying the
preferences to the smallest group last.
Preferences in Oracle Database 11g:
• CASCADE determines whether
index statistics are collected as part of gathering table statistics.
• DEGREE sets the degree
of parallelism that is used for gathering statistics.
• PUBLISH is used to decide whether to
publish the statistics to the dictionary or store them in a private area. This
enables the DBA to validate the statistics before publishing them to the data
dictionary with the PUBLISH_PENDING_STATS procedure.
• STALE_PERCENT is used to determine the
threshold level at which an object is considered to have stale statistics. The
value is a percentage of the rows modified since the last statistics gathering.
The example changes the 10 percent default to 13 percent for SH.SALES only.
• 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).
• NO_INVALIDATE is used to determine
whether to invalidate cursors.
• ESTIMATE_PERCENT is used to determine
the number of rows to sample to obtain good statistics. It is a percentage of
the number of rows in the table.
Preferences may be deleted with the DBMS_STATS.DELETE_*_PREFS
procedures at the table, schema, and database levels. You can reset the global
preferences to the recommended values with the DBMS_STATS.RESET_PARAM_DEFAULTS
procedure.
