| Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-02 |
|
|
View PDF |
| Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-02 |
|
|
View PDF |
With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
See Also:
Oracle Database Performance Tuning GuideThis chapter contains the following topics:
Overview
Types
Constants
Operational Notes
Deprecated Subprograms
Examples
This section contains topics which relate to using the DBMS_STATS package.
The Oracle RDBMS allows you to collect statistics of many different kinds as an aid to to improving performance. This package is concerned with optimizer statistics only. Given that Oracle sets automatic statistics collection of this kind on by default, this package is intended for only specialized cases.
The statistics of interest 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
See Also:
Oracle Database Performance Tuning Guide for more information about "Managing Optimizer Statistics".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.
Most of the procedures have a parameter, force which allows you to override any lock on statistics.Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring.
Gathering Optimizer Statistics
Use the following subprograms to gather certain classes of optimizer statistics, with possible performance improvements over the ANALYZE command:
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:
Setting or Getting Statistics
Use the following subprograms to store and retrieve individual column-related, index-related, and table-related statistics:
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:
User-defined statistics (extstats)
The statistics type schema name (statsschema)
The statistics type name (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.
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.
Deleting Statistics
The DELETE_* procedures delete both user-defined statistics and the standard statistics for the given schema object.
Transferring Statistics
Use the following procedures for creating and dropping the user statistics table.
Use the following procedures to transfer statistics
from the dictionary to a user statistics table (EXPORT_*)
from a user statistics table to the dictionary (IMPORT_*)
Locking or Unlocking Statistics
Use the following procedures to lock and unlock statistics on objects.
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.
Restoring and Purging Statistics History
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.
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 recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION Procedure.
The other DBMS_STATS procedures related to restoring statistics are:
PURGE_STATS Procedure: This procedure lets you manually purge old versions beyond a time stamp.
GET_STATS_HISTORY_RETENTION Function: This function gets the current statistics history retention value.
GET_STATS_HISTORY_AVAILABILITY Function: This function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.
RESTORE_* operations are not supported for user defined statistics.
User-Defined Statistics
The DBMS_STATS package 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 GET_INDEX_STATS Procedures 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.
EXPORT_*, IMPORT_* and RESTORE_* operations are not supported for user defined statistics.
The following subprograms are obsolete with Release 10g:
In earlier releases, you could use these subprograms to change DML monitoring behavior. These subprograms are now non-operational because Oracle performs their functions automatically.
Saving Original Statistics and Gathering New Statistics
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;
Gathering Daytime System Statistics
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;
Table 103-1 DBMS_STATS Package Subprograms
| 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 SET_COLUMN_STATS Procedures |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures |
|
|
Purges old versions of statistics saved in the dictionary |
|
|
Resets the default values of all parameters to Oracle recommended values |
|
|
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 |
|
|
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 |
Note:
See Deprecated Subprograms.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.
Syntax
DBMS_STATS.ALTER_DATABASE_TAB_MONITORING ( monitoring BOOLEAN DEFAULT TRUE, sysobjs BOOLEAN DEFAULT FALSE);
Parameters
Table 103-2 ALTER_DATABASE_TAB_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.
Note:
See Deprecated Subprograms.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.
Syntax
DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING ( ownname VARCHAR2 DEFAULT NULL, monitoring BOOLEAN DEFAULT TRUE);
Parameters
Table 103-3 ALTER_SCHEMA_TAB_MONITORING Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the schema. ( |
|
|
Enables |
Usage Notes
You should enable monitoring if you use GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS with the GATHER AUTO or GATHER STALE options.
Exceptions
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.
Syntax
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( retention IN NUMBER);
Parameters
Table 103-4 ALTER_STATS_HISTORY_RETENTION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The retention time in days. The statistics history will be retained for at least these many number of days.The valid range is [1,365000]. Also you can use the following values for special purposes:
|
Usage Notes
To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
Exceptions
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.
Syntax
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);
Pragmas
pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 103-5 CONVERT_RAW_VALUE Procedure Parameters
| 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.
Syntax
DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR ( rawval RAW, resval OUT NVARCHAR2);
Pragmas
pragma restrict_references(convert_raw_value_nvarchar, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 103-6 CONVERT_RAW_VALUE_NVARCHAR Procedure Parameters
| 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.
Syntax
DBMS_STATS.CONVERT_RAW_VALUE_ROWID ( rawval RAW, resval OUT ROWID);
Pragmas
pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 103-7 CONVERT_RAW_VALUE_ROWID Procedure Parameters
| 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.
Syntax
DBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);
Parameters
Table 103-8 CREATE_STAT_TABLE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table to create. This value should be passed as the |
|
|
Tablespace in which to create the statistics 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.
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 to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-9 DELETE_COLUMN_STATS Procedure Parameters
| 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 |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When value of this argument is |
Exceptions
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.
Syntax
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);
Parameters
Table 103-10 DELETE_DATABASE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When the value of this argument is |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
This procedure deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas).
Syntax
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);
Parameters
Table 103-11 DELETE_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When the value of this argument is |
Usage Notes
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
Exceptions
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.
Syntax
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);
Parameters
Table 103-12 DELETE_FIXED_OBJECTS_STATS Procedure Parameters
| 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 |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Ignores the statistics lock on objects and deletes the statistics if set to |
Usage Notes
You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
Exceptions
ORA-20000: Insufficient privileges.
ORA-20002: Bad user statistics table, may need to upgrade it.
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 to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-13 DELETE_INDEX_STATS Procedure Parameters
| 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 |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When value of this argument is |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20005: Object statistics are locked.
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 to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-14 DELETE_SCHEMA_STATS Procedure Parameters
| 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 |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When value of this argument is |
Exceptions
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.
Syntax
DBMS_STATS.DELETE_SYSTEM_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 103-15 DELETE_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Identifier of the user statistics table where the statistics will be saved |
|
|
Optional identifier associated with the statistics saved in the stattab |
|
|
Schema containing |
Exceptions
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.
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 to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-16 DELETE_TABLE_STATS Procedure Parameters
| 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 |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When value of this argument is |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20005: Object statistics are locked.
This procedure drops a user statistics table.
Syntax
DBMS_STATS.DROP_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
Parameters
Table 103-17 DROP_STAT_TABLE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
User statistics table identifier |
Exceptions
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.
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 103-18 EXPORT_COLUMN_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table to which this column belongs |
|
|
Name of the column |
|
|
Name of the table partition. If the table is partitioned and if |
|
|
User statistics table identifier describing where to store the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
Exceptions
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.
Syntax
DBMS_STATS.EXPORT_DATABASE_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 103-19 EXPORT_DATABASE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing where to store the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
Exceptions
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.
Syntax
DBMS_STATS.EXPORT_DICTIONARY_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 103-20 EXPORT_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing where to store the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
Usage Notes
You must have the SYSDBA or ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
Exceptions
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.
Syntax
DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 103-21 EXPORT_FIXED_OBJECTS_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing where to store the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
Exceptions
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.
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 103-22 EXPORT_INDEX_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the index |
|
|
Name of the index partition. If the index is partitioned and if |
|
|
User statistics table identifier describing where to store the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
Exceptions
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.
Syntax
DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 103-23 EXPORT_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
User statistics table identifier describing where to store the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
This procedure retrieves system statistics and stores them in the user statistics table, identified by stattab.
Syntax
DBMS_STATS.EXPORT_SYSTEM_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 103-24 EXPORT_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Identifier of the user statistics table that describes where the statistics will be stored. |
|
|
Optional identifier associated with the statistics stored from the |
|
|
Schema containing |
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.
This procedure retrieves statistics for a particular table and stores them in the user statistics table. Cascade results in all index and column statistics 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 103-25 EXPORT_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table |
|
|
Name of the table partition. If the table is partitioned and if |
|
|
User statistics table identifier describing where to store the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
If true, then column and index statistics for this table are also exported |
|
|
Schema containing |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
This procedure flushes in-memory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS views are updated immediately, without waiting for the Oracle database to flush them periodically. This procedure is useful when you need up-to-date information in those views. Because the GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.
Syntax
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
Exceptions
ORA-20000: Insufficient privileges.
This procedure gathers statistics for all objects in the database.
Syntax
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')));
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')));
Parameters
Table 103-26 GATHER_DATABASE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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. |
|
|
Accepts:
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.
The default is |
|
|
Degree of parallelism. The default for |
|
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
|
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. Use the constant |
|
|
User statistics 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. |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Further specification of which objects to gather statistics for:
|
|
|
List of objects found to be stale or empty |
|
|
Schema containing |
|
|
Gathers statistics on the objects owned by the |
|
|
Does not invalidate the dependent cursors if set to |
Usage Notes
Statistics for external tables are not collected by this procedure.
Exceptions
ORA-20000: Insufficient privileges.
ORA-20001: Bad input value.
This procedure gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components.
Syntax
DBMS_STATS.GATHER_DICTIONARY_STATS (
comp_id VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER AUTO',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')));
DBMS_STATS.GATHER_DICTIONARY_STATS (
comp_id VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT
to_estimate_percent_type(GET_PARAM('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(GET_PARAM('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(GET_PARAM('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER AUTO',
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(get_param('NO_INVALIDATE')));
Parameters
Table 103-27 GATHER_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The component id of the schema to analyze ( |
|
|
Percentage of rows to estimate ( |
|
|
Determines 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 performing estimate statistics. |
|
|
Accepts:
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.
The default is |
|
|
Degree of parallelism. The default for |
|
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
|
Gathers statistics on indexes also.Index statistics gathering will not be 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. Use the constant |
|
|
User statistics table identifier describing where to save the current statistics |
|
|
The (optional) identifier to associate with these statistics within |
|
|
Further specification of objects for which to gather statistics:
|
|
|
The list of objects found to be stale or empty |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
Usage Notes
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
Exceptions
ORA-20000: Index does not exist or insufficient privileges.
ORA-20001: Bad input value.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure gathers statistics for all fixed objects (dynamic performance tables).
Syntax
DBMS_STATS.GATHER_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')));
Parameters
Table 103-28 GATHER_FIXED_OBJECTS_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The user statistics table identifier describing where to save the current statistics |
|
|
The (optional) identifier to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
Usage Notes
You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
Exceptions
ORA-20000: Insufficient privileges.
ORA-20001: Bad input value.
ORA-20002: Bad user statistics table, may need to upgrade it.
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 to_estimate_percent_type
(GET_PARAM('ESTIMATE_PERCENT')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-29 GATHER_INDEX_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of index to analyze |
|
|
Name of index |
|
|
Name of partition |
|
|
Percentage of rows to estimate ( |
|
|
User statistics table identifier describing where to save the current statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Degree of parallelism. The default for |
|
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
|
Does not invalidate the dependent cursors if set to |
|
|
Gather statistics on object even if it is locked |
Exceptions
ORA-20000: Index does not exist or insufficient privileges.
ORA-20001: Bad input value.
This procedure gathers statistics for all objects in a schema.
Syntax
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE'),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-30 GATHER_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema to analyze ( |
|
|
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. |
|
|
Accepts:
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.
The default is |
|
|
Degree of parallelism. The default for |
|
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
|
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. Use the constant |
|
|
User statistics table identifier describing where to save the current statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Further specification of which objects to gather statistics for:
|
|
|
List of objects found to be stale or empty |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Gather statistics on objects even if they are locked |
Usage Notes
DBMS_STATS.GATHER_SCHEMA_STATS generates differing sampling rates on partitioned tables when you use the auto_sample_size constant. DBMS_STATS tries to determine an adequate sample size for each type of statistic, which is different for each table or column (and each partition, if partitioned). It starts with a sampling rate to get approximately 5000 rows and examines the result based on statistical equations. This process is repeated with increased sampling rate for unsatisfactory results.
In general, the number of distinct values column statistics requires the highest sampling rate among the others, especially when each distinct value repeats a small number of times.
When you use a specific value for the sampling percentage, DBMS_STATS honors it except for when:
The result is less than 2500 rows (too small a sample) and
The specified percentage is more than the certain percentage.
Statistics for external tables are not collected by this procedure.
Exceptions
ORA-20000: Schema does not exist or insufficient privileges.
ORA-20001: Bad input value.
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 103-31 GATHER_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Mode values are:
|
|
|
Time, in minutes, to gather statistics. This parameter applies only when |
|
|
Identifier of the user statistics table where the statistics will be saved |
|
|
Optional identifier associated with the statistics saved in the |
|
|
Schema containing |
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.
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.
Syntax
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
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);
Parameters
Table 103-32 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. |
|
|
Accepts:
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.
The default is |
|
|
Degree of parallelism. The default for |
|
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
|
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. Use the constant |
|
|
User statistics table identifier describing where to save the current statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Gather statistics of table even if it is locked |
Usage Notes
This operation does not parallelize if the user does not have select privilege on the table being analyzed.
Exceptions
ORA-20000: Table does not exist or insufficient privileges.
ORA-20001: Bad input value.
This procedure generates object statistics from previously collected statistics of related objects. 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 103-33 GENERATE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of object |
|
|
Name of object |
|
|
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. |
Usage Notes
For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.
Exceptions
ORA-20000: Unsupported object type of object does not exist.
ORA-20001: Invalid option or invalid statistics.
These procedures gets all column-related information. In the form 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
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 103-34 GET_COLUMN_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table to which this column belongs |
|
|
Name of the column |
|
|
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 |
|
|
The user-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of distinct values |
|
|
Column density |
|
|
Number of |
|
|
Structure holding internal representation of column minimum, maximum, and histogram values |
|
|
Average length of the column (in bytes) |
|
|
Schema containing |
Exceptions
ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object.
These procedures get all index-related information. In the form 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
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, cachedblk OUT NUMBER, cachehit OUT NUMBER); 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, cachedblk OUT NUMBER, cachehit 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, cachedblk OUT NUMBER, cachehit OUT NUMBER);
Parameters
Table 103-35 GET_INDEX_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the index |
|
|
Name of the index partition for which to get the statistics. If the index is partitioned and if |
|
|
User statistics table identifier describing from where to retrieve the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
The user-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of rows in the index (partition) |
|
|
Number of leaf blocks in the index (partition) |
|
|
Number of distinct keys in the index (partition) |
|
|
Average integral number of leaf blocks in which each distinct key appears for this index (partition) |
|
|
Average integral number of data blocks in the table pointed to by a distinct key for this index (partition) |
|
|
Clustering factor for the index (partition) |
|
|
Height of the index (partition) |
|
|
Schema containing |
|
|
Guess quality for the index (partition) |
|
|
The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) |
|
|
The average cache hit ratio for the segment (index/table/index partition/table partition) |
Usage Notes
The Optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The total cost of the operation will be combined from the I/O cost of reading not cached blocks from disk, the CPU cost of getting cached blocks from the buffer cache, and the CPU cost of processing the data.
Oracle maintains cachedblk and cachehit at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit and a cachedblk for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.
The automatic maintenance algorithm for object caching statistics assumes that there is only one major workload for the system and adjusts statistics to this workload, ignoring other "minor" workloads. If this is not the case, you must use manual mode for maintaining object caching statistics.
The object caching statistics maintenance algorithm for auto mode prevents you from using statistics in the following situations
When not enough data has been analyzed, such as when an object has been recently create
When the system does not have one major workload resulting in averages not corresponding to real values.
Exceptions
ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object.
This function returns the default value of parameters of DBMS_STATS procedures.
Syntax
DBMS_STATS.GET_PARAM ( pname IN VARCHAR2) RETURN VARCHAR2;
Parameters
Exceptions
ORA-20001: Invalid input values
This function returns oldest timestamp where statistics history is available.Users cannot restore statistics to a timestamp older than this one.
Syntax
DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY RETURN TIMESTAMP WITH TIMEZONE;
This function returns the current retention value.
Syntax
DBMS_STATS.GET_STATS_HISTORY_RETENTION RETURN NUMBER;
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 103-37 GET_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Output is one of the following:
|
|
|
Date when statistics gathering started. If |
|
|
Date when statistics gathering stopped.
|
|
|
The parameter name to get, which can have one of the following values:
|
|
|
The parameter value to get |
|
|
Identifier of the user statistics table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary. |
|
|
Optional identifier associated with the statistics saved in the |
|
|
Schema containing |
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.
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, cachedblk OUT NUMBER, cachehit OUT NUMBER);
Parameters
Table 103-38 GET_TABLE_STATS Procedure Parameters
| 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 |
|
|
Number of rows in the table (partition). |
|
|
Number of blocks the table (partition) occupies. |
|
|
Average row length for the table (partition). |
|
|
Schema containing |
|
|
|
|
|
Usage Notes
The Optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The total cost of the operation will be combined from the I/O cost of reading not cached blocks from disk, the CPU cost of getting cached blocks from the buffer cache, and the CPU cost of processing the data.
Oracle maintains cachedblk and cachehit at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit and a cachedblk for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.
The automatic maintenance algorithm for object caching statistics assumes that there is only one major workload for the system and adjusts statistics to this workload, ignoring other "minor" workloads. If this is not the case, you must use manual mode for maintaining object caching statistics.
The object caching statistics maintenance algorithm for auto mode prevents you from using statistics in the following situations
When not enough data has been analyzed, such as when an object has been recently create
When the system does not have one major workload resulting in averages not corresponding to real values.
Exceptions
ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object
This procedure retrieves statistics for a particular column from the user statistics 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 to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-39 IMPORT_COLUMN_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the schema |
|
|
The name of the table to which this column belongs |
|
|
The name of the column |
|
|
The name of the table partition. If the table is partitioned and if |
|
|
The user statistics table identifier describing from where to retrieve the statistics |
|
|
The (optional) identifier to associate with these statistics within |
|
|
The schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
If set to |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
ORA-20005: Object statistics are locked.
This procedure retrieves statistics for all objects in the database from the user statistics 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 to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-40 IMPORT_DATABASE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to retrieve the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Overrides statistics locked at the object (table) level:
|
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
This procedure retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) from the user statistics table and stores them in the dictionary.
Syntax
DBMS_STATS.IMPORT_DICTIONARY_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-41 IMPORT_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to retrieve the statistics |
|
|
The (optional) identifier to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Overrides statistics lock at the object (table) level:
|
Usage Notes
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for fixed tables from the user statistics table(s) and stores them in the dictionary.
Syntax
DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-42 IMPORT_FIXED_OBJECTS_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to retrieve the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Overrides statistics lock:
|
Usage Notes
You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
ORA-20002: Bad user statistics table, may need to upgrade it.
http://usunnab06.us.oracle.com:80/servers/MifChecker/Out/Y10312_01.htm retrieves statistics for a particular index from the user statistics 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 to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-43 IMPORT_INDEX_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the index |
|
|
Name of the index partition. If the index is partitioned and if |
|
|
User statistics table identifier describing from where to retrieve the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Imports statistics even if index statistics are locked |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
ORA-20005: Object statistics are locked.
http://usunnab06.us.oracle.com:80/servers/MifChecker/Out/Y10312_01.htm retrieves statistics for all objects in the schema identified by ownname from the user statistics 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 DEFAULTto_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-44 IMPORT_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
User statistics table identifier describing from where to retrieve the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Overrides statistics locked at the object (table) level:
|
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
http://usunnab06.us.oracle.com:80/servers/MifChecker/Out/Y10312_01.htm retrieves system statistics from the user statistics 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 103-45 IMPORT_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Identifier of the user statistics table where the statistics will be retrieved |
|
|
Optional identifier associated with the statistics retrieved from the stattab |
|
|
Schema containing |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
ORA-20002: Bad user statistics table; may need to be upgraded.
ORA-20003: Unable to import system statistics.
http://usunnab06.us.oracle.com:80/servers/MifChecker/Out/Y10312_01.htm retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary. Cascade results in all index and column statistics 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 to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-46 IMPORT_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table |
|
|
Name of the table partition. If the table is partitioned and if |
|
|
User statistics table identifier describing from where to retrieve the statistics |
|
|
Identifier (optional) to associate with these statistics within |
|
|
If true, then column and index statistics for this table are also imported |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Imports statistics even if table statistics are locked |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values in the user statistics table.
This procedure locks the statistics of all tables of a schema.
Syntax
DBMS_STATS.LOCK_SCHEMA_STATS ( ownname VARCHAR2);
Parameters
Table 103-47 LOCK_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the schema to lock |
Usage Notes
See "Usage Notes" for LOCK_TABLE_STATS Procedure.
This procedure locks the statistics on the table.
Syntax
DBMS_STATS.LOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
Parameters
Table 103-48 LOCK_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the schema |
|
|
The name of the table |
Usage Notes
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.
The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.
This procedure either freezes the current set of the statistics or keeps the statistics empty (uncollected) to use Dynamic Sampling.
The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS procedures.
These procedures convert 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, dblvals DBLARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, fltvals FLTARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, numvals NUMARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, rawvals RAWARRAY);
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 103-49 PREPARE_COLUMN_VALUES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Number of values specified in 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 |
|
|
If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in |
Datatype-specific input parameters (use one) are shown in Table 103-50.
Table 103-50 Datatype-Specific Input Parameters
| Type | Description |
|---|---|
|
|
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 |
|
|
The array of values when the column type is date-based |
|
|
The array of values when the column type is double-based |
|
|
The array of values when the column type is float-based |
|
|
The array of values when the column type is numeric-based |
|
|
The array of values when the column type is |
|
|
The minimum and maximum values when the column type is national character set based. No histogram information can be provided for a column of this type. If the datatype is fixed |
|
|
The minimum and maximum values when the column type is |
Output Parameters
Table 103-51 PREPARE_COLUMN_VALUES Procedure Output Parameters
| Parameter | Description |
|---|---|
|
|
Internal representation of the minimum suitable for use in a call to |
|
|
Internal representation of the maximum suitable for use in a call to |
|
|
Array suitable for use in a call to |
|
|
Array suitable for use in a call to |
Exceptions
ORA-20001: Invalid or inconsistent input values.
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_NVARCHAR2 ( srec IN OUT StatRec, nvmin NVARCHAR2, nvmax NVARCHAR2);
Pragmas
pragma restrict_references(prepare_column_values_nvarchar, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 103-52 PREPARE_COLUMN_VALUES_NVARCHAR2 Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Number of values specified in 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 |
|
|
If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in |
Datatype-specific input parameters (use one) are shown in Table 103-50.
Table 103-53 Datatype-Specific Input Parameters
| Type | Description |
|---|---|
|
|
The minimum and maximum values when the column type is national character set based. No histogram information can be provided for a column of this type. If the datatype is fixed |
Output Parameters
Table 103-54 PREPARE_COLUMN_VALUES Procedure Output Parameters
| Parameter | Description |
|---|---|
|
|
Internal representation of the minimum suitable for use in a call to |
|
|
Internal representation of the maximum suitable for use in a call to |
|
|
Array suitable for use in a call to |
|
|
Array suitable for use in a call to |
Exceptions
ORA-20001: Invalid or inconsistent input values.
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_ROWID ( srec IN OUT StatRec, rwmin ROWID, rwmax ROWID);
Pragmas
pragma restrict_references(prepare_column_values_rowid, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 103-55 PREPARE_COLUMN_VALUES_ROWID Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Number of values specified in 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 |
|
|
If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in |
Datatype-specific input parameters (use one) are shown in Table 103-50.
Table 103-56 Datatype-Specific Input Parameters
| Type | Description |
|---|---|
|
|
The minimum and maximum values when the column type is |
Output Parameters
Table 103-57 PREPARE_COLUMN_VALUES Procedure Output Parameters
| Parameter | Description |
|---|---|
|
|
Internal representation of the minimum suitable for use in a call to |
|
|
Internal representation of the maximum suitable for use in a call to |
|
|
Array suitable for use in a call to |
|
|
Array suitable for use in a call to |
Exceptions
ORA-20001: Invalid or inconsistent input values.
This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
Syntax
DBMS_STATS.PURGE_STATS(
before_timestamp TIMESTAMP WITH TIME ZONE);
Parameters
Table 103-58 PURGE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Versions of statistics saved before this timestamp are purged. If |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values.
This procedure resets the default values of all parameters to Oracle recommended values.
Syntax
DBMS_STATS.RESET_PARAM_DEFAULTS;
This procedure restores statistics of all tables of the database as of a specified timestamp (as_of_timestamp).
Syntax
DBMS_STATS.RESTORE_DATABSE_STATS(
as_of_timestamp TIMESTAMP WITH TIME ZONE,
force BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')));
Parameters
Table 103-59 RESTORE_DATABASE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The timestamp to which to restore statistics |
|
|
Restores statistics even if their statistics are locked |
|
|
Does not invalidate the dependent cursors if set to |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values.
ORA-20006: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all dictionary tables (tables of 'SYS', 'SYSTEM' and RDBMS component schemas) as of a specified timestamp (as_of_timestamp).
Syntax
DBMS_STATS.RESTORE_DICTIONARY_STATS(
as_of_timestamp TIMESTAMP WITH TIME ZONE,
force BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')));
Parameters
Table 103-60 RESTORE_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The timestamp to which to restore statistics |
|
|
Restores statistics even if their statistics are locked |
|
|
Does not invalidate the dependent cursors if set to |
Usage Notes
To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values.
ORA-20006: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all fixed tables as of a specified timestamp (as_of_timestamp).
Syntax
DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(
as_of_timestamp TIMESTAMP WITH TIME ZONE,
force BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')));
Parameters
Table 103-61 RESTORE_FIXED_OBJECTS_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The timestamp to which to restore statistics |
|
|
Restores statistics even if their statistics are locked |
|
|
Does not invalidate the dependent cursors if set to |
Usage Notes
To run this procedure, you must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values.
ORA-20006: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all tables of a schema as of a specified timestamp (as_of_timestamp).
Syntax
DBMS_STATS.RESTORE_SCHEMA_STATS(
ownname VARCHAR2,
as_of_timestamp TIMESTAMP WITH TIME ZONE,
force BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')));
Parameters
Table 103-62 RESTORE_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The schema of the tables for which the statistics are to be restored |
|
|
The timestamp to which to restore statistics |
|
|
Restores statistics even if their statistics are locked |
|
|
Does not invalidate the dependent cursors if set to |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values.
ORA-20006: Unable to restore statistics, statistics history not available.
This procedure restores system statistics as of a specified timestamp (as_of_timestamp).
Syntax
DBMS_STATS.RESTORE_SCHEMA_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE);
Parameters
Table 103-63 RESTORE_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The timestamp to which to restore statistics |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values.
ORA-20006: Unable to restore statistics, statistics history not available.
This procedure restores statistics of a table as of a specified timestamp (as_of_timestamp). The procedure will restore statistics of associated indexes and columns as well. If the table statistics were locked at the specified timestamp the procedure will lock the statistics. The procedure will not restore user defined statistics.
Syntax
DBMS_STATS.RESTORE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
as_of_timestamp TIMESTAMP WITH TIME ZONE,
restore_cluster_index BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')));
Parameters
Table 103-64 RESTORE_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The schema of the table for which the statistics are to be restored |
|
|
The table name |
|
|
The timestamp to which to restore statistics |
|
|
If the table is part of a cluster, restore statistics of the cluster index if set to |
|
|
Restores statistics even if the table statistics are locked. If the table statistics were not locked at the specified timestamp, it unlocks the statistics. |
|
|
Does not invalidate the dependent cursors if set to |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent values.
ORA-20006: Unable to restore statistics, statistics history not available.
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
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 to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force 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 to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-65 SET_COLUMN_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema. |
|
|
Name of the table to which this column belongs. |
|
|
Name of the column. |
|
|
Name of the table partition in which to store the statistics. If the table is partitioned and |
|
|
User statistics table identifier describing where to store the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
The user-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of distinct values |
|
|
Column density. If this value is |
|
|
Number of |
|
|
|
|
|
Average length for the column (in bytes) |
|
|
For internal Oracle use (should be left as |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Sets the values even if statistics of the column are locked |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or inconsistent input values.
ORA-20005: Object statistics are locked.
These procedures set 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
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 to_no_invalidate_type(
get_param('NO_INVALIDATE')),
guessq NUMBER DEFAULT NULL,
cachedblk NUMBER DEFAULT NULL,
cachehit NUMBER DEFUALT NULL,
force BOOLEAN DEFAULT FALSE);
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 to_no_invalidate_type(
get_param('NO_INVALIDATE')),
cachedblk NUMBER DEFAULT NULL,
cachehit NUMBER DEFUALT NULL,
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-66 SET_INDEX_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the index |
|
|
Name of the index partition in which to store the statistics. If the index is partitioned and if |
|
|
User statistics table identifier describing where to store the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
The user-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of rows in the index (partition) |
|
|
Number of leaf blocks in the index (partition) |
|
|
Number of distinct keys in the index (partition) |
|
|
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 |
|
|
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 |
|
|
See |
|
|
Height of the index (partition) |
|
|
For internal Oracle use (should be left as |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Guess quality. See the |
|
|
The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) |
|
|
The average cache hit ratio for the segment (index/table/index partition/table partition) |
|
|
Sets the values even if statistics of the index are locked |
Usage Notes
The Optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The total cost of the operation will be combined from the I/O cost of reading not cached blocks from disk, the CPU cost of getting cached blocks from the buffer cache, and the CPU cost of processing the data.
Oracle maintains cachedblk and cachehit at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit and a cachedblk for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.
The automatic maintenance algorithm for object caching statistics assumes that there is only one major workload for the system and adjusts statistics to this workload, ignoring other "minor" workloads. If this is not the case, you must use manual mode for maintaining object caching statistics.
The object caching statistics maintenance algorithm for auto mode prevents you from using statistics in the following situations
When not enough data has been analyzed, such as when an object has been recently create
When the system does not have one major workload resulting in averages not corresponding to real values.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid input value.
ORA-20005: Object statistics are locked.
This procedure sets default values for parameters of DBMS_STATS procedures. You can use the GET_PARAM Function to get the current default value of a parameter.
Syntax
DBMS_STATS.SET_PARAM ( pname IN VARCHAR2, pval IN VARCHAR2);
Parameters
Table 103-67 SET_PARAM Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The parameter name The default value for following parameters can be set.
|
|
|
The parameter value. If
|
Usage Notes
To run this procedure, you must have the SYSDBA or both the ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
Note that both arguments are of type VARCHAR2 and the values need to be enclosed in quotes.
Note also the difference between NULL and 'NULL':
When NULL is unquoted, this sets the parameter to the value Oracle recommends.
In the case of the quoted 'NULL', this sets the value of the parameter to NULL.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid or illegal input value.
Examples
DBMS_STATS.SET_PARAM('CASCADE','DBMS_STATS.AUTO_CASCADE');
DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','5');
DBMS_STATS.SET_PARAM('DEGREE','NULL');
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 103-68 SET_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The parameter name to get, which can have one of the following values:
|
|
|
Parameter value to get |
|
|
Identifier of the user statistics table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary. |
|
|
Optional identifier associated with the statistics saved in the stattab |
|
|
Schema containing |
|
|
The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) |
|
|
The average cache hit ratio for the segment (index/table/index partition/table partition) |
Usage Notes
The Optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The total cost of the operation will be combined from the I/O cost of reading not cached blocks from disk, the CPU cost of getting cached blocks from the buffer cache, and the CPU cost of processing the data.
Oracle maintains cachedblk and cachehit at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit and a cachedblk for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.
The automatic maintenance algorithm for object caching statistics assumes that there is only one major workload for the system and adjusts statistics to this workload, ignoring other "minor" workloads. If this is not the case, you must use manual mode for maintaining object caching statistics.
The object caching statistics maintenance algorithm for auto mode prevents you from using statistics in the following situations
When not enough data has been analyzed, such as when an object has been recently create
When the system does not have one major workload resulting in averages not corresponding to real values.
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.
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 to_no_invalidate_type (
get_param('NO_INVALIDATE')),
cachedblk NUMBER DEFAULT NULL,
cachehit NUMBER DEFUALT NULL,
force BOOLEAN DEFAULT FALSE);
Parameters
Table 103-69 SET_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table |
|
|
Name of the table partition in which to store the statistics. If the table is partitioned and |
|
|
User statistics table identifier describing where to store the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Number of rows in the table (partition) |
|
|
Number of blocks the table (partition) occupies |
|
|
Average row length for the table (partition) |
|
|
For internal Oracle use (should be left as |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) |
|
|
The average cache hit ratio for the segment (index/table/index partition/table partition) |
|
|
Sets the values even if statistics of the table are locked |
Usage Notes
The Optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The total cost of the operation will be combined from the I/O cost of reading not cached blocks from disk, the CPU cost of getting cached blocks from the buffer cache, and the CPU cost of processing the data.
Oracle maintains cachedblk and cachehit at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit and a cachedblk for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.
The automatic maintenance algorithm for object caching statistics assumes that there is only one major workload for the system and adjusts statistics to this workload, ignoring other "minor" workloads. If this is not the case, you must use manual mode for maintaining object caching statistics.
The object caching statistics maintenance algorithm for auto mode prevents you from using statistics in the following situations
When not enough data has been analyzed, such as when an object has been recently create
When the system does not have one major workload resulting in averages not corresponding to real values.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20001: Invalid input value.
ORA-20005: Object statistics are locked.
This procedure unlocks the statistics on all the tables in schema.
Syntax
DBMS_STATS.UNLOCK_SCHEMA_STATS ( ownname VARCHAR2);
Parameters
Table 103-70 UNLOCK_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the schema |
Usage Notes
When statistics on a table is 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.
The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.
This procedure unlocks the statistics on the table.
Syntax
DBMS_STATS.UNLOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
Parameters
Table 103-71 UNLOCK_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the schema |
|
|
The name of the table |
Usage Notes
When statistics on a table is 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.
The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.
This procedure upgrades a user statistics table from an older version.
Syntax
DBMS_STATS.UPGRADE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
Parameters
Table 103-72 UPGRADE_STAT_TABLE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table |
Exceptions
ORA-20000: Unable to upgrade table.
