63
DBMS_STATS

DBMS_STATS provides a mechanism for you to 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. 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:

Using DBMS_STATS

The DBMS_STATS subprograms perform the following general functions:

Most of the DBMS_STATS procedures include the three parameters statown, stattab, and 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.

The 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). Users may create multiple tables with different stattab identifiers to hold separate sets of statistics.

Additionally, users can maintain different sets of statistics within a single stattab by using the statid parameter, which can help avoid cluttering the user's schema.

For all of the SET or GET procedures, if stattab is not provided (i.e., NULL), then the operation works directly on the dictionary statistics; therefore, users 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 SET or GET operation works on the specified user statistics table, and not the dictionary.

Most of the procedures in this package commit the current transaction, perform the operation, and then commit again. These include:

SET_*
DELETE_*
EXPORT_*
IMPORT_*
GATHER_*
*_STAT_TABLE

Types

Types for minimum/maximum values and histogram endpoints:

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:

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;

The constant used to indicate that auto-sample size algorithms should be used is:

AUTO_SAMPLE_SIZE CONSTANT NUMBER;

The constant used to determine the system default degree of parallelism, based on the initialization parameters, is:

DEFAULT_DEGREE CONSTANT NUMBER;

Setting or Getting Statistics

The following procedures enable the storage and retrieval of individual column-, index-, and table-related statistics:

PREPARE_COLUMN_VALUES
SET_COLUMN_STATS
SET_INDEX_STATS
SET_SYSTEM_STATS
SET_TABLE_STATS

CONVERT_RAW_VALUE
GET_COLUMN_STATS
GET_INDEX_STATS
GET_SYSTEM_STATS
GET_TABLE_STATS

DELETE_COLUMN_STATS
DELETE_INDEX_STATS
DELETE_SYSTEM_STATS
DELETE_TABLE_STATS
DELETE_SCHEMA_STATS 
DELETE_DATABASE_STATS

Gathering Optimizer Statistics

The following procedures enable the gathering of certain classes of optimizer statistics, with possible performance improvements over the ANALYZE command:

GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DATABASE_STATS
GATHER_SYSTEM_STATS

The statown, stattab, and statid parameters instruct the package to backup current statistics in the specified table before gathering new statistics.

Oracle also provides the following procedure for generating some statistics for derived objects when we have sufficient statistics on related objects:

GENERATE_STATS

Transferring Statistics

The following procedures enable the transference of statistics from the dictionary to a user stat table (export_*) and from a user stat table to the dictionary (import_*):

CREATE_STAT_TABLE
DROP_STAT_TABLE

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

Summary of DBMS_STATS Subprograms

Table 63-1 DBMS_STATS Subprograms

Subprogram	Description
"PREPARE_COLUMN_VALUES Procedure"	Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage via `SET_COLUMN_STATS`.
"SET_COLUMN_STATS Procedure"	Sets column-related information.
"SET_INDEX_STATS Procedure"	Sets index-related information.
"SET_SYSTEM_STATS Procedure"	Sets system statistics.
"SET_TABLE_STATS Procedure"	Sets table-related information.
"CONVERT_RAW_VALUE Procedure"	Convert the internal representation of a minimum or maximum value into a datatype-specific value.
"GET_COLUMN_STATS Procedure"	Gets all column-related information.
"GET_INDEX_STATS Procedure"	Gets all index-related information.
"GET_SYSTEM_STATS Procedure"	Gets system statistics from stattab, or from the dictionary if stattab is null.
"GET_TABLE_STATS Procedure"	Gets all table-related information.
"DELETE_COLUMN_STATS Procedure"	Deletes column-related statistics.
"DELETE_INDEX_STATS Procedure"	Deletes index-related statistics.
"DELETE_SYSTEM_STATS Procedure"	Deletes system statistics.
"DELETE_TABLE_STATS Procedure"	Deletes table-related statistics.
"DELETE_SCHEMA_STATS Procedure"	Deletes schema-related statistics.
"DELETE_DATABASE_STATS Procedure"	Deletes statistics for the entire database.
"CREATE_STAT_TABLE Procedure"	Creates a table with name `stattab` in ownname's schema which is capable of holding statistics.
"DROP_STAT_TABLE Procedure"	Drops a user stat table created by `CREATE_STAT_TABLE`.
"EXPORT_COLUMN_STATS Procedure"	Retrieves statistics for a particular column and stores them in the user stat table identified by `stattab`.
"EXPORT_INDEX_STATS Procedure"	Retrieves statistics for a particular index and stores them in the user stat table identified by `stattab`.
"EXPORT_SYSTEM_STATS Procedure"	Retrieves system statistics and stores them in the user stat table.
"EXPORT_TABLE_STATS Procedure"	Retrieves statistics for a particular table and stores them in the user stat table.
"EXPORT_SCHEMA_STATS Procedure"	Retrieves statistics for all objects in the schema identified by ownname and stores them in the user stat table identified by `stattab`.
"EXPORT_DATABASE_STATS Procedure"	Retrieves statistics for all objects in the database and stores them in the user stat table identified by `statown`.`stattab`.
"IMPORT_COLUMN_STATS Procedure"	Retrieves statistics for a particular column from the user stat table identified by `stattab` and stores them in the dictionary.
"IMPORT_INDEX_STATS Procedure"	Retrieves statistics for a particular index from the user stat table identified by `stattab` and stores them in the dictionary.
"IMPORT_SYSTEM_STATS Procedure"	Retrieves system statistics from the user stat table and stores them in the dictionary
"IMPORT_TABLE_STATS Procedure"	Retrieves statistics for a particular table from the user stat table identified by `stattab` and stores them in the dictionary.
"IMPORT_SCHEMA_STATS Procedure"	Retrieves statistics for all objects in the schema identified by `ownname` from the user stat table and stores them in the dictionary.
"IMPORT_DATABASE_STATS Procedure"	Retrieves statistics for all objects in the database from the user stat table and stores them in the dictionary.
"GATHER_INDEX_STATS Procedure"	Gathers index statistics.
"GATHER_TABLE_STATS Procedure"	Gathers table and column (and index) statistics.
"GATHER_SCHEMA_STATS Procedure"	Gathers statistics for all objects in a schema.
"GATHER_DATABASE_STATS Procedure"	Gathers statistics for all objects in the database.
"GATHER_SYSTEM_STATS Procedure"	Gathers system statistics.
"GENERATE_STATS Procedure"	Generates object statistics from previously collected statistics of related objects.
"FLUSH_SCHEMA_MONITORING_INFO Procedure"	Flushes in-memory monitoring information for the tables in the specified schema in the dictionary.
"FLUSH_DATABASE_MONITORING_INFO Procedure"	Flushes in-memory monitoring information for all the tables to the dictionary.
"ALTER_SCHEMA_TABLE_MONITORING Procedure"	Enables or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support.
"ALTER_DATABASE_TABLE_MONITORING Procedure"	Enables or disables the S`DML monitoring feature of all the tables in the database, except for snapshot logs and the tables, which monitoring does not support.

63 DBMS_STATS