49
DBMS_STATS

DBMS_STATS provides a mechanism for you to view and modify optimizer statistics gathered for database objects.The statistics can reside in two different locations:

The dictionary.
A table created in the user's schema for this purpose.

Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.

This package also facilitates the gathering of some statistics in parallel. The package is divided into three main sections:

Using DBMS_STATS

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.

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;

Summary of Subprograms

Table 49-1 DBMS_STATS Package 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_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_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_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_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_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.
GENERATE_STATS procedure	Generates object statistics from previously collected statistics of related objects.

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_TABLE_STATS

CONVERT_RAW_VALUE
GET_COLUMN_STATS
GET_INDEX_STATS
GET_TABLE_STATS

DELETE_COLUMN_STATS
DELETE_INDEX_STATS
DELETE_TABLE_STATS
DELETE_SCHEMA_STATS 
DELETE_DATABASE_STATS

PREPARE_COLUMN_VALUES procedure

This procedure converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage via SET_COLUMN_STATS.

Syntax

DBMS_STATS.PREPARE_COLUMN_VALUES (
   srec     IN OUT StatRec, 
   charvals        CHARARRAY);

DBMS_STATS.PREPARE_COLUMN_VALUES (
   srec      IN OUT StatRec, 
   datevals         DATEARRAY);

DBMS_STATS.PREPARE_COLUMN_VALUES (
   srec     IN OUT StatRec, 
   numvals         NUMARRAY);

DBMS_STATS.PREPARE_COLUMN_VALUES ( 
   srec     IN OUT StatRec, 
   rawvals         RAWARRAY);

DBMS_STATS.PREPARE_COLUMN_VALUES_NVARCHAR (
   srec  IN OUT StatRec, 
   nvmin        NVARCHAR2, 
   nvmax        NVARCHAR2);

DBMS_STATS.PREPARE_COLUMN_VALUES_ROWID (
   srec  IN OUT StatRec, 
   rwmin        ROWID, 
   rwmax        ROWID);

Pragmas

pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
pragma restrict_references(prepare_column_values_nvarchar, WNDS, RNDS, WNPS, 
RNPS);
pragma restrict_references(prepare_column_values_rowid, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 49-2 PREPARE_COLUMN_VALUES Procedure Parameters

Parameter Description

Parameter	Description
srec.epc	Number of values specified in `charvals`, `datevals`, `numvals`, or `rawvals`. This value must be between 2 and 256, inclusive, and it should be set to `2` for procedures which do not allow histogram information (`nvarchar` and `rowid`). The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to `SET_COLUMN_STATS`.
srec.bkvals	If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in `charvals`, `datevals`, `numvals`, or `rawvals`. Otherwise, it is merely an output parameter, and it must be set to `NULL` when this procedure is called.

srec.epc

Number of values specified in charvals, datevals, numvals, or rawvals. This value must be between 2 and 256, inclusive, and it should be set to 2 for procedures which do not allow histogram information (nvarchar and rowid).

The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to SET_COLUMN_STATS.

srec.bkvals

If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in charvals, datevals, numvals, or rawvals. Otherwise, it is merely an output parameter, and it must be set to NULL when this procedure is called.

Datatype specific input parameters (one of the following):

charvals

The array of values when the column type is character-based. Up to the first 32 bytes of each string should be provided. Arrays must have between 2 and 256 entries, inclusive.

datevals

The array of values when the column type is date-based.

numvals

The array of values when the column type is numeric-based.

rawvals

The array of values when the column type is RAW. Up to the first 32 bytes of each strings should be provided.

nvmin, nvmax

The minimum and maximum values when the column type is national character set based (NLS). No histogram information can be provided for a column of this type.

rwmin, rwmax

The minimum and maximum values when the column type is rowid. No histogram information can be provided for a column of this type.

Output parameters

Table 49-3 PREPARE_COLUMN_VALUES Procedure Output Parameters

Parameter	Description
srec.minval	Internal representation of the minimum which is suitable for use in a call to `SET_COLUMN_STATS`.
srec.maxval	Internal representation of the maximum which is suitable for use in a call to `SET_COLUMN_STATS`.
srec.bkvals	Array suitable for use in a call to `SET_COLUMN_STATS`.
srec.novals	Array suitable for use in a call to `SET_COLUMN_STATS`.

Exceptions

ORA-20001: Invalid or inconsistent input values.

SET_COLUMN_STATS procedure

This procedure sets column-related information.

Syntax

DBMS_STATS.SET_COLUMN_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   colname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2 DEFAULT NULL, 
   statid   VARCHAR2 DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL,
   distcnt  NUMBER   DEFAULT NULL, 
   density  NUMBER   DEFAULT NULL,
   nullcnt  NUMBER   DEFAULT NULL, 
   srec     StatRec  DEFAULT NULL,
   avgclen  NUMBER   DEFAULT NULL, 
   flags    NUMBER   DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-4 SET_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition in which to store the statistics. If the table is partitioned and `partname` is `NULL`, then the statistics are stored at the global table level.
stattab	User stat table identifier describing where to store the statistics. If `stattab` is `NULL`, then the statistics are stored directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
distcnt	Number of distinct values.
density	Column density. If this value is `NULL` and if `distcnt` is not `NULL`, then density is derived from `distcnt`.
nullcnt	Number of `NULLs.`
srec	`StatRec` structure filled in by a call to `PREPARE_COLUMN_VALUES` or `GET_COLUMN_STATS`.
avgclen	Average length for the column (in bytes).
flags	For internal Oracle use (should be left as `NULL`).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent input values.

SET_INDEX_STATS procedure

This procedure sets index-related information.

Syntax

DBMS_STATS.SET_INDEX_STATS (
   ownname  VARCHAR2, 
   indname  VARCHAR2,
   partname VARCHAR2  DEFAULT NULL,
   stattab  VARCHAR2  DEFAULT NULL, 
   statid   VARCHAR2  DEFAULT NULL,
   numrows  NUMBER    DEFAULT NULL, 
   numlblks NUMBER    DEFAULT NULL,
   numdist  NUMBER    DEFAULT NULL, 
   avglblk  NUMBER    DEFAULT NULL,
   avgdblk  NUMBER    DEFAULT NULL, 
   clstfct  NUMBER    DEFAULT NULL,
   indlevel NUMBER    DEFAULT NULL, 
   flags    NUMBER    DEFAULT NULL,
   statown  VARCHAR2  DEFAULT NULL);

Parameters

Table 49-5 SET_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition in which to store the statistics. If the index is partitioned and if `partname` is `NULL`, then the statistics are stored at the global index level.
stattab	User stat table identifier describing where to store the statistics. If `stattab` is `NULL`, then the statistics are stored directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
numrows	Number of rows in the index (partition).
numlblks	Number of leaf blocks in the index (partition).
numdist	Number of distinct keys in the index (partition).
avglblk	Average integral number of leaf blocks in which each distinct key appears for this index (partition). If not provided, then this value is derived from `numlblks` and `numdist`.
avgdblk	Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, then this value is derived from `clstfct` and `numdist`.
clstfct	See `clustering_factor` column of the `user_indexes` view for a description.
indlevel	Height of the index (partition).
flags	For internal Oracle use (should be left as `NULL`).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid input value.

SET_TABLE_STATS procedure

This procedure sets table-related information.

Syntax

DBMS_STATS.SET_TABLE_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2 DEFAULT NULL, 
   statid   VARCHAR2 DEFAULT NULL,
   numrows  NUMBER   DEFAULT NULL, 
   numblks  NUMBER   DEFAULT NULL,
   avgrlen  NUMBER   DEFAULT NULL, 
   flags    NUMBER   DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-6 SET_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table.
partname	Name of the table partition in which to store the statistics. If the table is partitioned and `partname` is `NULL`, then the statistics are stored at the global table level.
stattab	User stat table identifier describing where to store the statistics. If `stattab` is `NULL`, then the statistics are stored directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
numrows	Number of rows in the table (partition).
numblks	Number of blocks the table (partition) occupies.
avgrlen	Average row length for the table (partition).
flags	For internal Oracle use (should be left as `NULL`).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid input value.

CONVERT_RAW_VALUE procedure

This procedure converts the internal representation of a minimum or maximum value into a datatype-specific value. The minval and maxval fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.

Syntax

DBMS_STATS.CONVERT_RAW_VALUE (
   rawval     RAW, 
   resval OUT VARCHAR2);

DBMS_STATS.CONVERT_RAW_VALUE (
   rawval     RAW, 
   resval OUT DATE);

DBMS_STATS.CONVERT_RAW_VALUE (
   rawval     RAW, 
   resval OUT NUMBER);

DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR (
   rawval     RAW, 
   resval OUT NVARCHAR2);

DBMS_STATS.CONVERT_RAW_VALUE_ROWID (
   rawval     RAW, 
   resval OUT ROWID);

Pragmas

pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
pragma restrict_references(convert_raw_value_nvarchar, WNDS, RNDS, WNPS, RNPS);
pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 49-7 CONVERT_RAW_VALUE Procedure Parameters

Parameter	Description
rawval	The raw representation of a column minimum or maximum datatype-specific output parameters.
resval	The converted, type-specific value.

Exceptions

None.

GET_COLUMN_STATS procedure

This procedure gets all column-related information.

Syntax

DBMS_STATS.GET_COLUMN_STATS (
   ownname     VARCHAR2, 
   tabname     VARCHAR2, 
   colname     VARCHAR2, 
   partname    VARCHAR2 DEFAULT NULL,
   stattab     VARCHAR2 DEFAULT NULL, 
   statid      VARCHAR2 DEFAULT NULL,
   distcnt OUT NUMBER, 
   density OUT NUMBER,
   nullcnt OUT NUMBER, 
   srec    OUT StatRec,
   avgclen OUT NUMBER,
   statown     VARCHAR2 DEFAULT NULL);

Parameters

Table 49-8 GET_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition from which to get the statistics. If the table is partitioned and if `partname` is `NULL`, then the statistics are retrieved from the global table level.
stattab	User stat table identifier describing from where to retrieve the statistics. If `stattab` is `NULL`, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
distcnt	Number of distinct values.
density	Column density.
nullcnt	Number of `NULLs.`
srec	Structure holding internal representation of column minimum, maximum, and histogram values.
avgclen	Average length of the column (in bytes).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object.

GET_INDEX_STATS procedure

This procedure of gets all index-related information.

Syntax

DBMS_STATS.GET_INDEX_STATS (
   ownname      VARCHAR2, 
   indname      VARCHAR2,
   partname     VARCHAR2 DEFAULT NULL,
   stattab      VARCHAR2 DEFAULT NULL, 
   statid       VARCHAR2 DEFAULT NULL,
   numrows  OUT NUMBER, 
   numlblks OUT NUMBER,
   numdist  OUT NUMBER, 
   avglblk  OUT NUMBER,
   avgdblk  OUT NUMBER, 
   clstfct  OUT NUMBER,
   indlevel OUT NUMBER,
   statown      VARCHAR2 DEFAULT NULL);

Parameters

Table 49-9 GET_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition for which to get the statistics. If the index is partitioned and if `partname` is `NULL`, then the statistics are retrieved for the global index level.
stattab	User stat table identifier describing from where to retrieve the statistics. If `stattab` is `NULL`, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
numrows	Number of rows in the index (partition).
numlblks	Number of leaf blocks in the index (partition).
numdist	Number of distinct keys in the index (partition).
avglblk	Average integral number of leaf blocks in which each distinct key appears for this index (partition).
avgdblk	Average integral number of data blocks in the table pointed to by a distinct key for this index (partition).
clstfct	Clustering factor for the index (partition).
indlevel	Height of the index (partition).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object.

GET_TABLE_STATS procedure

This procedure gets all table-related information.

Syntax

DBMS_STATS.GET_TABLE_STATS (
   ownname     VARCHAR2, 
   tabname     VARCHAR2, 
   partname    VARCHAR2 DEFAULT NULL,
   stattab     VARCHAR2 DEFAULT NULL, 
   statid      VARCHAR2 DEFAULT NULL,
   numrows OUT NUMBER, 
   numblks OUT NUMBER,
   avgrlen OUT NUMBER,
   statown     VARCHAR2 DEFAULT NULL);

Parameters

Table 49-10 GET_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
partname	Name of the table partition from which to get the statistics. If the table is partitioned and if `partname` is `NULL`, then the statistics are retrieved from the global table level.
stattab	User stat table identifier describing from where to retrieve the statistics. If `stattab` is `NULL`, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
numrows	Number of rows in the table (partition).
numblks	Number of blocks the table (partition) occupies.
avgrlen	Average row length for the table (partition).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object

DELETE_COLUMN_STATS procedure

This procedure deletes column-related statistics.

Syntax

DBMS_STATS.DELETE_COLUMN_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2, 
   colname       VARCHAR2, 
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   cascade_parts BOOLEAN  DEFAULT TRUE,
   statown       VARCHAR2 DEFAULT NULL);

Parameters

Table 49-11 DELETE_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition for which to delete the statistics. If the table is partitioned and if `partname` is `NULL`, then global column statistics are deleted.
stattab	User stat table identifier describing from where to delete the statistics. If `stattab` is `NULL`, then the statistics are deleted directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
cascade_parts	If the table is partitioned and if `partname` is `NULL`, then setting this to true causes the deletion of statistics for this column for all underlying partitions as well.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges

DELETE_INDEX_STATS procedure

This procedure deletes index-related statistics.

Syntax

DBMS_STATS.DELETE_INDEX_STATS (
   ownname       VARCHAR2, 
   indname       VARCHAR2,
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   cascade_parts BOOLEAN  DEFAULT TRUE,
   statown       VARCHAR2 DEFAULT NULL);

Parameters

Table 49-12 DELETE_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition for which to delete the statistics. If the index is partitioned and if `partname` is `NULL`, then index statistics are deleted at the global level.
stattab	User stat table identifier describing from where to delete the statistics. If `stattab` is `NULL`, then the statistics are deleted directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
cascade_parts	If the index is partitioned and if `partname` is `NULL`, then setting this to `TRUE` causes the deletion of statistics for this index for all underlying partitions as well.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

DELETE_TABLE_STATS procedure

This procedure deletes table-related statistics.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
   ownname         VARCHAR2, 
   tabname         VARCHAR2, 
   partname        VARCHAR2  DEFAULT NULL,
   stattab         VARCHAR2  DEFAULT NULL, 
   statid          VARCHAR2  DEFAULT NULL,
   cascade_parts   BOOLEAN   DEFAULT TRUE, 
   cascade_columns BOOLEAN   DEFAULT TRUE,
   cascade_indexes BOOLEAN   DEFAULT TRUE,
   statown         VARCHAR2  DEFAULT NULL);

Parameters

Table 49-13 DELETE_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition from which to get the statistics. If the table is partitioned and if `partname` is `NULL`, then the statistics are retrieved from the global table level.
stattab	User stat table identifier describing from where to retrieve the statistics. If `stattab` is `NULL`, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
cascade_parts	If the table is partitioned and if `partname` is `NULL`, then setting this to `TRUE` causes the deletion of statistics for this table for all underlying partitions as well.
cascade_columns	Indicates that `DELETE_COLUMN_STATS` should be called for all underlying columns (passing the `cascade_parts` parameter).
cascade_indexes	Indicates that `DELETE_INDEX_STATS` should be called for all underlying indexes (passing the `cascade_parts` parameter).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

DELETE_SCHEMA_STATS procedure

This procedure deletes statistics for an entire schema.

Syntax

DBMS_STATS.DELETE_SCHEMA_STATS (
   ownname VARCHAR2, 
   stattab VARCHAR2 DEFAULT NULL, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

Parameters

Table 49-14 DELETE_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User stat table identifier describing from where to delete the statistics. If `stattab` is `NULL`, then the statistics are deleted directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges

DELETE_DATABASE_STATS procedure

This procedure deletes statistics for an entire database.

Syntax

DBMS_STATS.DELETE_DATABASE_STATS (
   stattab VARCHAR2 DEFAULT NULL, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

Parameters

Table 49-15 DELETE_DATABASE_STATS Procedure Parameters

Parameter	Description
stattab	User stat table identifier describing from where to delete the statistics. If `stattab` is `NULL`, then the statistics are deleted directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within `stattab` (Only pertinent if `stattab` is not `NULL`).
statown	Schema containing `stattab`. If `stattab` is not `NULL` and if `statown` is `NULL`, then it is assumed that every schema in the database contains a user statistics table with the name `stattab`.

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

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_TABLE_STATS
EXPORT_SCHEMA_STATS
EXPORT_DATABASE_STATS

IMPORT_COLUMN_STATS
IMPORT_INDEX_STATS
IMPORT_TABLE_STATS
IMPORT_SCHEMA_STATS
IMPORT_DATABASE_STATS

CREATE_STAT_TABLE procedure

This procedure creates a table with name stattab in ownname's schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.

Syntax

DBMS_STATS.CREATE_STAT_TABLE (
   ownname  VARCHAR2, 
   stattab  VARCHAR2,
   tblspace VARCHAR2 DEFAULT NULL);

Parameters

Table 49-16 CREATE_STAT_TABLE Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	Name of the table to create. This value should be passed as the `stattab` parameter to other procedures when the user does not want to modify the dictionary statistics directly.
tblspace	Tablespace in which to create the stat tables. If none is specified, then they are created in the user's default tablespace.

Exceptions

ORA-20000: Table already exists or insufficient privileges.

ORA-20001: Tablespace does not exist.

DROP_STAT_TABLE procedure

This procedure drops a user stat table.

Syntax

DBMS_STATS.DROP_STAT_TABLE (
   ownname VARCHAR2, 
   stattab VARCHAR2);

Parameters

Table 49-17 DROP_STAT_TABLE Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User stat table identifier.

Exceptions

ORA-20000: Table does not exists or insufficient privileges.

EXPORT_COLUMN_STATS procedure

This procedure retrieves statistics for a particular column and stores them in the user stat table identified by stattab.

Syntax

DBMS_STATS.EXPORT_COLUMN_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   colname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-18 EXPORT_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition. If the table is partitioned and if `partname` is `NULL`, then global and partition column statistics are exported.
stattab	User stat table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

EXPORT_INDEX_STATS procedure

This procedure retrieves statistics for a particular index and stores them in the user stat table identified by stattab.

Syntax

DBMS_STATS.EXPORT_INDEX_STATS (
   ownname  VARCHAR2, 
   indname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-19 EXPORT_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition. If the index is partitioned and if `partname` is `NULL`, then global and partition index statistics are exported.
stattab	User stat table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

EXPORT_TABLE_STATS procedure

This procedure retrieves statistics for a particular table and stores them in the user stat table. Cascade results in all index and column stats associated with the specified table being exported as well.

Syntax

DBMS_STATS.EXPORT_TABLE_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   cascade  BOOLEAN  DEFAULT TRUE,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-20 EXPORT_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table.
partname	Name of the table partition. If the table is partitioned and if `partname` is `NULL`, then global and partition table statistics are exported.
stattab	User stat table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
cascade	If true, then column and index statistics for this table are also exported.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

EXPORT_SCHEMA_STATS procedure

This procedure retrieves statistics for all objects in the schema identified by ownname and stores them in the user stat tables identified by stattab.

Syntax

DBMS_STATS.EXPORT_SCHEMA_STATS (
   ownname VARCHAR2,
   stattab VARCHAR2, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

Parameters

Table 49-21 EXPORT_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User stat table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

EXPORT_DATABASE_STATS procedure

This procedure retrieves statistics for all objects in the database and stores them in the user stat tables identified by statown.stattab

Syntax

DBMS_STATS.EXPORT_DATABASE_STATS (
   stattab VARCHAR2, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

Parameters

Table 49-22 EXPORT_DATABASE_STATS Procedure Parameters

Parameter	Description
stattab	User stat table identifier describing where to store the statistics
statid	Identifier (optional) to associate with these statistics within `stattab`
statown	Schema containing `stattab`. If `statown` is `NULL`, then it is assumed that every schema in the database contains a user statistics table with the name `stattab`.

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

IMPORT_COLUMN_STATS procedure

This procedure retrieves statistics for a particular column from the user stat table identified by stattab and stores them in the dictionary.

Syntax

DBMS_STATS.IMPORT_COLUMN_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   colname  VARCHAR2,
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-23 IMPORT_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition. If the table is partitioned and if `partname` is `NULL`, then global and partition column statistics are imported.
stattab	User stat table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values in the user stat table.

IMPORT_INDEX_STATS procedure

This procedure retrieves statistics for a particular index from the user stat table identified by stattab and stores them in the dictionary.

Syntax

DBMS_STATS.IMPORT_INDEX_STATS (
   ownname  VARCHAR2, 
   indname  VARCHAR2,
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-24 IMPORT_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition. If the index is partitioned and if `partname` is `NULL`, then global and partition index statistics are imported.
stattab	User stat table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values in the user stat table.

IMPORT_TABLE_STATS procedure

This procedure retrieves statistics for a particular table from the user stat table identified by stattab and stores them in the dictionary. Cascade results in all index and column stats associated with the specified table being imported as well.

Syntax

DBMS_STATS.IMPORT_TABLE_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2,
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   cascade  BOOLEAN  DEFAULT TRUE,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 49-25 IMPORT_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table.
partname	Name of the table partition. If the table is partitioned and if `partname` is `NULL`, then global and partition table statistics are imported.
stattab	User stat table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
cascade	If true, then column and index statistics for this table are also imported.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values in the user stat table.

IMPORT_SCHEMA_STATS procedure

This procedure retrieves statistics for all objects in the schema identified by ownname from the user stat table and stores them in the dictionary.

Syntax

DBMS_STATS.IMPORT_SCHEMA_STATS (
   ownname VARCHAR2,
   stattab VARCHAR2, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

Parameters

Table 49-26 IMPORT_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User stat table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values in the user stat table.

IMPORT_DATABASE_STATS procedure

This procedure retrieves statistics for all objects in the database from the user stat table(s) and stores them in the dictionary.

Syntax

DBMS_STATS.IMPORT_DATABASE_STATS (
   stattab VARCHAR2, 
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

Parameters

Table 49-27 IMPORT_DATABASE_STATS Procedure Parameters

Parameter	Description
stattab	User stat table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab`. If `statown` is `NULL`, then it is assumed that every schema in the database contains a user statistics table with the name `stattab`.

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values in the user stat table.

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

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

GATHER_INDEX_STATS procedure

This procedure gathers index statistics. It is equivalent to running ANALYZE INDEX [ownname.]indname [PARTITION partname] COMPUTE STATISTICS | ESTIMATE STATISTICS SAMPLE estimate_percent PERCENT

It does not execute in parallel.

Syntax

DBMS_STATS_GATHER_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL);

Parameters

Table 49-28 GATHER_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Schema of index to analyze.
indname	Name of index.
partname	Name of partition.
estimate_percent	Percentage of rows to estimate (`NULL` means compute). The valid range is [0.000001,100). This value may be increased automatically to achieve better results.
stattab	User stat table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Index does not exist or insufficient privileges.

ORA-20001: Bad input value.

GATHER_TABLE_STATS procedure

This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters. This operation does not parallelize if the user does not have select privilege on the table being analyzed.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL);

Parameters

Table 49-29 GATHER_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Schema of table to analyze.
tabname	Name of table.
partname	Name of partition.
estimate_percent	Percentage of rows to estimate (`NULL` means compute) The valid range is [0.000001,100). This value may be increased automatically to achieve better results.
block_sample	Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt	Method options of the following format (the phrase '`SIZE 1`' is required to ensure gathering statistics in parallel and for use with the phrase hidden): FOR ALL [INDEXED \| HIDDEN] COLUMNS [SIZE integer] FOR COLUMNS [SIZE integer] column\|attribute [,column\|attribute ...] Optimizer related table statistics are always gathered.
degree	Degree of parallelism (`NULL` means use table default value).
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). `DEFAULT`: Gather global- and partition-level statistics. `SUBPARTITION`: Gather subpartition-level statistics. `PARTITION`: Gather partition-level statistics. `GLOBAL`: Gather global statistics. `ALL`: Gather all (subpartition, partition, and global) statistics.
cascade	Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the `gather_index_stats` procedure on each of the table's indexes.
stattab	User stat table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within `stattab`.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Table does not exist or insufficient privileges.

ORA-20001: Bad input value.

GATHER_SCHEMA_STATS procedure

This procedure gathers statistics for all objects in a schema.

Syntax

DBMS_STATS.GATHER_SCHEMA_STATS (
   ownname          VARCHAR2,
   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE);

DBMS_STATS.GATHER_SCHEMA_STATS (
   ownname          VARCHAR2,
   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist     OUT  ObjectTab,
   statown          VARCHAR2 DEFAULT NULL);

Parameters

Table 49-30 GATHER_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Schema to analyze (`NULL` means current schema).
estimate_percent	Percentage of rows to estimate (`NULL` means compute): The valid range is [0.000001,100).
block_sample	Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt	Method options of the following format (the phrase '`SIZE 1`' is required to ensure gathering statistics in parallel and for use with the phrase hidden): FOR ALL [INDEXED \| HIDDEN] COLUMNS [SIZE integer] This value is passed to all of the individual tables.
degree	Degree of parallelism (`NULL` means use table default value).
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). `DEFAULT`: Gather global- and partition-level statistics. `SUBPARTITION`: Gather subpartition-level statistics. `PARTITION`: Gather partition-level statistics. `GLOBAL`: Gather global statistics. `ALL`: Gather all (subpartition, partition, and global) statistics.
cascade	Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the `gather_index_stats` procedure on each of the indexes in the schema in addition to gathering table and column statistics.
stattab	User stat table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within `stattab.`
options	Further specification of which objects to gather statistics for: `GATHER`: Gather statistics on all objects in the schema. `GATHER` `STALE`: Gather statistics on stale objects as determined by looking at the `_tab_modifications` views. Also, return a list of objects found to be stale. `GATHER` `EMPTY`: Gather statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics. `LIST` `STALE`: Return list of stale objects as determined by looking at the `_tab_modifications` views. `LIST` `EMPTY`: Return list of objects which currently have no statistics.
objlist	List of objects found to be stale or empty.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Schema does not exist or insufficient privileges.

ORA-20001: Bad input value.

GATHER_DATABASE_STATS procedure

This procedure gathers statistics for all objects in the database.

Syntax

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist     OUT  ObjectTab,
   statown          VARCHAR2 DEFAULT NULL);

Parameters

Table 49-31 GATHER_DATABASE_STATS Procedure Parameters

Parameter	Description
estimate_percent	Percentage of rows to estimate (`NULL` means compute): The valid range is [0.000001,100).
block_sample	Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt	Method options of the following format (the phrase '`SIZE 1`' is required to ensure gathering statistics in parallel and for use with the phrase hidden): FOR ALL [INDEXED \| HIDDEN] COLUMNS [SIZE integer] This value is passed to all of the individual tables.
degree	Degree of parallelism (`NULL` means use table default value).
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). `DEFAULT`: Gather global- and partition-level statistics. `SUBPARTITION`: Gather subpartition-level statistics. `PARTITION`: Gather partition-level statistics. `GLOBAL`: Gather global statistics. `ALL`: Gather all (subpartition, partition, and global) statistics.
cascade	Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the `gather_index_stats` procedure on each of the indexes in the database in addition to gathering table and column statistics.
stattab	User stat table identifier describing where to save the current statistics. The statistics table is assumed to reside in the same schema as the object being analyzed, so there must be one such table in each schema to use this option.
statid	Identifier (optional) to associate with these statistics within `stattab`.
options	Further specification of which objects to gather statistics for: `GATHER` `STALE`: Gather statistics on stale objects as determined by looking at the `_tab_modifications` views. Also, return a list of objects found to be stale. `GATHER` `EMPTY`: Gather statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics. `LIST` `STALE`: Return list of stale objects as determined by looking at the `_tab_modifications` views. `LIST` `EMPTY`: Return list of objects which currently have no statistics.
objlist	List of objects found to be stale or empty.
statown	Schema containing `stattab` (if different than `ownname`).

Exceptions

ORA-20000: Insufficient privileges.

ORA-20001: Bad input value.

GENERATE_STATS procedure

This procedure generates object statistics from previously collected statistics of related objects. For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.The currently supported objects are b-tree and bitmap indexes.

Syntax

DBMS_STATS.GENERATE_STATS (
   ownname   VARCHAR2, 
   objname   VARCHAR2,
   organized NUMBER DEFAULT 7);

Parameters

Table 49-32 GENERATE_STATS Procedure Parameters

Parameter

Description

ownname

Schema of object.

objname

Name of object.

organized

Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have consecutive keys referencing different table blocks on disk.

This parameter is only used for b-tree indexes. The number can be in the range of 0-10, with 0 representing a completely organized index and 10 a completely disorganized one.

Exceptions

ORA-20000: Unsupported object type of object does not exist.

ORA-20001: Invalid option or invalid statistics.

Example

Scenario

There has been a lot of modification against the emp table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:

BEGIN
   DBMS_STATS.CREATE_STAT_TABLE ('scott', 'savestats');
   DBMS_STATS.GATHER_TABLE_STATS ('scott', 'emp', 5, stattab => 'savestats');
END;

This operation gathers new statistics on emp, but first saves the original statistics in a user stat table: emp.savestats.

If the user believes that the new statistics are causing the optimizer to generate poor plans, then the original stats can be restored as follows:

BEGIN
   DBMS_STATS.DELETE_TABLE_STATS ('scott', 'emp');
   DBMS_STATS.IMPORT_TABLE_STATS ('scott', 'emp', stattab => 'savestats');
END;

charvals	The array of values when the column type is character-based. Up to the first 32 bytes of each string should be provided. Arrays must have between 2 and 256 entries, inclusive.
datevals	The array of values when the column type is date-based.
numvals	The array of values when the column type is numeric-based.
rawvals	The array of values when the column type is `RAW`. Up to the first 32 bytes of each strings should be provided.
nvmin, nvmax	The minimum and maximum values when the column type is national character set based (NLS). No histogram information can be provided for a column of this type.
rwmin, rwmax	The minimum and maximum values when the column type is rowid. No histogram information can be provided for a column of this type.

49DBMS_STATS

Using DBMS_STATS

Types

Summary of Subprograms

Table 49-1 DBMS_STATS Package Subprograms

Setting or Getting Statistics

PREPARE_COLUMN_VALUES procedure

Syntax

Pragmas

Parameters

Table 49-2 PREPARE_COLUMN_VALUES Procedure Parameters

Output parameters

Table 49-3 PREPARE_COLUMN_VALUES Procedure Output Parameters

Exceptions

SET_COLUMN_STATS procedure

Syntax

Parameters

Table 49-4 SET_COLUMN_STATS Procedure Parameters

Exceptions

SET_INDEX_STATS procedure

Syntax

Parameters

Table 49-5 SET_INDEX_STATS Procedure Parameters

Exceptions

SET_TABLE_STATS procedure

Syntax

Parameters

Table 49-6 SET_TABLE_STATS Procedure Parameters

Exceptions

CONVERT_RAW_VALUE procedure

Syntax

Pragmas

Parameters

Table 49-7 CONVERT_RAW_VALUE Procedure Parameters

Exceptions

GET_COLUMN_STATS procedure

Syntax

Parameters

Table 49-8 GET_COLUMN_STATS Procedure Parameters

Exceptions

GET_INDEX_STATS procedure

Syntax

Parameters

Table 49-9 GET_INDEX_STATS Procedure Parameters

Exceptions

GET_TABLE_STATS procedure

Syntax

Parameters

Table 49-10 GET_TABLE_STATS Procedure Parameters

Exceptions

DELETE_COLUMN_STATS procedure

Syntax

Parameters

Table 49-11 DELETE_COLUMN_STATS Procedure Parameters

Exceptions

DELETE_INDEX_STATS procedure

Syntax

Parameters

Table 49-12 DELETE_INDEX_STATS Procedure Parameters

Exceptions

DELETE_TABLE_STATS procedure

Syntax

Parameters

Table 49-13 DELETE_TABLE_STATS Procedure Parameters

Exceptions

DELETE_SCHEMA_STATS procedure

Syntax

Parameters

Table 49-14 DELETE_SCHEMA_STATS Procedure Parameters

Exceptions

DELETE_DATABASE_STATS procedure

Syntax

Parameters

Table 49-15 DELETE_DATABASE_STATS Procedure Parameters

Exceptions

Transferring Statistics

CREATE_STAT_TABLE procedure

Syntax

Parameters

Table 49-16 CREATE_STAT_TABLE Procedure Parameters

49
DBMS_STATS