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

Parameters

Table 70-28 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`).
`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 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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-29 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`).
`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 values in the user stat table.

IMPORT_SYSTEM_STATS Procedure

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

Syntax

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

Parameters

Table 70-30 IMPORT_SYSTEM_STATS Procedure Parameters

Parameter	Description
stattab	Identifier of the user stat table where the statistics will be retrieved.
statid	Optional identifier associated with the statistics retrieved 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-20001: Invalid or inconsistent values in the user stat table.

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

ORA-20003: Unable to import system statistics.

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

Parameters

Table 70-31 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`).
`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 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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-32 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`).
`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 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,
   no_invalidate BOOLEAN DEFAULT FALSE);

Parameters

Table 70-33 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`.
`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 values in the user stat table.

GATHER_INDEX_STATS Procedure

This procedure gathers index statistics. It attempts to parallelize as much of the work as possible. Restrictions are described in the individual parameters. This operation will not parallelize with certain types of indexes, including cluster indexes, domain indexes, and bitmap join indexes. The granularity and no_invalidate arguments are not relevant to these types of indexes.

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,
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT',
   no_invalidate    BOOLEAN  DEFAULT FALSE);

Parameters

Table 70-34 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]`. Use the constant `DBMS_STATS.AUTO_SAMPLE_SIZE` to have Oracle determine the best sample size for good statistics.
`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`).
degree	Degree of parallelism (`NULL` means use of table default value that was specified by the `DEGREE` clause in the `CREATE/ALTER INDEX` statement). Use the constant `DBMS_STATS.DEFAULT_DEGREE` for the default value based on the initialization parameters.
granularity	The granularity of statistics to collect (only pertinent if the index is partitioned): `'DEFAULT'` - gathers global and partition-level statistics `'SUBPARTITION'` - gathers subpartition-level statistics `'PARTITION` '- gathers partition-level statistics `'GLOBAL'` - gathers global statistics `'ALL'` - gathers all (subpartition, partition, and global) statistics
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`.

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

Parameters

Table 70-35 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]. Use the constant `DBMS_STATS.AUTO_SAMPLE_SIZE` to have Oracle determine the best sample size for good statistics.
`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`	Accepts: FOR ALL [INDEXED \| HIDDEN] COLUMNS [size_clause] `FOR COLUMNS [size clause] column\|attribute [size_clause] [,column\|attribute [size_clause]...]`, where `size_clause` is defined as: `size_clause := SIZE {integer \| REPEAT \| AUTO \| SKEWONLY}` `integer`--Number of histogram buckets. Must be in the range [1,254]. `REPEAT`--Collects histograms only on the columns that already have histograms. `AUTO`--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. `SKEWONLY`--Oracle determines the columns to collect histograms based on the data distribution of the columns.
`degree`	Degree of parallelism. `NULL` means use the table default value specified by the `DEGREE` clause in the `CREATE TABLE` or `ALTER TABLE` statement. Use the constant `DBMS_STATS.DEFAULT_DEGREE` to specify the default value based on the initialization parameters.
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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`. When the `'cascade'` argument is specified, this parameter is not relevant with certain types of indexes, as described in "GATHER_INDEX_STATS Procedure".

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,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist      OUT ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN DEFAULT FALSE,
   gather_temp      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',
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT FALSE,
   gather_temp      BOOLEAN  DEFAULT FALSE);

Parameters

Table 70-36 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]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.
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	Accepts: FOR ALL [INDEXED \| HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column\|attribute [size_clause] [,column\|attribute [size_clause]...], where `size_clause` is defined as size_clause := SIZE {integer \| REPEAT \| AUTO \| SKEWONLY} `integer`--Number of histogram buckets. Must be in the range [1,254]. `REPEAT`--Collects histograms only on the columns that already have histograms. `AUTO`--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. `SKEWONLY`--Oracle determines the columns to collect histograms based on the data distribution of the columns.
degree	Degree of parallelism. `NULL` means use the table default value specified by the `DEGREE` clause in the `CREATE TABLE` or `ALTER TABLE` statement. Use the constant `DBMS_STATS.DEFAULT_DEGREE` to specify the default value based on the initialization parameters.
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`: Gathers statistics on all objects in the schema. `GATHER` `AUTO`: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When `GATHER AUTO` is specified, the only additional valid parameters are `ownname`, `stattab`, `statid`, `objlist` and `statown`; all other parameter settings are ignored. Returns a list of processed objects. `GATHER` `STALE`: Gathers 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`: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics. `LIST AUTO`: Returns a list of objects to be processed with `GATHER AUTO`. `LIST` `STALE`: Returns list of stale objects as determined by looking at the `_tab_modifications` views. `LIST` `EMPTY`: Returns 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`).
`no_invalidate`	Dependent cursors are not invalidated if this parameter is set to `TRUE`. When the `'cascade'` argument is specified, this parameter is not relevant with certain types of indexes, as described in "GATHER_INDEX_STATS Procedure".
gather_temp	Gathers statistics on global temporary tables. The temporary table must be created with the `"on commit preserve rows"` clause. The statistics being collected are based on the data in the session in which this procedure is run, but shared across all sessions.

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,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   objlist   OUT    ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN DEFAULT FALSE,
   no_invalidate    BOOLEAN DEFAULT FALSE,
   gather_temp      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',
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN DEFAULT FALSE,
   no_invalidate    BOOLEAN DEFAULT FALSE,
   gather_temp      BOOLEAN DEFAULT FALSE);

Parameters

Table 70-37 GATHER_DATABASE_STATS Procedure Parameters

Parameter	Description
estimate_percent	Percentage of rows to estimate (`NULL` means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.
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	Accepts: FOR ALL [INDEXED \| HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column\|attribute [size_clause] [,column\|attribute [size_clause]...], where `size_clause` is defined as size_clause := SIZE {integer \| REPEAT \| AUTO \| SKEWONLY} `integer`--Number of histogram buckets. Must be in the range [1,254]. `REPEAT`--Collects histograms only on the columns that already have histograms. `AUTO`--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. `SKEWONLY`--Oracle determines the columns to collect histograms based on the data distribution of the columns.
degree	Degree of parallelism. `NULL` means use the table default value specified by the `DEGREE` clause in the `CREATE TABLE` or `ALTER TABLE` statement. Use the constant `DBMS_STATS.DEFAULT_DEGREE` to specify the default value based on the initialization parameters.
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`: Gathers statistics on all objects in the schema. `GATHER` `AUTO`: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When `GATHER AUTO` is specified, the only additional valid parameters are `stattab`, `statid`, `objlist` and `statown`; all other parameter settings are ignored. Returns a list of processed objects. `GATHER` `STALE`: Gathers 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`: Gathers statistics on objects which currently have no statistics. Return a list of objects found to have no statistics. `LIST AUTO`: Returns a list of objects to be processed with `GATHER AUTO`. `LIST` `STALE`: Returns a list of stale objects as determined by looking at the `_tab_modifications` views. `LIST` `EMPTY`: Returns a 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`).
gather_sys	Gathers statistics on the objects owned by the `'SYS'` user.
no_invalidate	Dependent cursors are not invalidated if this parameter is set to `TRUE`. When the `'cascade'` option is specified, this parameter is not relevant with certain types of indexes, as described in "GATHER_INDEX_STATS Procedure".
gather_temp	Gathers statistics on global temporary tables. The temporary table must be created with the `"on commit preserve rows"` clause. The statistics being collected are based on the data in the session in which this procedure is run, but shared across all sessions.

Exceptions

ORA-20000: Insufficient privileges.

ORA-20001: Bad input value.

GATHER_SYSTEM_STATS Procedure

This procedure gathers system statistics.

Syntax

DBMS_STATS.GATHER_SYSTEM_STATS (
   gathering_mode   VARCHAR2 DEFAULT 'NOWORKLOAD',
   interval         INTEGER  DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL);

Parameters

Table 70-38 GATHER_SYSTEM_STATS Procedure Parameters

Parameter	Description
gathering_mode	Mode values are: `NOWORKLOAD`: No workload is required to capture system activity. Oracle generates system statistics using internal defaults. This mode can be used when suitable workload cannot be submitted (during the development process, for example). For system statistics values to be based on real system activity, use the `INTERVAL` or `START\|STOP` modes instead. `INTERVAL`: Captures system activity during a specified interval. This works in combination with the `interval` parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or `stattab`. You can use `gather_system_stats(gathering_mode=>'STOP')`to stop gathering earlier when scheduled. `START` \| `STOP`: Captures system activity during specified start and stop times and refreshes the dictionary or `stattab` with statistics for the elapsed period. `Interval` value is ignored.
interval	Time, in minutes, to gather statistics. This parameter applies only when `gathering_mode='INTERVAL'`.
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-20001: Invalid input value.

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

ORA-20003: Unable to gather system statistics.

ORA-20004: Error in the INTERVAL mode: system parameter job_queue_processes must be >0.

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

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.

FLUSH_SCHEMA_MONITORING_INFO Procedure

This procedure flushes in-memory monitoring information for the tables in the specified schema to the dictionary.

Syntax

DBMS_STATS.FLUSH_SCHEMA_MONITORING_INFO (
   ownname VARCHAR2 DEFAULT NULL);

Parameters

Table 70-40 FLUSH_SCHEMA_MONITORING_INFO Procedure Parameters

Parameter	Description
`ownname`	The name of the schema. (`NULL` means the current schema.)

Exceptions

ORA-20000: The object does not exist or it contains insufficient privileges.

FLUSH_DATABASE_MONITORING_INFO Procedure

This procedure flushes in-memory monitoring information for all the tables to the dictionary.

Syntax

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

Exceptions

ORA-20000: Insufficient privileges.

ALTER_SCHEMA_TABLE_MONITORING Procedure

This procedure enable or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Using this procedure is equivalent to issuing ALTER TABLE...MONITORING (or NOMONITORING) individually. You should enable monitoring if you use GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS with the GATHER AUTO or GATHER STALE options.

Syntax

DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING (
   ownname    VARCHAR2 DEFAULT NULL,
   monitoring BOOLEAN DEFAULT TRUE);

Parameters

Table 70-41 ALTER_SCHEMA_TABLE_MONITORING Procedure Parameters

Parameter	Description
`ownname`	The name of the schema. (`NULL` means the current schema.)
`monitoring`	Enables monitoring if true, and disables monitoring if false.

Exceptions

ORA-20000: Insufficient privileges.

ALTER_DATABASE_TABLE_MONITORING Procedure

This procedure enables or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Using this procedure is equivalent to issuing ALTER TABLE...MONITORING (or NOMONITORING) individually. You should enable monitoring if you use GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS with the GATHER AUTO or GATHER STALE options.

Syntax

DBMS_STATS.ALTER_DATABASE_TABLE_MONITORING (
   monitoring BOOLEAN DEFAULT TRUE,
   sysobjs    BOOLEAN DEFAULT FALSE);

Parameters

Table 70-42 ALTER_DATABASE_TABLE_MONITORING Procedure Parameters

Parameter	Description
`monitoring`	Enables monitoring if true, and disables monitoring if false.
`sysobjs`	If true, changes monitoring on the dictionary objects.

Exceptions

ORA-20000: Insufficient privileges.

Saving Original Statistics and Gathering New Statistics: Example

Assume many modifications have been made to the employees 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 ('hr', 'savestats');
   DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees', stattab => 'savestats');
END;

This operation gathers new statistics on the employees table, but first saves the original statistics in a user stat table: hr.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 ('hr', 'employees');
   DBMS_STATS.IMPORT_TABLE_STATS ('hr', 'employees', stattab => 'savestats');
END;

Gathering Daytime System Statistics: Example

Assume that you want to perform database application processing OLTP transactions during the day and run reports at night.

To collect daytime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS table.

BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS (
      interval => 720,
      stattab  => 'mystats',
      statid   => 'OLTP');
END;

To collect nighttime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS table.

BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS (
      interval => 720,
      stattab  => 'mystats',
      statid   => 'OLAP');
END;

Update the dictionary with the gathered statistics.

VARIABLE   jobno   number;
BEGIN
   DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS

   (''mystats'',''OLTP'');'
   sysdate, 'sysdate + 1');
   COMMIT;
END;

BEGIN
   DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS

   (''mystats'',''OLAP'');'
   sysdate + 0.5, 'sysdate + 1');
   COMMIT;
END;