93
DBMS_STATS

With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.

This chapter contains the following topics:

• Using DBMS_STATS
  • Overview
  • Types
  • Constants
  • Operational Notes
  • Deprecated Subprograms
  • Examples
• Summary of DBMS_STATS Subprograms

---

Using DBMS_STATS

• Overview
• Types
• Constants
• Operational Notes
• Deprecated Subprograms
• Examples

---

Overview

The statistics to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if the DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.

Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel

---

Types

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;

---

Constants

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

---

Operational Notes

The DBMS_STATS subprograms perform the following general operations:

• Setting, Getting or Deleting Statistics
• Deleting Statistics
• Transferring Statistics
• Gathering Optimizer Statistics
• Locking or Unlocking Statistics
• Restoring and Purging Statistics History
• Setting, Getting or Deleting Statistics

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.

Setting, Getting or Deleting Statistics

When a DBMS_STATS subprogram modifies or deletes the statistics for an object, all the dependent cursors are invalidated by default and corresponding statements are subject to recompilation next time so that the new statistics have immediate effects. This behavior can be altered with the no_invalidate argument.

Use the following procedures to store and retrieve individual column-related, index-related, and table-related statistics:

PREPARE_COLUMN_VALUES
SET_COLUMN_STATS
SET_INDEX_STATS
SET_SYSTEM_STATS
SET_TABLE_STATS

In the special versions of the SET_*_STATS procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or external statistics table:

• 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.

CONVERT_RAW_VALUE
GET_COLUMN_STATS
GET_INDEX_STATS
GET_SYSTEM_STATS
GET_TABLE_STATS

The special versions of the GET_*_STATS procedures return user-defined statistics and the statistics type owner and name as OUT arguments corresponding to the schema object specified. If user-defined statistics are not collected, NULL values are returned.

Deleting Statistics

The DELETE_* procedures delete both user-defined statistics and the standard statistics for the given schema object.

DELETE_COLUMN_STATS
DELETE_DATABASE_STATS
DELETE_DICTIONARY_STATS
DELETE_FIXED_OBJECTS_STATS
DELETE_INDEX_STATS
DELETE_SCHEMA_STATS 
DELETE_SYSTEM_STATS
DELETE_TABLE_STATS

Transferring Statistics

Use the following procedures to transfer statistics from the dictionary to a user statistics table (export_*) and from a user statistics table to the dictionary (import_*):

CREATE_STAT_TABLE
DROP_STAT_TABLE

CREATE_STAT_TABLE can hold user-defined statistics and the statistics type object number.

EXPORT_COLUMN_STATS
EXPORT_DATABASE_STATS
EXPORT_DICTIONARY_STATS
EXPORT_FIXED_OBJECTS_STATS
EXPORT_INDEX_STATS
EXPORT_SCHEMA_STATS
EXPORT_SYSTEM_STATS
EXPORT_TABLE_STATS

IMPORT_COLUMN_STATS
IMPORT_DICTIONARY_STATS
IMPORT_FIXED_OBJECTS_STATS
IMPORT_INDEX_STATS
IMPORT_SCHEMA_STATS
IMPORT_SYSTEM_STATS
IMPORT_TABLE_STATS

The IMPORT_* procedures retrieve statistics, including user-defined statistics, from the stattab table and store them in the dictionary. Because the SET_*_STATS and GET_*_STATS interfaces are supported for user-defined statistics, user-defined statistics can be copied to another database using this interface.

Gathering Optimizer Statistics

Use the following procedures to gather certain classes of optimizer statistics, with possible performance improvements over the ANALYZE command:

GATHER_DATABASE_STATS
GATHER_DICTIONARY_STATS
GATHER_FIXED_OBJECTS_STATS
GATHER_INDEX_STATS
GATHER_SCHEMA_STATS
GATHER_SYSTEM_STATS
GATHER_TABLE_STATS

The GATHER_* procedures also collect user-defined statistics for columns and domain indexes.

The statown, stattab, and statid parameters instruct the package to back up current statistics in the specified table before gathering new statistics.

Oracle also provides the following procedure for generating statistics for derived objects when you have sufficient statistics on related objects:

GENERATE_STATS

Locking or Unlocking Statistics

Use the following procedures to lock and unlock statistics on objects.

LOCK_TABLE_STATS
LOCK_SCHEMA_STATS

UNLOCK_TABLE_STATS
UNLOCK_SCHEMA_STATS

The LOCK* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

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.

RESTORE_TABLE_STATS
RESTORE_SCHEMA_STATS
RESTORE_DATABASE_STATS
RESTORE_FIXED_OBJECTS_STATS
RESTORE_DICTIONARY_STATS
RESTORE_SYSTEM_STATS

Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure.

The other DBMS_STATS procedures related to restoring statistics are:

• PURGE_STATS: This procedure lets you manually purge old versions beyond a time stamp.
• GET_STATS_HISTORY_RENTENTION: This function gets the current statistics history retention value.
• GET_STATS_HISTORY_AVAILABILTY: 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.

User-Defined Statistics

DBMS_STATS supports operations on user-defined statistics. When a domain index or column is associated with a statistics type (using the associate statement), operations on the index or column manipulate user-defined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the GATHER_INDEX_STATS interface invokes the user-defined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate user-defined statistics.

SET and GET operations for user-defined statistics are also supported using a special version of the SET and GET interfaces for columns and indexes.

---

Deprecated Subprograms

The following subprograms are obsolete with Release 10g:

• ALTER_DATABASE_TAB_MONITORING Procedure
• ALTER_SCHEMA_TAB_MONITORING Procedure

In earlier releases, you could use these subprograms to operate on statistics. These subprograms are now non-operational because Oracle performs their functions automatically.

---

Examples

• Saving Original Statistics and Gathering New Statistics
• Gathering Daytime System Statistics

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;

---

Summary of DBMS_STATS Subprograms

Table 93-1  DBMS_STATS Package Subprograms

Subprogram	Description
ALTER_DATABASE_TAB_MONITORING Procedure	Enables or disables the DML monitoring feature of all tables in the database, except for snapshot logs and the tables, which monitoring does not support [See Deprecated Subprograms]
ALTER_SCHEMA_TAB_MONITORING Procedure	Enables or disables the DML monitoring feature of all tables in the schema, except for snapshot logs and the tables, which monitoring does not support [See Deprecated Subprograms]
ALTER_STATS_HISTORY_RETENTION Procedure	Changes the statistics history retention value
CONVERT_RAW_VALUE Procedures	Convert the internal representation of a minimum or maximum value into a datatype-specific value
CONVERT_RAW_VALUE_NVARCHAR Procedure	Convert the internal representation of a minimum or maximum value into a datatype-specific value
CONVERT_RAW_VALUE_ROWID Procedure	Convert the internal representation of a minimum or maximum value into a datatype-specific value
CREATE_STAT_TABLE Procedure	Creates a table with name stattab in ownname's schema which is capable of holding statistics
DELETE_COLUMN_STATS Procedure	Deletes column-related statistics
DELETE_DATABASE_STATS Procedure	Deletes statistics for the entire database
DELETE_DICTIONARY_STATS Procedure	Deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas)
DELETE_FIXED_OBJECTS_STATS Procedure	Deletes statistics of all fixed tables
DELETE_INDEX_STATS Procedure	Deletes index-related statistics
DELETE_SCHEMA_STATS Procedure	Deletes schema-related statistics
DELETE_SYSTEM_STATS Procedure	Deletes system statistics
DELETE_TABLE_STATS Procedure	Deletes table-related statistics
DROP_STAT_TABLE Procedure	Drops a user statistics table created by CREATE_STAT_TABLE
EXPORT_COLUMN_STATS Procedure	Retrieves statistics for a particular column and stores them in the user statistics table identified by stattab
EXPORT_DATABASE_STATS Procedure	Retrieves statistics for all objects in the database and stores them in the user statistics table identified by statown.stattab
EXPORT_DICTIONARY_STATS Procedure	Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) and stores them in the user statistics table identified by stattab
EXPORT_FIXED_OBJECTS_STATS Procedure	Retrieves statistics for fixed tables and stores them in the user statistics table identified by stattab
EXPORT_INDEX_STATS Procedure	Retrieves statistics for a particular index and stores them in the user statistics table identified by stattab
EXPORT_SCHEMA_STATS Procedure	Retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics table identified by stattab
EXPORT_SYSTEM_STATS Procedure	Retrieves system statistics and stores them in the user statistics table
EXPORT_TABLE_STATS Procedure	Retrieves statistics for a particular table and stores them in the user statistics table
FLUSH_DATABASE_MONITORING_INFO Procedure	Flushes in-memory monitoring information for all the tables to the dictionary
GATHER_DATABASE_STATS Procedures	Gathers statistics for all objects in the database
GATHER_DICTIONARY_STATS Procedure	Gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components
GATHER_FIXED_OBJECTS_STATS Procedure	Gathers statistics of fixed objects
GATHER_INDEX_STATS Procedure	Gathers index statistics
GATHER_SCHEMA_STATS Procedures	Gathers statistics for all objects in a schema
GATHER_SYSTEM_STATS Procedure	Gathers system statistics
GATHER_TABLE_STATS Procedure	Gathers table and column (and index) statistics
GENERATE_STATS Procedure	Generates object statistics from previously collected statistics of related objects
GET_COLUMN_STATS Procedures	Gets all column-related information
GET_INDEX_STATS Procedures	Gets all index-related information
GET_PARAM Function	Gets the default value of parameters of DBMS_STATS procedures
GET_STATS_HISTORY_AVAILABILITY Function	Gets the oldest timestamp where statistics history is available
GET_STATS_HISTORY_RETENTION Function	Returns the current retention value
GET_SYSTEM_STATS Procedure	Gets system statistics from stattab, or from the dictionary if stattab is NULL
GET_TABLE_STATS Procedure	Gets all table-related information
IMPORT_COLUMN_STATS Procedure	Retrieves statistics for a particular column from the user statistics table identified by stattab and stores them in the dictionary
"IMPORT_DATABASE_STATS Procedure"	Retrieves statistics for all objects in the database from the user statistics table and stores them in the dictionary
"IMPORT_DICTIONARY_STATS Procedure"	Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) from the user statistics table and stores them in the dictionary
IMPORT_FIXED_OBJECTS_STATS Procedure	Retrieves statistics for fixed tables from the user statistics table identified by stattab and stores them in the dictionary
IMPORT_INDEX_STATS Procedure	Retrieves statistics for a particular index from the user statistics table identified by stattab and stores them in the dictionary
IMPORT_SCHEMA_STATS Procedure	Retrieves statistics for all objects in the schema identified by ownname from the user statistics table and stores them in the dictionary
IMPORT_SYSTEM_STATS Procedure	Retrieves system statistics from the user statistics table and stores them in the dictionary
IMPORT_TABLE_STATS Procedure	Retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary
LOCK_SCHEMA_STATS Procedure	Locks the statistics of all tables of a schema
LOCK_TABLE_STATS Procedure	Locks the statistics on the table
PREPARE_COLUMN_VALUES 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
PREPARE_COLUMN_VALUES_NVARCHAR2 Procedure	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
PREPARE_COLUMN_VALUES_ROWID Procedure	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
PURGE_STATS Procedure	Purges old versions of statistics saved in the dictionary
RESTORE_DATBASE_STATS Procedure	Restores statistics of all tables of the database as of a specified timestamp
RESTORE_DICTIONARY_STATS Procedure	Restores statistics of all dictionary tables (tables of 'SYS', 'SYSTEM' and RDBMS component schemas) as of a specified timestamp
RESTORE_FIXED_OBJECTS_STATS Procedure	Restores statistics of all fixed tables as of a specified timestamp
RESTORE_SCHEMA_STATS Procedure	Restores statistics of all tables of a schema as of a specified timestamp
RESTORE_SYSTEM_STATS Procedure	Restores statistics of all tables of a schema as of a specified timestamp
RESTORE_TABLE_STATS Procedure	Restores statistics of a table as of a specified timestamp (as_of_timestamp), as well as statistics of associated indexes and columns
SET_COLUMN_STATS Procedures	Sets column-related information
SET_INDEX_STATS Procedures	Sets index-related information
SET_PARAM Procedure	Sets default values for parameters of DBMS_STATS procedures
SET_SYSTEM_STATS Procedure	Sets system statistics
SET_TABLE_STATS Procedure	Sets table-related information
UNLOCK_SCHEMA_STATS Procedure	Unlocks the statistics on all the table in a schema
UNLOCK_TABLE_STATS Procedure	Unlocks the statistics on the table
UPGRADE_STAT_TABLE Procedure	Upgrades user statistics on an older table

---

ALTER_DATABASE_TAB_MONITORING Procedure

This procedure enables or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Using this procedure is equivalent to issuing ALTER TABLE...MONITORING (or NOMONITORING) individually.

Syntax

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

Parameters

Table 93-2  ALTER_DATABASE_TAB_MONITORING Procedure Parameters

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

Usage Notes

Exceptions

ORA-20000: Insufficient privileges.

---

ALTER_SCHEMA_TAB_MONITORING Procedure

This procedure enables or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Using this procedure is equivalent to issuing ALTER TABLE...MONITORING (or NOMONITORING) individually.

Syntax

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

Parameters

Table 93-3  ALTER_SCHEMA_TAB_MONITORING Procedure Parameters

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

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.

---

ALTER_STATS_HISTORY_RETENTION Procedure

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 93-4  ALTER_STATS_HISTORY_RETENTION Procedure Parameters

Parameter

Description

retention

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: 0 - old statistics are never saved. The automatic purge will delete all statistics history 1 - statistics history is never purged by automatic purge. NULL - change statistics history retention to default value

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.

---

CONVERT_RAW_VALUE Procedures

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 93-5  CONVERT_RAW_VALUE Procedure Parameters

Parameter	Description
rawval	The raw representation of a column minimum or maximum datatype-specific output parameters.
resval	The converted, type-specific value.

---

CONVERT_RAW_VALUE_NVARCHAR Procedure

This procedure converts the internal representation of a minimum or maximum value into a datatype-specific value. The minval and maxval fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.

Syntax

DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR (
   rawval     RAW, 
   resval OUT NVARCHAR2);

Pragmas

pragma restrict_references(convert_raw_value_nvarchar, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 93-6  CONVERT_RAW_VALUE_NVARCHAR Procedure Parameters

Parameter	Description
rawval	The raw representation of a column minimum or maximum datatype-specific output parameters.
resval	The converted, type-specific value.

---

CONVERT_RAW_VALUE_ROWID Procedure

This procedure converts the internal representation of a minimum or maximum value into a datatype-specific value. The minval and maxval fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.

Syntax

DBMS_STATS.CONVERT_RAW_VALUE_ROWID (
   rawval     RAW, 
   resval OUT ROWID);

Pragmas

pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 93-7  CONVERT_RAW_VALUE_ROWID Procedure Parameters

Parameter	Description
rawval	The raw representation of a column minimum or maximum datatype-specific output parameters.
resval	The converted, type-specific value.

---

CREATE_STAT_TABLE Procedure

This procedure creates a table with name stattab in ownname's schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.

Syntax

DBMS_STATS.CREATE_STAT_TABLE (
   ownname  VARCHAR2, 
   stattab  VARCHAR2,
   tblspace VARCHAR2 DEFAULT NULL);

Parameters

Table 93-8  CREATE_STAT_TABLE Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	Name of the table to create. This value should be passed as the stattab parameter to other procedures when the user does not want to modify the dictionary statistics directly.
tblspace	Tablespace in which to create the 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.

---

DELETE_COLUMN_STATS Procedure

This procedure deletes column-related statistics.

Syntax

DBMS_STATS.DELETE_COLUMN_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2, 
   colname       VARCHAR2, 
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   cascade_parts BOOLEAN  DEFAULT TRUE,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force         BOOLEAN DEFAULT FALSE);

Parameters

Table 93-9  DELETE_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition for which to delete the statistics. If the table is partitioned and if partname is NULL, then global column statistics are deleted.
stattab	User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
cascade_parts	If the table is partitioned and if partname is NULL, then setting this to true causes the deletion of statistics for this column for all underlying partitions as well.
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	When value of this argument is TRUE, deletes column statistics even if locked.

Exceptions

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

ORA-20005: Object statistics are locked.

---

DELETE_DATABASE_STATS Procedure

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 93-10  DELETE_DATABASE_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
statown	Schema containing stattab. If stattab is not NULL and if statown is NULL, then it is assumed that every schema in the database contains a user statistics table with the name stattab.
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	When the value of this argument is TRUE, deletes statistics of tables in a database even if they are locked.

Exceptions

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

---

DELETE_DICTIONARY_STATS Procedure

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 93-11  DELETE_DICTIONARY_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
statown	Schema containing stattab. If stattab is not NULL and if statown is NULL, then it is assumed that every schema in the database contains a user statistics table with the name stattab.
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.)Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	When the value of this argument is TRUE, deletes statistics of tables in a database even if they are locked.

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.

---

DELETE_FIXED_OBJECTS_STATS Procedure

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 93-12  DELETE_FIXED_OBJECTS_STATS Procedure Parameters

Parameter	Description
stattab	The user statistics table identifier describing from where to delete the current statistics. If stattab is NULL, the statistics will be deleted directly in the dictionary.
statid	The (optional) identifier to associate with these statistics within stattab. This only applies if stattab is not NULL.
statown	The schema containing stattab (if different from ownname).
no_invalidate	Does not invalidate the dependent cursors if set to TRUE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	Ignores the statistics lock on objects and deletes the statistics if set to TRUE.

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.

---

DELETE_INDEX_STATS Procedure

This procedure deletes index-related statistics.

Syntax

DBMS_STATS.DELETE_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2,
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   cascade_parts    BOOLEAN  DEFAULT TRUE,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

Parameters

Table 93-13  DELETE_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition for which to delete the statistics. If the index is partitioned and if partname is NULL, then index statistics are deleted at the global level.
stattab	User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
cascade_parts	If the index is partitioned and if partname is NULL, then setting this to TRUE causes the deletion of statistics for this index for all underlying partitions as well.
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	When value of this argument is TRUE, deletes index statistics even if locked.

Exceptions

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

ORA-20005: Object statistics are locked.

---

DELETE_SCHEMA_STATS Procedure

This procedure deletes statistics for an entire schema.

Syntax

DBMS_STATS.DELETE_SCHEMA_STATS (
   ownname          VARCHAR2, 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

Parameters

Table 93-14  DELETE_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	When value of this argument is TRUE, deletes statistics of tables in a schema even if locked.

Exceptions

ORA-20000: Object does not exist or insufficient privileges

---

DELETE_SYSTEM_STATS Procedure

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 93-15  DELETE_SYSTEM_STATS Procedure Parameters

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

Exceptions

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

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

---

DELETE_TABLE_STATS Procedure

This procedure deletes table-related statistics.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   cascade_parts    BOOLEAN  DEFAULT TRUE, 
   cascade_columns  BOOLEAN  DEFAULT TRUE,
   cascade_indexes  BOOLEAN  DEFAULT TRUE,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

Parameters

Table 93-16  DELETE_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
partname	Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.
stattab	User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
cascade_parts	If the table is partitioned and if partname is NULL, then setting this to TRUE causes the deletion of statistics for this table for all underlying partitions as well.
cascade_columns	Indicates that DELETE_COLUMN_STATS should be called for all underlying columns (passing the cascade_parts parameter).
cascade_indexes	Indicates that DELETE_INDEX_STATS should be called for all underlying indexes (passing the cascade_parts parameter).
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	When value of this argument is TRUE, deletes table statistics even if locked.

Exceptions

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

ORA-20005: Object statistics are locked.

---

DROP_STAT_TABLE Procedure

This procedure drops a user statistics table.

Syntax

DBMS_STATS.DROP_STAT_TABLE (
   ownname VARCHAR2, 
   stattab VARCHAR2);

Parameters

Table 93-17 DROP_STAT_TABLE Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User statistics table identifier.

Exceptions

ORA-20000: Table does not exists or insufficient privileges.

---

EXPORT_COLUMN_STATS Procedure

This procedure retrieves statistics for a particular column and stores them in the user 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 93-18  EXPORT_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition column statistics are exported.
stattab	User statistics table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).

Exceptions

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

---

EXPORT_DATABASE_STATS Procedure

This procedure retrieves statistics for all objects in the database and stores them in the user statistics tables identified by statown.stattab.

Syntax

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

Parameters

Table 93-19  EXPORT_DATABASE_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing where to store the statistics
statid	Identifier (optional) to associate with these statistics within stattab
statown	Schema containing stattab. If statown is NULL, then it is assumed that every schema in the database contains a user statistics table with the name stattab.

Exceptions

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

---

EXPORT_DICTIONARY_STATS Procedure

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 93-20  EXPORT_DICTIONARY_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing where to store the statistics
statid	Identifier (optional) to associate with these statistics within stattab
statown	Schema containing stattab. If statown is NULL, then it is assumed that every schema in the database contains a user statistics table with the name stattab.

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.

---

EXPORT_FIXED_OBJECTS_STATS Procedure

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 93-21  EXPORT_FIXED_OBJECTS_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different from ownname).

Exceptions

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

ORA-20002: Bad user statistics table, may need to upgrade it.

---

EXPORT_INDEX_STATS Procedure

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 93-22  EXPORT_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition. If the index is partitioned and if partname is NULL, then global and partition index statistics are exported.
stattab	User statistics table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).

Exceptions

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

---

EXPORT_SCHEMA_STATS Procedure

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 93-23  EXPORT_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User statistics table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).

Exceptions

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

---

EXPORT_SYSTEM_STATS Procedure

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

Syntax

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

Parameters

Table 93-24  EXPORT_SYSTEM_STATS Procedure Parameters

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

Exceptions

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

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

ORA-20003: Unable to export system statistics.

---

EXPORT_TABLE_STATS Procedure

This procedure retrieves statistics for a particular table and stores them in the user 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 93-25  EXPORT_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table.
partname	Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported.
stattab	User statistics table identifier describing where to store the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
cascade	If true, then column and index statistics for this table are also exported.
statown	Schema containing stattab (if different than ownname).

Exceptions

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

---

FLUSH_DATABASE_MONITORING_INFO Procedure

This procedure flushes in-memory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS 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.

---

GATHER_DATABASE_STATS Procedures

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 'AUTO', 
   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 FALSE,
   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 'AUTO', 
   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 FALSE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')));

Parameters

Table 93-26  GATHER_DATABASE_STATS Procedure Parameters

Parameter	Description
estimate_percent	Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure...
block_sample	Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt	Accepts: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...], where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY} integer--Number of histogram buckets. Must be in the range [1,254]. REPEAT--Collects histograms only on the columns that already have histograms. AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns. The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.
degree	Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type, and collects the global, partition level and subpartition level statistics if the subpartitioning method is LIST, and the global and partition level only otherwise. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics.
cascade	Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the database in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_PARAM Procedure.
stattab	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.
statid	Identifier (optional) to associate with these statistics within stattab.
options	Further specification of which objects to gather statistics for: GATHER: Gathers statistics on all objects in the schema. GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects. GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale. GATHER EMPTY: Gathers statistics on objects which currently have no statistics. Return a list of objects found to have no statistics. LIST AUTO: Returns a list of objects to be processed with GATHER AUTO. LIST STALE: Returns a list of stale objects as determined by looking at the *_tab_modifications views. LIST EMPTY: Returns a list of objects which currently have no statistics.
objlist	List of objects found to be stale or empty.
statown	Schema containing stattab (if different than ownname).
gather_sys	Gathers statistics on the objects owned by the 'SYS' user.
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the objects for which statistics are being gathered. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Cannot be used with the cascade option. This option has no effect when an index is picked up as a target because GATHER_INDEX_STATS does not support this option. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

Usage Notes

Statistics for external tables are not collected by this procedure.

Exceptions

ORA-20000: Insufficient privileges.

ORA-20001: Bad input value.

---

GATHER_DICTIONARY_STATS Procedure

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 'AUTO',
   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')));

Parameters

Table 93-27  GATHER_DICTIONARY_STATS Procedure Parameters

Parameter	Description
comp_id	The component id of the schema to analyze (NULL will result in analyzing schemas of all RDBMS components).Please refer to comp_id column of DBA_REGISTRY view. The procedure always gather statistics on 'SYS' and 'SYSTEM' schemas regardless of this argument.
estimate_percent	Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.
block_sample	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.
method_opt	The method options of the following format: size_clause := SIZE [integer | auto | skewonly | repeat] where integer is between 1 and 254 FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]. This value will be passed to all of the individual tables. The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.
degree	Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure. NULL means use of table default value that was specified by the DEGREE clause in the CREATE or ALTER INDEX statement. Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type, and collects the global, partition level and subpartition level statistics if the subpartitioning method is LIST, and the global and partition level only otherwise. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics.
cascade	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 DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default.The default value can be changed using the SET_PARAM Procedure.
stattab	User statistics table identifier describing where to save the current statistics.
statid	The (optional) identifier to associate with these statistics within stattab.
options	Further specification of objects for which to gather statistics: 'GATHER' - gathers statistics on all objects in the schema 'GATHER AUTO' - gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics and determines how to gather those statistics. When 'GATHER AUTO' is specified, the only additional valid parameters are comp_id, stattab, statid and statown; all other parameter settings will be ignored. Also, returns a list of objects processed. 'GATHER STALE' - gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, returns a list of objects found to be stale. 'GATHER EMPTY' - gathers statistics on objects which currently have no statistics. Also, returns a list of objects found to have no statistics. 'LIST AUTO' - returns list of objects to be processed with 'GATHER AUTO' 'LIST STALE' - returns list of stale objects as determined by looking at the *_tab_modifications views 'LIST EMPTY' - returns list of objects which currently have no statistics
objlist	The list of objects found to be stale or empty.
statown	The schema containing stattab (if different than ownname).
no_invalidate	Dependent cursors are not invalidated if this parameter is set to TRUE. When 'CASCADE' option is specified, this does not apply to certain types of indexes described with regard to the GATHER_INDEX_STATS Procedure. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

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.

---

GATHER_FIXED_OBJECTS_STATS Procedure

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 93-28  GATHER_FIXED_OBJECTS_STATS Procedure Parameters

Parameter	Description
stattab	The user statistics table identifier describing where to save the current statistics.
statid	The (optional) identifier to associate with these statistics within stattab.
statown	The schema containing stattab (if different from ownname).
no_invalidate	Does not invalidate the dependent cursors if set to TRUE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

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.

---

GATHER_INDEX_STATS Procedure

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

Syntax

DBMS_STATS.GATHER_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT 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 'AUTO',
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')));

Parameters

Table 93-29  GATHER_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Schema of index to analyze.
indname	Name of index.
partname	Name of partition.
estimate_percent	Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.
stattab	User statistics table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).
degree	Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure. NULL means use of table default value that was specified by the DEGREE clause in the CREATE/ALTER INDEX statement. Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type, and collects the global, partition level and subpartition level statistics if the subpartitioning method is LIST, and the global and partition level only otherwise. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics.
no_invalidate	Dependent cursors are not invalidated if this parameter is set to TRUE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

Exceptions

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

ORA-20001: Bad input value.

---

GATHER_SCHEMA_STATS Procedures

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 'AUTO', 
   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'))); 
   
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 'AUTO', 
   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'))); 

Parameters

Table 93-30  GATHER_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Schema to analyze (NULL means current schema).
estimate_percent	Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.
block_sample	Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt	Accepts: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...], where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY} integer--Number of histogram buckets. Must be in the range [1,254]. REPEAT--Collects histograms only on the columns that already have histograms. AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns. The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.
degree	Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type, and collects the global, partition level and subpartition level statistics if the subpartitioning method is LIST, and the global and partition level only otherwise. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics.
cascade	Gather statistics on the indexes as well.Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_PARAM Procedure.
stattab	User statistics table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
options	Further specification of which objects to gather statistics for: GATHER: Gathers statistics on all objects in the schema. GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects. GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale. GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics. LIST AUTO: Returns a list of objects to be processed with GATHER AUTO. LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views. LIST EMPTY: Returns list of objects which currently have no statistics.
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the objects for which statistics are being gathered. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Cannot be used with the cascade option because GATHER_INDEX_STATS does not support the cascade option. This option has no effect when an index is picked up as a target because GATHER_INDEX_STATS does not support this option.Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

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.

---

GATHER_SYSTEM_STATS Procedure

This procedure gathers system statistics.

Syntax

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

Parameters

Table 93-31  GATHER_SYSTEM_STATS Procedure Parameters

Parameter	Description
gathering_mode	Mode values are: NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both 'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed. INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc. START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
interval	Time, in minutes, to gather statistics. This parameter applies only when gathering_mode='INTERVAL'.
stattab	Identifier of the user statistics table where the statistics will be saved.
statid	Optional identifier associated with the statistics saved in the stattab.
statown	The schema containing stattab, if different from the user's schema.

Exceptions

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

ORA-20001: Invalid input value.

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

ORA-20003: Unable to gather system statistics.

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

---

GATHER_TABLE_STATS Procedure

This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters.

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 'AUTO', 
   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')));

Parameters

Table 93-32  GATHER_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Schema of table to analyze.
tabname	Name of table.
partname	Name of partition.
estimate_percent	Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.
block_sample	Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt	Accepts: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...], where size_clause is defined as: size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY} integer--Number of histogram buckets. Must be in the range [1,254]. REPEAT--Collects histograms only on the columns that already have histograms. AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns. The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.
degree	Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
granularity	Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type, and collects the global, partition level and subpartition level statistics if the subpartitioning method is LIST, and the global and partition level only otherwise. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics.
cascade	Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_PARAM Procedure.
stattab	User statistics table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Cannot be used with the cascade option because GATHER_INDEX_STATS does not support the cascade option. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

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.

---

GENERATE_STATS Procedure

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 93-33  GENERATE_STATS Procedure Parameters

Parameter	Description
ownname	Schema of object.
objname	Name of object.
organized	Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have consecutive keys referencing different table blocks on disk. This parameter is only used for b-tree indexes. The number can be in the range of 0-10, with 0 representing a completely organized index and 10 a completely disorganized one.

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.

---

GET_COLUMN_STATS Procedures

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 93-34  GET_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.
stattab	User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
distcnt	Number of distinct values.
density	Column density.
nullcnt	Number of NULLs.
srec	Structure holding internal representation of column minimum, maximum, and histogram values.
avgclen	Average length of the column (in bytes).
statown	Schema containing stattab (if different than ownname).

Exceptions

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

---

GET_INDEX_STATS Procedures

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 93-35  GET_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition for which to get the statistics. If the index is partitioned and if partname is NULL, then the statistics are retrieved for the global index level.
stattab	User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
numrows	Number of rows in the index (partition).
numlblks	Number of leaf blocks in the index (partition).
numdist	Number of distinct keys in the index (partition).
avglblk	Average integral number of leaf blocks in which each distinct key appears for this index (partition).
avgdblk	Average integral number of data blocks in the table pointed to by a distinct key for this index (partition).
clstfct	Clustering factor for the index (partition).
indlevel	Height of the index (partition).
statown	Schema containing stattab (if different than ownname).
guessq	Guess quality for the index (partition).
cachedblk	The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition).
cachehit	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.

---

GET_PARAM Function

This function returns the default value of parameters of DBMS_STATS procedures.

Syntax

DBMS_STATS.GET_PARAM (
   pname     IN   VARCHAR2)
 RETURN VARCHAR2;

Parameters

Table 93-36  GET_PARAM Function Parameters

Parameter	Description
pname	The parameter name.

Exceptions

ORA-20001: Invalid input values

---

GET_STATS_HISTORY_AVAILABILITY Function

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;

---

GET_STATS_HISTORY_RETENTION Function

This function returns the current retention value.

Syntax

DBMS_STATS.GET_STATS_HISTORY_RETENTION
 RETURN NUMBER;

---

GET_SYSTEM_STATS Procedure

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

Syntax

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

Parameters

Table 93-37  GET_SYSTEM_STATS Procedure Parameters

Parameter	Description
status	Output is one of the following: COMPLETED: AUTOGATHERING: MANUALGATHERING: BADSTATS:
dstart	Date when statistics gathering started. If status = MANUALGATHERING, the start date is returned.
dstop	Date when statistics gathering stopped. If status = COMPLETE, the finish date is returned. If status = AUTOGATHERING, the future finish date is returned. If status = BADSTATS, the must-finished-by date is returned.
pname	The parameter name to get, which can have one of the following values: iotfrspeed - I/O transfer speed in bytes for each millisecond ioseektim - seek time + latency time + operating system overhead time, in milliseconds sreadtim - average time to read single block (random read), in milliseconds mreadtim - average time to read an mbrc block at once (sequential read), in milliseconds cpuspeed - average number of CPU cycles for each second, in millions, captured for the workload (statistics collected using 'INTERVAL' or 'START' and 'STOP' options) cpuspeednw - average number of CPU cycles for each second, in millions, captured for the noworkload (statistics collected using 'NOWORKLOAD' option. mbrc - average multiblock read count for sequential read, in blocks maxthr - maximum I/O system throughput, in bytes/second slavethr - average slave I/O throughput, in bytes/second
pvalue	The parameter value to get.
stattab	Identifier of the user statistics table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary.
statid	Optional identifier associated with the statistics saved in the stattab.
statown	The schema containing stattab, if different from the user's schema.

Exceptions

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

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

ORA-20003: Unable to gather system statistics.

ORA-20004: Parameter does not exist.

---

GET_TABLE_STATS Procedure

This procedure gets all table-related information.

Syntax

DBMS_STATS.GET_TABLE_STATS (
   ownname         VARCHAR2, 
   tabname         VARCHAR2, 
   partname        VARCHAR2 DEFAULT NULL,
   stattab         VARCHAR2 DEFAULT NULL,
   statid          VARCHAR2 DEFAULT NULL,
   numrows     OUT NUMBER, 
   numblks     OUT NUMBER,
   avgrlen     OUT NUMBER,
   statown         VARCHAR2 DEFAULT NULL,
   cachedblk   OUT NUMBER,
   cachehit    OUT NUMBER);

Parameters

Table 93-38  GET_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
partname	Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.
stattab	User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
numrows	Number of rows in the table (partition).
numblks	Number of blocks the table (partition) occupies.
avgrlen	Average row length for the table (partition).
statown	Schema containing stattab (if different than ownname).
cachedblk
cachehit

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

---

IMPORT_COLUMN_STATS Procedure

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 93-39  IMPORT_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema.
tabname	The name of the table to which this column belongs.
colname	The name of the column.
partname	The name of the table partition. If the table is partitioned and if partname is NULL, then global and partition column statistics are imported.
stattab	The user statistics table identifier describing from where to retrieve the statistics.
statid	The (optional) identifier to associate with these statistics within stattab.
statown	The schema containing stattab (if different than ownname).
no_invalidate	If set to FALSE, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they expire or are invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	If set to TRUE, imports statistics even if 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.

---

IMPORT_DATABASE_STATS Procedure

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 93-40  IMPORT_DATABASE_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab. If statown is NULL, then it is assumed that every schema in the database contains a user statistics table with the name stattab.
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the objects to which the statistics are being imported. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	Overrides statistics locked at the object (table) level: TRUE - Ignores the statistics lock and imports the statistics. FALSE - The statistics will be imported only if they are not locked.

Exceptions

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

ORA-20001: Invalid or inconsistent values in the user statistics table.

---

IMPORT_DICTIONARY_STATS Procedure

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 93-41  IMPORT_DICTIONARY_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing from where to retrieve the statistics.
statid	The (optional) identifier to associate with these statistics within stattab.
statown	The schema containing stattab (if different from current schema).
no_invalidate	Do not invalidate the dependent cursors if set to TRUE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	Overrides statistics locked at the object (table) level: TRUE - Ignores the statistics lock and imports the statistics. FALSE - The statistics will be imported only if there is no lock.

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.

---

IMPORT_FIXED_OBJECTS_STATS Procedure

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 93-42  IMPORT_FIXED_OBJECTS_STATS Procedure Parameters

Parameter	Description
stattab	User statistics table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab. If statown is NULL, then it is assumed that every schema in the database contains a user statistics table with the name stattab.
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the objects to which the statistics are being imported. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	Overrides statistics lock: TRUE - Ignores the statistics lock and imports the statistics FALSE - The statistics will be imported only if there is no 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.

---

IMPORT_INDEX_STATS Procedure

This procedure 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 93-43  IMPORT_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition. If the index is partitioned and if partname is NULL, then global and partition index statistics are imported.
stattab	User statistics table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	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.

---

IMPORT_SCHEMA_STATS Procedure

This procedure 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 93-44  IMPORT_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	User statistics table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the objects to which the statistics are being imported. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	Overrides statistics locked at the object (table) level: TRUE - Ignores the statistics lock and imports the statistics. FALSE - The statistics will be imported only if there is no lock.

Exceptions

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

ORA-20001: Invalid or inconsistent values in the user statistics table.

---

IMPORT_SYSTEM_STATS Procedure

This procedure 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 93-45  IMPORT_SYSTEM_STATS Procedure Parameters

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

Exceptions

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

ORA-20001: Invalid or inconsistent values in the user statistics table.

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

ORA-20003: Unable to import system statistics.

---

IMPORT_TABLE_STATS Procedure

This procedure retrieves statistics for a particular table from the user 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 93-46  IMPORT_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table.
partname	Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are imported.
stattab	User statistics table identifier describing from where to retrieve the statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
cascade	If true, then column and index statistics for this table are also imported.
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	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.

---

LOCK_SCHEMA_STATS Procedure

This procedure locks the statistics of all tables of a schema.

Syntax

DBMS_STATS.LOCK_SCHEMA_STATS (
   ownname    VARCHAR2);

Parameters

Table 93-47  LOCK_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema to lock.

Usage Notes

See "Usage Notes" for LOCK_TABLE_STATS Procedure.

---

LOCK_TABLE_STATS Procedure

This procedure locks the statistics on the table.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
   ownname    VARCHAR2,
   tabname    VARCHAR2);

Parameters

Table 93-48  LOCK_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema.
tabname	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 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.

---

PREPARE_COLUMN_VALUES 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 93-49  PREPARE_COLUMN_VALUES Procedure Parameters

Parameter	Description
srec.epc	Number of values specified in charvals, datevals, dblvals, fltvals, numvals, or rawvals. This value must be between 2 and 256, inclusive, and it should be set to 2 for procedures which do not allow histogram information (nvarchar and rowid). The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to SET_COLUMN_STATS.
srec.bkvals	If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in charvals, datevals, dblvals, fltvals, numvals, or rawvals. Otherwise, it is merely an output parameter, and it must be set to NULL when this procedure is called.

Datatype-specific input parameters (use one) are shown in Table 93-50.

Table 93-50  Datatype-Specific Input Parameters

Type	Description
charvals	The array of values when the column type is character-based. Up to the first 32 bytes of each string should be provided. Arrays must have between 2 and 256 entries, inclusive. If the datatype is fixed CHAR, the strings must be space-padded to 15 characters for correct normalization.
datevals	The array of values when the column type is date-based.
dblvals	The array of values when the column type is double-based.
fltvals	The array of values when the column type is float-based.
numvals	The array of values when the column type is numeric-based.
rawvals	The array of values when the column type is RAW. Up to the first 32 bytes of each strings should be provided.
nvmin, nvmax	The minimum and maximum values when the column type is national character set based. No histogram information can be provided for a column of this type. If the datatype is fixed CHAR, the strings must be space-padded to 15 characters for correct normalization.
rwmin, rwmax	The minimum and maximum values when the column type is rowid. No histogram information is provided for a column of this type.

Output Parameters

Table 93-51  PREPARE_COLUMN_VALUES Procedure Output Parameters

Parameter	Description
srec.minval	Internal representation of the minimum suitable for use in a call to SET_COLUMN_STATS.
srec.maxval	Internal representation of the maximum suitable for use in a call to SET_COLUMN_STATS.
srec.bkvals	Array suitable for use in a call to SET_COLUMN_STATS.
srec.novals	Array suitable for use in a call to SET_COLUMN_STATS.

Exceptions

ORA-20001: Invalid or inconsistent input values.

---

PREPARE_COLUMN_VALUES_NVARCHAR2 Procedure

This procedure converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using SET_COLUMN_STATS.

Syntax

DBMS_STATS.PREPARE_COLUMN_VALUES_NVARCHAR2 (
   srec     IN OUT StatRec, 
   nvmin           NVARCHAR2, 
   nvmax           NVARCHAR2);

Pragmas

pragma restrict_references(prepare_column_values_nvarchar, WNDS, RNDS, WNPS, 
RNPS);

Parameters

Table 93-52  PREPARE_COLUMN_VALUES_NVARCHAR2 Procedure Parameters

Parameter	Description
srec.epc	Number of values specified in charvals, datevals, dblvals, fltvals, numvals, or rawvals. This value must be between 2 and 256, inclusive, and it should be set to 2 for procedures which do not allow histogram information (nvarchar and rowid). The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to SET_COLUMN_STATS.
srec.bkvals	If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in charvals, datevals, dblvals, fltvals, numvals, or rawvals. Otherwise, it is merely an output parameter, and it must be set to NULL when this procedure is called.

Datatype-specific input parameters (use one) are shown in Table 93-50.

Table 93-53  Datatype-Specific Input Parameters

Type	Description
nvmin, nvmax	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 CHAR, the strings must be space-padded to 15 characters for correct normalization.

Output Parameters

Table 93-54  PREPARE_COLUMN_VALUES Procedure Output Parameters

Parameter	Description
srec.minval	Internal representation of the minimum suitable for use in a call to SET_COLUMN_STATS.
srec.maxval	Internal representation of the maximum suitable for use in a call to SET_COLUMN_STATS.
srec.bkvals	Array suitable for use in a call to SET_COLUMN_STATS.
srec.novals	Array suitable for use in a call to SET_COLUMN_STATS.

Exceptions

ORA-20001: Invalid or inconsistent input values.

---

PREPARE_COLUMN_VALUES_ROWID Procedure

This procedure converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using SET_COLUMN_STATS.

Syntax

DBMS_STATS.PREPARE_COLUMN_VALUES_ROWID (
   srec  IN OUT StatRec, 
   rwmin        ROWID, 
   rwmax        ROWID);

Pragmas

pragma restrict_references(prepare_column_values_rowid, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 93-55  PREPARE_COLUMN_VALUES_ROWID Procedure Parameters

Parameter	Description
srec.epc	Number of values specified in charvals, datevals, dblvals, fltvals, numvals, or rawvals. This value must be between 2 and 256, inclusive, and it should be set to 2 for procedures which do not allow histogram information (nvarchar and rowid). The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to SET_COLUMN_STATS.
srec.bkvals	If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in charvals, datevals, dblvals, fltvals, numvals, or rawvals. Otherwise, it is merely an output parameter, and it must be set to NULL when this procedure is called.

Datatype-specific input parameters (use one) are shown in Table 93-50.

Table 93-56  Datatype-Specific Input Parameters

Type	Description
rwmin, rwmax	The minimum and maximum values when the column type is rowid. No histogram information is provided for a column of this type.

Output Parameters

Table 93-57  PREPARE_COLUMN_VALUES Procedure Output Parameters

Parameter	Description
srec.minval	Internal representation of the minimum suitable for use in a call to SET_COLUMN_STATS.
srec.maxval	Internal representation of the maximum suitable for use in a call to SET_COLUMN_STATS.
srec.bkvals	Array suitable for use in a call to SET_COLUMN_STATS.
srec.novals	Array suitable for use in a call to SET_COLUMN_STATS.

Exceptions

ORA-20001: Invalid or inconsistent input values.

---

PURGE_STATS Procedure

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 93-58  PURGE_STATS Procedure Parameters

Parameter

Description

before_timestamp

Versions of statistics saved before this timestamp are purged. If NULL, it uses the purging policy used by automatic purge. The automatic purge deletes all history older than the older of (current time - statistics history retention) and (time of recent analyze in the system - 1). The statistics history retention value can be changed using ALTER_STATS_HISTORY_RETENTION Procedure.The default is 31 days.

Exceptions

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

ORA-20001: Invalid or inconsistent values.

---

RESTORE_DATBASE_STATS Procedure

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

Parameters

Table 93-59  RESTORE_DATBASE_STATS Procedure Parameters

Parameter	Description
as_of_timestamp	The timestamp to which to restore statistics.
force	Restores statistics even if their statistics are locked.

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.

---

RESTORE_DICTIONARY_STATS Procedure

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

Parameters

Table 93-60  RESTORE_DICTIONARY_STATS Procedure Parameters

Parameter	Description
as_of_timestamp	The timestamp to which to restore statistics.
force	Restores statistics even if their statistics are locked.

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.

---

RESTORE_FIXED_OBJECTS_STATS Procedure

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

Parameters

Table 93-61  RESTORE_FIXED_OBJECTS_STATS Procedure Parameters

Parameter	Description
as_of_timestamp	The timestamp to which to restore statistics.
force	Restores statistics even if their statistics are locked.

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.

---

RESTORE_SCHEMA_STATS Procedure

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

Parameters

Table 93-62  RESTORE_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	The schema of the tables for which the statistics are to be restored.
as_of_timestamp	The timestamp to which to restore statistics.
force	Restores statistics even if their statistics are locked.

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.

---

RESTORE_SYSTEM_STATS Procedure

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 93-63  RESTORE_SYSTEM_STATS Procedure Parameters

Parameter	Description
as_of_timestamp	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.

---

RESTORE_TABLE_STATS Procedure

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

Parameters

Table 93-64  RESTORE_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	The schema of the table for which the statistics are to be restored.
tabname	The table name.
as_of_timestamp	The timestamp to which to restore statistics.
restore_cluster_index	If the table is part of a cluster, restore statistics of the cluster index if set to TRUE.
force	Restores statistics even if the table statistics are locked. If the table statistics were not locked at the specified timestamp, it unlocks the 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.

---

SET_COLUMN_STATS Procedures

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 93-65  SET_COLUMN_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table to which this column belongs.
colname	Name of the column.
partname	Name of the table partition in which to store the statistics. If the table is partitioned and partname is NULL, then the statistics are stored at the global table level.
stattab	User statistics table identifier describing where to store the statistics. If stattab is NULL, then the statistics are stored directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
distcnt	Number of distinct values.
density	Column density. If this value is NULL and if distcnt is not NULL, then density is derived from distcnt.
nullcnt	Number of NULLs.
srec	StatRec structure filled in by a call to PREPARE_COLUMN_VALUES or GET_COLUMN_STATS.
avgclen	Average length for the column (in bytes).
flags	For internal Oracle use (should be left as NULL).
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
force	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.

---

SET_INDEX_STATS Procedures

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 93-66  SET_INDEX_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
indname	Name of the index.
partname	Name of the index partition in which to store the statistics. If the index is partitioned and if partname is NULL, then the statistics are stored at the global index level.
stattab	User statistics table identifier describing where to store the statistics. If stattab is NULL, then the statistics are stored directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
ext_stats	The user-defined statistics.
stattypown	Schema of the statistics type.
stattypname	Name of the statistics type.
numrows	Number of rows in the index (partition).
numlblks	Number of leaf blocks in the index (partition).
numdist	Number of distinct keys in the index (partition).
avglblk	Average integral number of leaf blocks in which each distinct key appears for this index (partition). If not provided, then this value is derived from numlblks and numdist.
avgdblk	Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, then this value is derived from clstfct and numdist.
clstfct	See clustering_factor column of the all_indexes view for a description.
indlevel	Height of the index (partition).
flags	For internal Oracle use (should be left as NULL).
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
guessq	Guess quality. See the pct_direct_access column of the all_indexes view for a description.
cachedblk	The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition).
cachehit	The average cache hit ratio for the segment (index/table/index partition/table partition).
force	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.

---

SET_PARAM Procedure

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 93-67 SET_PARAM Procedure Parameters

Parameter	Description
pname	The parameter name The default value for following parameters can be set. CASCADE - The default value for CASCADE set by SET_PARAM is not used by export/import procedures.It is used only by gather procedures. DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE
pval	The parameter value. If NULL is specified, it will set the oracle default value.

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.

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

---

SET_SYSTEM_STATS Procedure

This procedure sets systems statistics.

Syntax

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

Parameters

Table 93-68  SET_SYSTEM_STATS Procedure Parameters

Parameter	Description
pname	The parameter name to get, which can have one of the following values: iotfrspeed--I/O transfer speed in bytes for each millisecond ioseektim - seek time + latency time + operating system overhead time, in milliseconds sreadtim - average time to read single block (random read), in milliseconds mreadtim - average time to read an mbrc block at once (sequential read), in milliseconds cpuspeed - average number of CPU cycles for each second, in millions, captured for the workload (statistics collected using 'INTERVAL' or 'START' and 'STOP' options) cpuspeednw - average number of CPU cycles for each second, in millions, captured for the noworkload (statistics collected using 'NOWORKLOAD' option. mbrc - average multiblock read count for sequential read, in blocks maxthr - maximum I/O system throughput, in bytes/second slavethr - average slave I/O throughput, in bytes/second
pvalue	Parameter value to get.
stattab	Identifier of the user statistics table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary.
statid	Optional identifier associated with the statistics saved in the stattab.
statown	The schema containing stattab, if different from the user's schema.
cachedblk	The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition).
cachehit	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.

---

SET_TABLE_STATS Procedure

This procedure sets table-related information.

Syntax

DBMS_STATS.SET_TABLE_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2, 
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   numrows       NUMBER   DEFAULT NULL, 
   numblks       NUMBER   DEFAULT NULL,
   avgrlen       NUMBER   DEFAULT NULL, 
   flags         NUMBER   DEFAULT NULL,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   cachedblk     NUMBER    DEFAULT NULL,
   cachehit      NUMBER    DEFUALT NULL,
   force         BOOLEAN   DEFAULT FALSE);

Parameters

Table 93-69  SET_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	Name of the schema.
tabname	Name of the table.
partname	Name of the table partition in which to store the statistics. If the table is partitioned and partname is NULL, then the statistics are stored at the global table level.
stattab	User statistics table identifier describing where to store the statistics. If stattab is NULL, then the statistics are stored directly in the dictionary.
statid	Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
numrows	Number of rows in the table (partition).
numblks	Number of blocks the table (partition) occupies.
avgrlen	Average row length for the table (partition).
flags	For internal Oracle use (should be left as NULL).
statown	Schema containing stattab (if different than ownname).
no_invalidate	If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
cachedblk	The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition).
cachehit	The average cache hit ratio for the segment (index/table/index partition/table partition).
force	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.

---

UNLOCK_SCHEMA_STATS Procedure

This procedure unlocks the statistics on all the tables in schema.

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
   ownname    VARCHAR2);

Parameters

Table 93-70  UNLOCK_SCHEMA_STATS Procedure Parameters

Parameter	Description
ownname	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.

---

UNLOCK_TABLE_STATS Procedure

This procedure unlocks the statistics on the table.

Syntax

DBMS_STATS.UNLOCK_TABLE_STATS (
   ownname    VARCHAR2,
   tabname    VARCHAR2);

Parameters

Table 93-71  UNLOCK_TABLE_STATS Procedure Parameters

Parameter	Description
ownname	The name of the schema.
tabname	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.

---

UPGRADE_STAT_TABLE Procedure

This procedure upgrades a user statistics table from an older version.

Syntax

DBMS_STATS.UPGRADE_STAT_TABLE (
   ownname    VARCHAR2,
   stattab    VARCHAR2);

Parameters

Table 93-72  UPGRADE_STAT_TABLE Procedure Parameters

Parameter	Description
ownname	Name of the schema.
stattab	Name of the table.

Exceptions

ORA-20000: Unable to upgrade table.