DBMS_STATS, 2 of 2

Summary of DBMS_STATS Subprograms

Table 70-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 using `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 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 DML monitoring feature of all tables in the database, except for snapshot logs and the tables, which monitoring does not support.

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 using 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 70-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 (use one) are shown in Table 70-3.

Table 70-3 Datatype-Specific Input Parameters

Type	Description
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. If the datatype is fixed `CHAR`, the strings must be space-padded to 15 characters for correct normalization.
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. If the datatype is fixed `CHAR`, the strings must be space-padded to 15 characters for correct normalization.
rwmin, rwmax	The minimum and maximum values when the column type is `rowid`. No histogram information is provided for a column of this type.

Output Parameters

Table 70-4 PREPARE_COLUMN_VALUES Procedure Output Parameters

Parameter	Description
srec.minval	Internal representation of the minimum suitable for use in a call to `SET_COLUMN_STATS`.
srec.maxval	Internal representation of the maximum 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. In the version of this procedure that deals with user-defined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual user-defined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.

Syntax

Use the following for standard statistics:

DBMS_STATS.SET_COLUMN_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2, 
   colname       VARCHAR2, 
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Use the following for user-defined statistics:

DBMS_STATS.SET_COLUMN_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2, 
   colname       VARCHAR2, 
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   ext_stats     RAW,
   stattypown    VARCHAR2 DEFAULT NULL, 
   stattypname   VARCHAR2 DEFAULT NULL, 
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-5 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`).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

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. In the version of this procedure that deals with user-defined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual user-defined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.

Syntax

Use the following for standard statistics:

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,
   no_invalidate BOOLEAN DEFAULT FALSE,
   guessq NUMBER DEFAULT NULL);

Use the following for user-defined statistics:

DBMS_STATS.SET_INDEX_STATS (
   ownname       VARCHAR2, 
   indname       VARCHAR2,
   partname      VARCHAR2  DEFAULT NULL,
   stattab       VARCHAR2  DEFAULT NULL, 
   statid        VARCHAR2  DEFAULT NULL,
   ext_stats     RAW,
   stattypown    VARCHAR2 DEFAULT NULL, 
   stattypname   VARCHAR2 DEFAULT NULL, 
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT FALSE,

Parameters

Table 70-6 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`).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
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 `all_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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.
guessq	Guess quality. See the `pct_direct_access` column of the `all_indexes` view for a description.

Exceptions

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

ORA-20001: Invalid input value.

SET_SYSTEM_STATS Procedure

This procedure sets systems statistics.

Syntax

DBMS_STATS.SET_SYSTEM_STATS (
   pname          VARCHAR2,
   pvalue         NUMBER,
   stattab   IN   VARCHAR2 DEFAULT NULL, 
   statid    IN   VARCHAR2 DEFAULT NULL,
   statown   IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 70-7 SET_SYSTEM_STATS Procedure Parameters

Parameter	Description
pname	The parameter name to get, which can have one of the following values: `sreadtim`--average time to read single block (random read), in milliseconds `mreadtim`--average time to read an mbrc block at once (sequential read), in milliseconds `cpuspeed`--average number of CPU cycles per second, in millions `mbrc`--average multiblock read count for sequential read, in blocks `maxthr`--maximum I/O system throughput, in bytes/sec `slavethr`--average slave I/O throughput, in bytes/sec
pvalue	Parameter value to get.
stattab	Identifier of the user stat table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary.
statid	Optional identifier associated with the statistics saved in the stattab.
statown	The schema containing stattab, if different from the user's schema.

Exceptions

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

ORA-20001: Invalid input value.

ORA-20002: Bad user statistics table; may need to be upgraded.

ORA-20003: Unable to set system statistics.

ORA-20004: Parameter does not exist.

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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-8 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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

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 70-9 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.

GET_COLUMN_STATS Procedure

This procedure gets all column-related information. In the version of this procedure that deals with user-defined statistics, the statistics type returned is the type stored, in addition to the user-defined statistics.

Syntax

Use the following for standard statistics:

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

Use the following for user-defined statistics:

DBMS_STATS.GET_COLUMN_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   colname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   ext_stats   OUT  RAW,
   stattypown  OUT  VARCHAR2 DEFAULT NULL, 
   stattypname OUT  VARCHAR2 DEFAULT NULL, 
   statown          VARCHAR2 DEFAULT NULL);

Parameters

Table 70-10 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`).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
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 gets all index-related information. In the version of this procedure that deals with user-defined statistics, the statistics type returned is the type stored, in addition to the user-defined statistics.

Syntax

Use the following for standard statistics:

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

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,
   guessq   OUT NUMBER);

Use the following for user-defined statistics:

DBMS_STATS.GET_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2,
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   ext_stats   OUT  RAW,
   stattypown  OUT  VARCHAR2 DEFAULT NULL, 
   stattypname OUT  VARCHAR2 DEFAULT NULL, 
   statown          VARCHAR2 DEFAULT NULL,

Parameters

Table 70-11 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`).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
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`).
guessq	Guess quality for the index (partition).

Exceptions

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

GET_SYSTEM_STATS Procedure

This procedure gets system statistics from stattab, or from the dictionary if stattab is null.

Syntax

DBMS_STATS.GET_SYSTEM_STATS (
   status    OUT  VARCHAR2,
   dstart    OUT  DATE,
   dstop     OUT  DATE,
   pname          VARCHAR2,
   pvalue    OUT  NUMBER,
   stattab   IN   VARCHAR2 DEFAULT NULL, 
   statid    IN   VARCHAR2 DEFAULT NULL,
   statown   IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 70-12 GET_SYSTEM_STATS Procedure Parameters

Parameter	Description
status (OUT)	Output is one of the following: `COMPLETED:` `AUTOGATHERING:` `MANUALGATHERING:` `BADSTATS:`
dstart (OUT)	Date when statistics gathering started. If `status` = `MANUALGATHERING`, the start date is returned.
dstop (OUT)	Date when statistics gathering stopped. If `status` = `COMPLETE`, the finish date is returned. If `status` = `AUTOGATHERING`, the future finish date is returned. If `status` = `BADSTATS`, the must-finished-by date is returned.
pname	The parameter name to get, which can have one of the following values: `sreadtim`--average time to read single block (random read), in milliseconds `mreadtim`--average time to read an mbrc block at once (sequential read), in milliseconds `cpuspeed`--average number of CPU cycles per second, in millions `mbrc`--average multiblock read count for sequential read, in blocks `maxthr`--maximum I/O system throughput, in bytes/sec `slavethr`--average slave I/O throughput, in bytes/sec
pvalue	The parameter value to get.
stattab	Identifier of the user stat table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary.
statid	Optional identifier associated with the statistics saved in the stattab.
statown	The schema containing stattab, if different from the user's schema.

Exceptions

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

ORA-20002: Bad user statistics table; may need to be upgraded.

ORA-20003: Unable to gather system statistics.

ORA-20004: Parameter does not exist.

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 70-13 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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-14 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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-15 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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

Exceptions

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

DELETE_SYSTEM_STATS Procedure

This procedure deletes system statistics.

Syntax

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

Parameters

Table 70-16 DELETE_INDEX_STATS Procedure Parameters

Parameter	Description
stattab	Identifier of the user stat table where the statistics will be saved.
statid	Optional identifier associated with the statistics saved in the stattab.
statown	The schema containing stattab, if different from the user's schema.

Exceptions

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

ORA-20002: Bad user statistics table; may need to be upgraded.

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,
   no_invalidate   BOOLEAN DEFAULT FALSE);

Parameters

Table 70-17 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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-18 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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-19 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`.
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

Exceptions

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

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 70-20 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 70-21 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 70-22 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 70-23 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_SYSTEM_STATS Procedure

This procedure retrieves system statistics and stores them in the user stat table, identified by stattab.

Syntax

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

Parameters

Table 70-24 EXPORT_SYSTEM_STATS Procedure Parameters

Parameter	Description
stattab	Identifier of the user stat table that describes where the statistics will be stored.
statid	Optional identifier associated with the statistics stored from the stattab.
statown	The schema containing stattab, if different from the user's schema.

Exceptions

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

ORA-20002: Bad user statistics table; may need to be upgraded.

ORA-20003: Unable to export system statistics.

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 70-25 EXPORT_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
ta