|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)
Part Number A96612-01
DBMS_STATS you can view and modify optimizer statistics gathered for database objects. The statistics can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if the
DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use
DBMS_STATS to gather statistics in parallel.
This chapter contains the following topics:
DBMS_STATS subprograms perform the following general functions:
Most of the
DBMS_STATS procedures include the three parameters
statid. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
stattab parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the
statown parameter is specified). You can create multiple tables with different
stattab identifiers to hold separate sets of statistics.
Additionally, you can maintain different sets of statistics within a single
stattab by using the
statid parameter, which avoids cluttering the user's schema.
GET procedures, if
stattab is not provided (that is,
NULL), then the operation works directly on the dictionary statistics; therefore, you do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if
stattab is not
NULL, then the
GET operation works on the specified user statistics table, and not the dictionary.
DBMS_STATS subprogram modifies or deletes the statistics for an object, all the dependent cursors are invalidated by default and corresponding statements are subject to recompilation next time so that the new statistics have immediate effects. This behavior can be altered with the
DBMS_STATS supports operations on user-defined statistics. When a domain index or column is associated with a statistics type (using the
associate statement), operations on the index or column manipulate user-defined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the
GATHER_INDEX_STATS interface invokes the user-defined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate user-defined statistics.
GET operations for user-defined statistics are also supported using a special version of the
GET interfaces for columns and indexes.
The following procedures in this package commit the current transaction, perform the operation, and then commit again:
Types for the minimum and maximum values and histogram endpoints include:
TYPE numarray IS VARRAY(256) OF NUMBER; TYPE datearray IS VARRAY(256) OF DATE; TYPE chararray IS VARRAY(256) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(256) OF RAW(2000); type StatRec is record ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY);
Types for listing stale tables include:
type ObjectElem is record ( ownname VARCHAR2(30), -- owner objtype VARCHAR2(6), -- 'TABLE' or 'INDEX' objname VARCHAR2(30), -- table/index partname VARCHAR2(30), -- partition subpartname VARCHAR2(30), -- subpartition confidence NUMBER); -- not used type ObjectTab is TABLE of ObjectElem;
Use the following constant to indicate that auto-sample size algorithms should be used:
The constant used to determine the system default degree of parallelism, based on the initialization parameters, is:
Use the following procedures to store and retrieve individual column-related, index-related, and table-related statistics:
In the special versions of the
SET_*_STATS procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or external statistics table:
The user-defined statistics and the corresponding statistics type are inserted into the
USTATS$ dictionary table. You can specify user-defined statistics without specifying the statistics type name.
The special versions of the
GET_*_STATS procedures return user-defined statistics and the statistics type owner and name as
OUT arguments corresponding to the schema object specified. If user-defined statistics are not collected,
NULL values are returned.
DELETE_COLUMN_STATS DELETE_INDEX_STATS DELETE_SYSTEM_STATS DELETE_TABLE_STATS DELETE_SCHEMA_STATS DELETE_DATABASE_STATS
DELETE_* procedures delete user-defined statistics and the standard statistics for the given schema object.
Use the following procedures to transfer statistics from the dictionary to a user stat table (
export_*) and from a user stat table to the dictionary (
CREATE_STAT_TABLE can hold user-defined statistics and the statistics type object number.
EXPORT_COLUMN_STATS EXPORT_INDEX_STATS EXPORT_SYSTEM_STATS EXPORT_TABLE_STATS EXPORT_SCHEMA_STATS EXPORT_DATABASE_STATS IMPORT_COLUMN_STATS IMPORT_INDEX_STATS IMPORT_SYSTEM_STATS IMPORT_TABLE_STATS IMPORT_SCHEMA_STATS IMPORT_DATABASE_STATS
IMPORT_* procedures retrieve statistics, including user-defined statistics, from the
stattab table and store them in the dictionary. Because the
GET_*_STATS interfaces are supported for user-defined statistics, user-defined statistics can be copied to another database using this interface.
Use the following procedures to gather certain classes of optimizer statistics, with possible performance improvements over the
GATHER_* procedures also collects user-defined statistics for columns and domain indexes.
statid parameters instruct the package to back up current statistics in the specified table before gathering new statistics.
Oracle also provides the following procedure for generating statistics for derived objects when you have sufficient statistics on related objects: