Summary of DBMS_STATS Subprograms
Table 70-1 DBMS_STATS Subprograms
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
Datatype-specific input parameters (use one) are shown in Table 70-3.
Table 70-3 Datatype-Specific Input Parameters
Output Parameters
Table 70-4 PREPARE_COLUMN_VALUES Procedure Output Parameters
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 |
---|---|
| Schema of table to analyze. |
| Name of table. |
| Name of partition. |
| Percentage of rows to estimate ( |
| 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. |
| FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
|
| Degree of parallelism. |
granularity | Granularity of statistics to collect (only pertinent if the table is partitioned).
|
cascade | Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the |
stattab | User stat table identifier describing where to save the current statistics. |
statid | Identifier (optional) to associate with these statistics within |
statown | Schema containing |
| Dependent cursors are not invalidated if this parameter is set to |
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 ( |
estimate_percent | Percentage of rows to estimate ( |
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 | FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...], where
|
degree | Degree of parallelism. |
granularity | Granularity of statistics to collect (only pertinent if the table is partitioned).
|
cascade | Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the |
stattab | User stat table identifier describing where to save the current statistics. |
statid | Identifier (optional) to associate with these statistics within |
options | Further specification of which objects to gather statistics for:
|
objlist | List of objects found to be stale or empty. |
statown | Schema containing |
| Dependent cursors are not invalidated if this parameter is set to |
gather_temp | Gathers statistics on global temporary tables. The temporary table must be created with the |
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 ( |
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 | FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...], where
|
degree | Degree of parallelism. |
granularity | Granularity of statistics to collect (only pertinent if the table is partitioned).
|
cascade | Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the |
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 |
options | Further specification of which objects to gather statistics for:
|
objlist | List of objects found to be stale or empty. |
statown | Schema containing |
gather_sys | Gathers statistics on the objects owned by the |
no_invalidate | Dependent cursors are not invalidated if this parameter is set to |
gather_temp | Gathers statistics on global temporary tables. The temporary table must be created with the |
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
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
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 |
---|---|
| The name of the 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 |
---|---|
| The name of the schema. ( |
| 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 |
---|---|
| Enables monitoring if true, and disables monitoring if false. |
| 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;