| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
This chapter contains the following topics:
The statistics to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if the DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel
Types for the minimum and maximum values and histogram endpoints include:
TYPE numarray IS VARRAY(256) OF NUMBER; TYPE datearray IS VARRAY(256) OF DATE; TYPE chararray IS VARRAY(256) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(256) OF RAW(2000); TYPE fltarray IS VARRAY(256) OF BINARY_FLOAT; TYPE dblarray IS VARRAY(256) OF BINARY_DOUBLE; TYPE StatRec IS RECORD ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY);
Types for listing stale tables include:
TYPE ObjectElem IS RECORD ( ownname VARCHAR2(30), -- owner objtype VARCHAR2(6), -- 'TABLE' or 'INDEX' objname VARCHAR2(30), -- table/index partname VARCHAR2(30), -- partition subpartname VARCHAR2(30), -- subpartition confidence NUMBER); -- not used type ObjectTab is TABLE of ObjectElem;
Use the following constant to indicate that auto-sample size algorithms should be used:
AUTO_SAMPLE_SIZE CONSTANT NUMBER;
The constant used to determine the system default degree of parallelism, based on the initialization parameters, is:
DEFAULT_DEGREE CONSTANT NUMBER;
Use the following constant to let Oracle select the degree of parallelism based on size of the object, number of CPUs and initialization parameters:
AUTO_DEGREE CONSTANT NUMBER;
Use the following constant to let Oracle decide whether to collect statistics for indexes or not:
AUTO_CASCADE CONSTANT BOOLEAN;
Use the following constant to let oracle decide when to invalidate dependent cursors.
AUTO_INVALIDATE CONSTANT BOOLEAN
The DBMS_STATS subprograms perform the following general operations:
Most of the DBMS_STATS procedures include the three parameters statown, stattab, and statid. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
The stattab parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown parameter is specified). You can create multiple tables with different stattab identifiers to hold separate sets of statistics.
Additionally, you can maintain different sets of statistics within a single stattab by using the statid parameter, which avoids cluttering the user's schema.
For the SET and GET procedures, if stattab is not provided (that is, NULL), then the operation works directly on the dictionary statistics; therefore, you do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if stattab is not NULL, then the SET or GET operation works on the specified user statistics table, and not the dictionary.
You can change the default values of some of the parameters of DBMS_STATS procedures using the SET_PARAM Procedure.
Most of the procedures in this package commit the current transaction, perform the operation, and then commit again.
When a DBMS_STATS subprogram modifies or deletes the statistics for an object, all the dependent cursors are invalidated by default and corresponding statements are subject to recompilation next time so that the new statistics have immediate effects. This behavior can be altered with the no_invalidate argument.
Use the following procedures to store and retrieve individual column-related, index-related, and table-related statistics:
PREPARE_COLUMN_VALUES SET_COLUMN_STATS SET_INDEX_STATS SET_SYSTEM_STATS SET_TABLE_STATS
In the special versions of the SET_*_STATS procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or external statistics table:
extstats)statsschema)statsname)The user-defined statistics and the corresponding statistics type are inserted into the USTATS$ dictionary table. You can specify user-defined statistics without specifying the statistics type name.
CONVERT_RAW_VALUE GET_COLUMN_STATS GET_INDEX_STATS GET_SYSTEM_STATS GET_TABLE_STATS
The special versions of the GET_*_STATS procedures return user-defined statistics and the statistics type owner and name as OUT arguments corresponding to the schema object specified. If user-defined statistics are not collected, NULL values are returned.
The DELETE_* procedures delete both user-defined statistics and the standard statistics for the given schema object.
DELETE_COLUMN_STATS DELETE_DATABASE_STATS DELETE_DICTIONARY_STATS DELETE_FIXED_OBJECTS_STATS DELETE_INDEX_STATS DELETE_SCHEMA_STATS DELETE_SYSTEM_STATS DELETE_TABLE_STATS
Use the following procedures to transfer statistics from the dictionary to a user statistics table (export_*) and from a user statistics table to the dictionary (import_*):
CREATE_STAT_TABLE DROP_STAT_TABLE
CREATE_STAT_TABLE can hold user-defined statistics and the statistics type object number.
EXPORT_COLUMN_STATS EXPORT_DATABASE_STATS EXPORT_DICTIONARY_STATS EXPORT_FIXED_OBJECTS_STATS EXPORT_INDEX_STATS EXPORT_SCHEMA_STATS EXPORT_SYSTEM_STATS EXPORT_TABLE_STATS IMPORT_COLUMN_STATS IMPORT_DICTIONARY_STATS IMPORT_FIXED_OBJECTS_STATS IMPORT_INDEX_STATS IMPORT_SCHEMA_STATS IMPORT_SYSTEM_STATS IMPORT_TABLE_STATS
The IMPORT_* procedures retrieve statistics, including user-defined statistics, from the stattab table and store them in the dictionary. Because the SET_*_STATS and GET_*_STATS interfaces are supported for user-defined statistics, user-defined statistics can be copied to another database using this interface.
Use the following procedures to gather certain classes of optimizer statistics, with possible performance improvements over the ANALYZE command:
GATHER_DATABASE_STATS GATHER_DICTIONARY_STATS GATHER_FIXED_OBJECTS_STATS GATHER_INDEX_STATS GATHER_SCHEMA_STATS GATHER_SYSTEM_STATS GATHER_TABLE_STATS
The GATHER_* procedures also collect user-defined statistics for columns and domain indexes.
The statown, stattab, and statid parameters instruct the package to back up current statistics in the specified table before gathering new statistics.
Oracle also provides the following procedure for generating statistics for derived objects when you have sufficient statistics on related objects:
GENERATE_STATS
Use the following procedures to lock and unlock statistics on objects.
LOCK_TABLE_STATS LOCK_SCHEMA_STATS UNLOCK_TABLE_STATS UNLOCK_SCHEMA_STATS
The LOCK* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
Use the following procedures to restore statistics as of a specified timestamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.
RESTORE_TABLE_STATS RESTORE_SCHEMA_STATS RESTORE_DATABASE_STATS RESTORE_FIXED_OBJECTS_STATS RESTORE_DICTIONARY_STATS RESTORE_SYSTEM_STATS
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure.
The other DBMS_STATS procedures related to restoring statistics are:
PURGE_STATS: This procedure lets you manually purge old versions beyond a time stamp.DBMS_STATS supports operations on user-defined statistics. When a domain index or column is associated with a statistics type (using the associate statement), operations on the index or column manipulate user-defined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the GATHER_INDEX_STATS interface invokes the user-defined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate user-defined statistics.
SET and GET operations for user-defined statistics are also supported using a special version of the SET and GET interfaces for columns and indexes.
The following subprograms are obsolete with Release 10g:
In earlier releases, you could use these subprograms to operate on statistics. These subprograms are now non-operational because Oracle performs their functions automatically.
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 statistics table: hr.savestats.
If the user believes that the new statistics are causing the optimizer to generate poor plans, then the original statistics can be restored as follows:
BEGIN DBMS_STATS.DELETE_TABLE_STATS ('hr', 'employees'); DBMS_STATS.IMPORT_TABLE_STATS ('hr', 'employees', stattab => 'savestats'); END;
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;
| Subprogram | Description |
|---|---|
|
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 [See Deprecated Subprograms] | |
|
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 [See Deprecated Subprograms] | |
|
Changes the statistics history retention value | |
|
Convert the internal representation of a minimum or maximum value into a datatype-specific value | |
|
Convert the internal representation of a minimum or maximum value into a datatype-specific value | |
|
Convert the internal representation of a minimum or maximum value into a datatype-specific value | |
|
Creates a table with name | |
|
Deletes column-related statistics | |
|
Deletes statistics for the entire database | |
|
Deletes statistics for all dictionary schemas (' | |
|
Deletes statistics of all fixed tables | |
|
Deletes index-related statistics | |
|
Deletes schema-related statistics | |
|
Deletes system statistics | |
|
Deletes table-related statistics | |
|
Drops a user statistics table created by | |
|
Retrieves statistics for a particular column and stores them in the user statistics table identified by | |
|
Retrieves statistics for all objects in the database and stores them in the user statistics table identified by | |
|
Retrieves statistics for all dictionary schemas (' | |
|
Retrieves statistics for fixed tables and stores them in the user statistics table identified by | |
|
Retrieves statistics for a particular index and stores them in the user statistics table identified by | |
|
Retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics table identified by | |
|
Retrieves system statistics and stores them in the user statistics table | |
|
Retrieves statistics for a particular table and stores them in the user statistics table | |
|
Flushes in-memory monitoring information for all the tables to the dictionary | |
|
Gathers statistics for all objects in the database | |
|
Gathers statistics for dictionary schemas ' | |
|
Gathers statistics of fixed objects | |
|
Gathers index statistics | |
|
Gathers statistics for all objects in a schema | |
|
Gathers system statistics | |
|
Gathers table and column (and index) statistics | |
|
Generates object statistics from previously collected statistics of related objects | |
|
Gets all column-related information | |
|
Gets all index-related information | |
|
Gets the default value of parameters of | |
|
Gets the oldest timestamp where statistics history is available | |
|
Returns the current retention value | |
|
Gets system statistics from stattab, or from the dictionary if stattab is | |
|
Gets all table-related information | |
|
Retrieves statistics for a particular column from the user statistics table identified by | |
|
Retrieves statistics for all objects in the database from the user statistics table and stores them in the dictionary | |
|
Retrieves statistics for all dictionary schemas (' | |
|
Retrieves statistics for fixed tables from the user statistics table identified by | |
|
Retrieves statistics for a particular index from the user statistics table identified by | |
|
Retrieves statistics for all objects in the schema identified by | |
|
Retrieves system statistics from the user statistics table and stores them in the dictionary | |
|
Retrieves statistics for a particular table from the user statistics table identified by | |
|
Locks the statistics of all tables of a schema | |
|
Locks the statistics on the table | |
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the | |
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the | |
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the | |
|
Purges old versions of statistics saved in the dictionary | |
|
Restores statistics of all tables of the database as of a specified timestamp | |
|
Restores statistics of all dictionary tables (tables of ' | |
|
Restores statistics of all fixed tables as of a specified timestamp | |
|
Restores statistics of all tables of a schema as of a specified timestamp | |
|
Restores statistics of all tables of a schema as of a specified timestamp | |
|
Restores statistics of a table as of a specified timestamp (as_of_timestamp), as well as statistics of associated indexes and columns | |
|
Sets column-related information | |
|
Sets index-related information | |
|
Sets default values for parameters of DBMS_STATS procedures | |
|
Sets system statistics | |
|
Sets table-related information | |
|
Unlocks the statistics on all the table in a schema | |
|
Unlocks the statistics on the table | |
|
Upgrades user statistics on an older table |
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.
DBMS_STATS.ALTER_DATABASE_TAB_MONITORING ( monitoring BOOLEAN DEFAULT TRUE, sysobjs BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
Enables monitoring if true, and disables monitoring if false. |
|
|
If true, changes monitoring on the dictionary objects. |
ORA-20000: Insufficient privileges.
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.
DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING ( ownname VARCHAR2 DEFAULT NULL, monitoring BOOLEAN DEFAULT TRUE);
| Parameter | Description |
|---|---|
|
|
The name of the schema. ( |
|
|
Enables monitoring if true, and disables monitoring if false. |
You should enable monitoring if you use GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS with the GATHER AUTO or GATHER STALE options.
ORA-20000: Insufficient privileges.
This procedure changes the statistics history retention value. Statistics history retention is used by both the automatic purge and PURGE_STATS Procedure.
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( retention IN NUMBER);
To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
ORA-20000: Insufficient privileges.
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.
DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_FLOAT); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_DOUBLE); 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 ( rawval RAW, resval OUT VARCHAR2);
pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
| Parameter | Description |
|---|---|
|
|
The raw representation of a column minimum or maximum datatype-specific output parameters. |
|
|
The converted, type-specific value. |
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.
DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR ( rawval RAW, resval OUT NVARCHAR2);
pragma restrict_references(convert_raw_value_nvarchar, WNDS, RNDS, WNPS, RNPS);
| Parameter | Description |
|---|---|
|
|
The raw representation of a column minimum or maximum datatype-specific output parameters. |
|
|
The converted, type-specific value. |
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.
DBMS_STATS.CONVERT_RAW_VALUE_ROWID ( rawval RAW, resval OUT ROWID);
pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);
| Parameter | Description |
|---|---|
|
|
The raw representation of a column minimum or maximum datatype-specific output parameters. |
|
|
The converted, type-specific value. |
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.
DBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);
ORA-20000: Table already exists or insufficient privileges.
ORA-20001: Tablespace does not exist.
This procedure deletes column-related statistics.
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 to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
Name of the schema. |
|
|
Name of the table to which this column belongs. |
|
|
Name of the column. |
|
|
Name of the table partition for which to delete the statistics. If the table is partitioned and if |
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
If the table is partitioned and if |
|
|
Schema containing |
|
|
If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use |
|
|
When value of this argument is |
ORA-20000: Object does not exist or insufficient privileges.
ORA-20005: Object statistics are locked.
This procedure deletes statistics for all the tables in a database.
DBMS_STATS.DELETE_DATABASE_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use |
|
|
When the value of this argument is |
ORA-20000: Object does not exist or insufficient privileges.
This procedure deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas).
DBMS_STATS.DELETE_DICTIONARY_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.)Use |
|
|
When the value of this argument is |
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
ORA-20000: Object does not exist or insufficient privileges.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure deletes statistics of all fixed tables.
DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
The user statistics table identifier describing from where to delete the current statistics. If |
|
|
The (optional) identifier to associate with these statistics within stattab. This only applies if |
|
|
The schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Ignores the statistics lock on objects and deletes the statistics if set to |
You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
ORA-20000: Insufficient privileges.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure deletes index-related statistics.
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 to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
Name of the schema. |
|
|
Name of the index. |
|
|
Name of the index partition for which to delete the statistics. If the index is partitioned and if |
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
If the index is partitioned and if |
|
|
Schema containing |
|
|
If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use |
|
|
When value of this argument is |
ORA-20000: Object does not exist or insufficient privileges.
ORA-20005: Object statistics are locked.
This procedure deletes statistics for an entire schema.
DBMS_STATS.DELETE_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
Name of the schema. |
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use |
|
|
When value of this argument is |
ORA-20000: Object does not exist or insufficient privileges
This procedure deletes workload statistics (collected using the 'INTERVAL' or 'START' and 'STOP' options) and resets the default to noworkload statistics (collected using 'NOWORKLOAD' option) if stattab is not specified. If stattab is specified, the subprogram deletes all system statistics with the associated statid from the stattab.
DBMS_STATS.DELETE_SYSTEM_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA-20000: Object does not exist or insufficient privileges.
ORA-20002: Bad user statistics table; may need to be upgraded.
This procedure deletes table-related statistics.
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 to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
| Parameter | Description |
|---|---|
|
|
Name of the schema. |
|
|
Name of the table to which this column belongs. |
|
|
Name of the table partition from which to get the statistics. If the table is partitioned and if |
|
|
User statistics table identifier describing from where to retrieve the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
If the table is partitioned and if |
|
|
Indicates that |
|
|
Indicates that |
|
|
Schema containing |
|
|
If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use |
|
|
When value of this argument is |
ORA-20000: Object does not exist or insufficient privileges.
ORA-20005: Object statistics are locked.
This procedure drops a user statistics table.
DBMS_STATS.DROP_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
| Parameter | Description |
|---|---|
|
|
Name of the schema. |
|
|
User statistics table identifier. |
ORA-20000: Table does not exists or insufficient privileges.
This procedure retrieves statistics for a particular column and stores them in the user statistics table identified by stattab.
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);
ORA-20000: Object does not exist or insufficient privileges.
This procedure retrieves statistics for all objects in the database and stores them in the user statistics tables identified by statown.stattab.
DBMS_STATS.EXPORT_DATABASE_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA-20000: Object does not exist or insufficient privileges.
This procedure retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) and stores them in the user statistics table identified by stattab.
DBMS_STATS.EXPORT_DICTIONARY_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
You must have the SYSDBA or ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
ORA-20000: Object does not exist or insufficient privileges.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for fixed tables and stores them in the user statistics table identified by stattab.
DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA-20000: Object does not exist or insufficient privileges.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for a particular index and stores them in the user statistics table identified by stattab.
DBMS_STATS.EXPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA-20000: Object does not exist or insufficient privileges.
This procedure retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics tables identified by stattab.