70
DBMS_STATS

With 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:

Using DBMS_STATS

The DBMS_STATS subprograms perform the following general functions:

Set or get statistics
Transfer statistics
Gather optimizer statistics

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). 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.

For the SET and 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 SET or GET operation works on the specified user statistics table, and not the dictionary.

When a 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 no_invalidate argument.

User-Defined Statistics

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.

SET and GET operations for user-defined statistics are also supported using a special version of the SET and GET interfaces for columns and indexes.

The following procedures in this package commit the current transaction, perform the operation, and then commit again:

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

Types

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:

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

Use the following procedures to store and retrieve individual column-related, index-related, and table-related statistics:

PREPARE_COLUMN_VALUES
SET_COLUMN_STATS
SET_INDEX_STATS
SET_SYSTEM_STATS
SET_TABLE_STATS

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:

User-defined statistics (extstats)
The statistics type schema name (statsschema)
The statistics type name (statsname)

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.

CONVERT_RAW_VALUE
GET_COLUMN_STATS
GET_INDEX_STATS
GET_SYSTEM_STATS
GET_TABLE_STATS

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

The DELETE_* procedures delete user-defined statistics and the standard statistics for the given schema object.

Transferring Statistics

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 (import_*):

CREATE_STAT_TABLE
DROP_STAT_TABLE

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

The IMPORT_* procedures retrieve statistics, including user-defined statistics, from the stattab table and store them in the dictionary. Because the SET_*_STATS and GET_*_STATS interfaces are supported for user-defined statistics, user-defined statistics can be copied to another database using this interface.

Gathering Optimizer Statistics

Use the following procedures to gather 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 GATHER_* procedures also collects user-defined statistics for columns and domain indexes.

The statown, stattab, and 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:

GENERATE_STATS

70 DBMS_STATS