Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1) B2841903 


PDF · Mobi · ePub 
With the DBMS_STATS
package you can view and modify optimizer statistics gathered for database objects.
See Also:
Oracle Database Performance Tuning GuideThis chapter contains the following topics:
Overview
Types
Constants
Operational Notes
Deprecated Subprograms
Examples
This section contains topics which relate to using the DBMS_STATS
package.
The Oracle RDBMS allows you to collect statistics of many different kinds as an aid to improving performance. This package is concerned with optimizer statistics only. Given that Oracle sets automatic statistics collection of this kind on by default, this package is intended for only specialized cases.
The statistics of interest to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage userdefined 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, userdefined 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 costbased optimizer. You can also use DBMS_STATS
to gather statistics in parallel
See Also:
Oracle Database Performance Tuning Guide for more information about "Managing Optimizer Statistics".Types for the minimum and maximum values and histogram endpoints include:
TYPE numarray IS VARRAY(256) OF NUMBER; TYPE datearray IS VARRAY(256) OF DATE; TYPE chararray IS VARRAY(256) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(256) OF RAW(2000); TYPE fltarray IS VARRAY(256) OF BINARY_FLOAT; TYPE dblarray IS VARRAY(256) OF BINARY_DOUBLE; TYPE StatRec IS RECORD ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY);
Types for listing stale tables include:
TYPE ObjectElem IS RECORD ( ownname VARCHAR2(30),  owner objtype VARCHAR2(6),  'TABLE' or 'INDEX' objname VARCHAR2(30),  table/index partname VARCHAR2(30),  partition subpartname VARCHAR2(30));  subpartition type ObjectTab is TABLE of ObjectElem;
Type for displaying statistics difference report:
TYPE DiffRepElem IS RECORD ( report CLOB,  stats difference report maxdiffpct number);  max stats difference (percentage) type DiffRepTab is table of DiffRepElem;
The DBMS_STATS
package uses the constants shown in Table 1271:
Table 1271 DBMS_STATS Constants
Name  Type  Value  Description 




Lets Oracle decide whether to collect statistics for indexes or not 



Lets Oracle select the degree of parallelism based on size of the object, number of CPUs and initialization parameters 


NULL 
Lets Oracle decide when to invalidate dependent cursors 



Indicates that autosample size algorithms should be used 
The DBMS_STATS
subprograms perform the following general operations:
Most of the DBMS_STATS
procedures include the three parameters statown
, stattab
, and statid
. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
The stattab
parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown
parameter is specified). You can create multiple tables with different stattab
identifiers to hold separate sets of statistics.
Additionally, you can maintain different sets of statistics within a single stattab
by using the statid
parameter, which avoids cluttering the user's schema.
For the SET
and GET
procedures, if stattab
is not provided (that is, NULL
), then the operation works directly on the dictionary statistics; therefore, you do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if stattab
is not NULL
, then the SET
or GET
operation works on the specified user statistics table, and not the dictionary.
You can change the default values of some of the parameters of DBMS_STATS
procedures using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
Most of the procedures in this package commit the current transaction, perform the operation, and then commit again.
Most of the procedures have a parameter, force
which allows you to override any lock on statistics.Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring.
Gathering Optimizer Statistics
Use the following subprograms to gather certain classes of optimizer statistics, with possible performance improvements over the ANALYZE
command:
The GATHER_*
procedures also collect userdefined 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:
Use the following subprograms to store and retrieve individual columnrelated, indexrelated, and tablerelated statistics:
In the special versions of the SET_*_STATS
procedures for setting userdefined statistics, the following, if provided, are stored in the dictionary or external statistics table:
Userdefined statistics (extstats
)
The statistics type schema name (statsschema
)
The statistics type name (statsname
)
The userdefined statistics and the corresponding statistics type are inserted into the USTATS$
dictionary table. You can specify userdefined statistics without specifying the statistics type name.
The special versions of the GET_*_STATS
procedures return userdefined statistics and the statistics type owner and name as OUT
arguments corresponding to the schema object specified. If userdefined statistics are not collected, NULL
values are returned.
The DELETE_*
procedures delete both userdefined statistics and the standard statistics for the given schema object.
Use the following procedures for creating and dropping the user statistics table.
Use the following procedures to transfer statistics
from the dictionary to a user statistics table (EXPORT_
*)
from a user statistics table to the dictionary (IMPORT_
*)
Note:
Oracle does not support export or import of statistics across databases of different character sets.Locking or Unlocking Statistics
Use the following procedures to lock and unlock statistics on objects.
The LOCK_*
procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
Restoring and Purging Statistics History
Use the following procedures to restore statistics as of a specified timestamp. This is useful in case newly collected statistics leads to some suboptimal execution plans and the administrator wants to revert to the previous set of statistics.
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION Procedure.
The other DBMS_STATS
procedures related to restoring statistics are:
PURGE_STATS Procedure: This procedure lets you manually purge old versions beyond a time stamp.
GET_STATS_HISTORY_RETENTION Function: This function gets the current statistics history retention value.
GET_STATS_HISTORY_AVAILABILITY Function: This function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.
RESTORE_*
operations are not supported for user defined statistics.
The DBMS_STATS
package supports operations on userdefined statistics. When a domain index or column is associated with a statistics type (using the associate
statement), operations on the index or column manipulate userdefined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the GET_INDEX_STATS Procedures invokes the userdefined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate userdefined statistics.
SET_*
and GET_*
operations for userdefined statistics are also supported using a special version of the SET
and GET
interfaces for columns and indexes.
EXPORT_*
, IMPORT_*
and RESTORE_*
operations are not supported for user defined statistics.
The package gather statistics and stores it in the dictionary by default. User's can store these statistics in the system's private area instead of the dictionary by turning the PUBLISH
option to FALSE
using the SET*PREFS
procedures. The default value for PUBLISH
is TRUE
.The statistics stored in private area are not used by Cost Based Optimizer unless parameter optimizer_use_private_statistics
is set to TRUE
. The default value of this parameter is FALSE
and this boolean parameter can be set at the session/system level. Users can verify the impact of the new statistics on query plans by using the pending statistics on a session.
Pending statistics provide a mechanism to verify the impact of the new statistics on query plans before making them available for general use. There are two scenarios to verify the query plans:
Export the pending statistics (use the EXPORT_PENDING_STATS Procedure) to a test system, then run the query workload and check the performance or plans.
Set optimizer_use_pending_statistics
to TRUE
in a session on the system where pending statistics have been gathered, run the workload, and check the performance or plans.
Once the performance or query plans have been verified, the pending statistics can be published (run the PUBLISH_PENDING_STATS Procedure) if the performance is acceptable or delete (run the DELETE_PENDING_STATS Procedure) if not.
Pending statistics can be published, exported, or deleted. The following procedures are provided to manage pending statistics:
The DIFF_TABLE_STATS_
* statistics can be used to compare statistics for a table from two different sources. The statistics can be from:
two different user statistics tables
a single user statistics table containing two sets of statistics that can be identified using statids
a user statistics table and dictionary history
pending statistics
The functions also compare the statistics of the dependent objects (indexes, columns, partitions). They displays statistics of the object(s) from both sources if the difference between those statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the function, with a default of 10%. The statistics corresponding to the first source (stattab1
or time1
) will be used as basis for computing the differential percentage.
This package allows you to collect statistics for column groups and expressions (known as "statistics extensions"). The statistics collected for column groups and expressions are called "extended statistics".
Statistics on Column groups are used by optimizer for accounting correlation between columns. For example, if a query has predicates c1=1 and c2=1 and if there are statistics on (c1, c2), the optimizer will use this statistics for estimating the combined selectivity of the predicates.The expression statistics are used by optimizer for estimating selectivity of predicates on those expressions. The extended statistics are similar to column statistics and the procedures that take columns names will accept extension names in place of column names.
Related subprograms:
The following subprograms are obsolete with Release 11g:
Instead, use GET_PREFS Function
Instead, use SET_GLOBAL_PREFS Procedure
RESET_PARAM_DEFAULTS Procedure
Instead use RESET_GLOBAL_PREF_DEFAULTS Procedure
Assume many modifications have been made to the employees
table since the last time statistics were gathered. To ensure that the costbased 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:
EXEC DBMS_STATS.SET_TABLE_PREFS('hr', 'employees', 'PUBLISH', 'false');
By setting the employees tables publish preference to FALSE
, any statistics gather from now on will not be automatically published. The newly gathered statistics will be marked as pending.
EXEC DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees');
To test the newly gathered statistics, set optimizer_pending_statistics
to TRUE
in a session and run sample queries.
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
If the pending statistics generate sound execution plans, they can be published:
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('hr', 'employees'); EXEC DBMS_STATS.SET_TABLE_PREF('hr', 'employees', 'PUBLISH', 'true');
Gathering Daytime System Statistics
Assume that you want to perform database application processing OLTP transactions during the day and run reports at night.
To collect daytime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS
table.
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 720, stattab => 'mystats', statid => 'OLTP'); END;
To collect nighttime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS
table.
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 720, stattab => 'mystats', statid => 'OLAP'); END;
Update the dictionary with the gathered statistics.
VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS (''mystats'',''OLTP'');' sysdate, 'sysdate + 1'); COMMIT; END; BEGIN DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS (''mystats'',''OLAP'');' sysdate + 0.5, 'sysdate + 1'); COMMIT; END;
Table 1272 DBMS_STATS Package Subprograms
Subprogram  Description 

Changes the statistics history retention value 

Convert the internal representation of a minimum or maximum value into a datatypespecific value 

Convert the internal representation of a minimum or maximum value into a datatypespecific value 

Convert the internal representation of a minimum or maximum value into a datatypespecific value 

Copies the statistics of the source [sub] partition to the destination [sub] partition after scaling 

Creates a virtual column for a user specified column group or an expression in a table 

Creates a table with name 

Deletes columnrelated statistics 

Deletes the statistics preferences of all the tables, excluding the tables owned by Oracle. 

Deletes statistics for the entire database 

Deletes statistics for all dictionary schemas (' 

Deletes statistics of all fixed tables 

Deletes indexrelated statistics 

Deletes the private statistics that have been collected but have not been published 

Deletes the statistics preferences of all the tables owned by the specified owner name 

Deletes schemarelated statistics 

Deletes system statistics 

Deletes statistics preferences of the specified table in the specified schema 

Deletes tablerelated statistics 

Compares statistics for a table from two timestamps in past and compare the statistics as of that timestamps 

Compares pending statistics and statistics as of a timestamp or statistics from dictionary. 

Compares statistics for a table from two different sources 

Drops the statistics entry that is created for the user specified extension 

Drops a user statistics table created by 

Retrieves statistics for a particular column and stores them in the user statistics table identified by 

Exports the statistics preferences of all the tables, excluding the tables owned by Oracle. 

Retrieves statistics for all objects in the database and stores them in the user statistics table identified by 

Retrieves statistics for all dictionary schemas (' 

Retrieves statistics for fixed tables and stores them in the user statistics table identified by 

Retrieves statistics for a particular index and stores them in the user statistics table identified by 

Exports the statistics gathered and stored as pending 

Exports the statistics preferences of all the tables owned by the specified owner name 

Retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics table identified by 

Retrieves system statistics and stores them in the user statistics table 

Exports statistics preferences of the specified table in the specified schema into the specified statistics table 

Retrieves statistics for a particular table and stores them in the user statistics table 

Flushes inmemory monitoring information for all the tables to the dictionary 

Gathers statistics for all objects in the database 

Gathers statistics for dictionary schemas ' 

Gathers statistics of fixed objects 

Gathers index statistics 

Gathers statistics for all objects in a schema 

Gathers system statistics 

Gathers table and column (and index) statistics 

Generates object statistics from previously collected statistics of related objects 

Gets all columnrelated information 

Gets all indexrelated information 

Gets the default value of parameters of 

Gets the oldest timestamp where statistics history is available 

Returns the current retention value 

Gets system statistics from stattab, or from the dictionary if stattab is 

Gets all tablerelated information 

Retrieves statistics for a particular column from the user statistics table identified by 

Imports the statistics preferences of all the tables, excluding the tables owned by Oracle. 

Retrieves statistics for all objects in the database from the user statistics table and stores them in the dictionary 

Retrieves statistics for all dictionary schemas (' 

Retrieves statistics for fixed tables from the user statistics table identified by 

Retrieves statistics for a particular index from the user statistics table identified by 

Imports the statistics preferences of all the tables owned by the specified owner name 

Retrieves statistics for all objects in the schema identified by 

Retrieves system statistics from the user statistics table and stores them in the dictionary 

Sets the statistics preferences of the specified table in the specified schema. 

Retrieves statistics for a particular table from the user statistics table identified by 

Locks statistics for a partition 

Locks the statistics of all tables of a schema 

Locks the statistics on the table 

Converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures 

Converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures 

Converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures 

Publishes the statistics gathered and stored as pending 

Purges old versions of statistics saved in the dictionary 

Resets the default values of all parameters to Oracle recommended values 

Resets global preferences to default values [see Deprecated Subprograms ] 

Restores statistics of all dictionary tables (tables of ' 

Restores statistics of all fixed tables as of a specified timestamp 

Restores statistics of all tables of a schema as of a specified timestamp 

Restores statistics of all tables of a schema as of a specified timestamp 

Restores statistics of a table as of a specified timestamp (as_of_timestamp), as well as statistics of associated indexes and columns 

Sets columnrelated information 

Sets the statistics preferences of all the tables, excluding the tables owned by Oracle 

Sets the global statistics preferences 

Sets indexrelated information 

Sets default values for parameters of 

Sets the statistics preferences of all the tables owned by the specified owner name 

Sets system statistics 

Sets the statistics preferences of the specified table in the specified schema 

Sets tablerelated information 

Returns the name of the virtual column that is created for the userspecified extension 

Unlocks the statistics for a partition 

Unlocks the statistics on all the table in a schema 

Unlocks the statistics on the table 

Upgrades user statistics on an older table 
This procedure changes the statistics history retention value. Statistics history retention is used by both the automatic purge and PURGE_STATS Procedure.
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( retention IN NUMBER);
Table 1273 ALTER_STATS_HISTORY_RETENTION Procedure Parameters
Parameter  Description 


The retention time in days. The statistics history will be retained for at least these many number of days.The valid range is [1,365000]. Also you can use the following values for special purposes:

To run this procedure, you must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege.
ORA20000
: Insufficient privileges.
This procedure converts the internal representation of a minimum or maximum value into a datatypespecific value. The minval and maxval fields of the StatRec
structure as filled in by GET_COLUMN_STATS
or PREPARE_COLUMN_VALUES
are appropriate values for input.
DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_FLOAT); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_DOUBLE); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT DATE); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT NUMBER); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT VARCHAR2);
pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
Table 1274 CONVERT_RAW_VALUE Procedure Parameters
Parameter  Description 


The raw representation of a column minimum or maximum datatypespecific output parameters 

The converted, typespecific value 
This procedure converts the internal representation of a minimum or maximum value into a datatypespecific value. The minval and maxval fields of the StatRec
structure as filled in by GET_COLUMN_STATS
or PREPARE_COLUMN_VALUES
are appropriate values for input.
DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR ( rawval RAW, resval OUT NVARCHAR2);
pragma restrict_references(convert_raw_value_nvarchar, WNDS, RNDS, WNPS, RNPS);
Table 1275 CONVERT_RAW_VALUE_NVARCHAR Procedure Parameters
Parameter  Description 


The raw representation of a column minimum or maximum datatypespecific output parameters 

The converted, typespecific value 
This procedure converts the internal representation of a minimum or maximum value into a datatypespecific value. The minval and maxval fields of the StatRec
structure as filled in by GET_COLUMN_STATS
or PREPARE_COLUMN_VALUES
are appropriate values for input.
DBMS_STATS.CONVERT_RAW_VALUE_ROWID ( rawval RAW, resval OUT ROWID);
pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);
Table 1276 CONVERT_RAW_VALUE_ROWID Procedure Parameters
Parameter  Description 


The raw representation of a column minimum or maximum datatypespecific output parameters 

The converted, typespecific value 
This procedure copies the statistics of the source [sub] partition to the destination [sub] partition after scaling (the number of blks, number of rows). It sets the high bound partitioning value as the maximum value of the first partitioning column and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for a range partitioned table. It finds the maximum and minimum from the list of values for the list partitioned table. It also sets the normalized maximum and minimum values. If the destination partition is the first partition then minimum values are equal to maximum values. If the statistics for source are not available then nothing is copied.
DBMS_STATS.COPY_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, srcpartname VARCHAR2, dstpartname VARCHAR2, scale_factor VARCHAR2 DEFAULT 1, force BOOLEAN DEFAULT FALSE);
Table 1277 COPY_TABLE_STATS Procedure Parameters
Parameter  Description 


Schema of index to analyze 

Table name of source and destination [sub] partitions 

Source [sub] partition 

Destination [sub] partition 

Scale factor to scale nblks, nrows etc. in 

When value of this argument is 
ORA20000
: Invalid partition name
ORA20001
: Bad input value
This function creates a column statistics entry in the system for a user specified column group or an expression in a table. Statistics for this extension will be gathered when user or auto statistics gathering job gathers statistics for the table. We call statistics for such an extension, "extended statistics". This function returns the name of this newly created entry for the extension.
DBMS_STATS.CREATE_EXTENDED_STATS ( ownname VARCHAR2, tabname VARCHAR2, extension VARCHAR2) RETURN VARCHAR2;
Table 1278 CREATE_EXTENDED_STATS Function Parameters
Parameter  Description 


Owner name of a table 

Name of the table 

Can be either a column group or an expression. Suppose the specified table has two column 
This function returns the name of this newly created entry for the extension.
ORA20000
: Insufficient privileges / creating extension is not supported
ORA20001
: Error when processing extension
ORA20007
: Extension already exists
ORA20008
: Reached the upper limit on number of extensions
There are nine restrictions on the extension:
The extension cannot contain a virtual column.
Extensions cannot be created on tables owned by SYS.
Extensions cannot be created on cluster tables, index organized tables, temporary tables or external tables..
The total number of extensions in a table cannot be greater than a maximum of (20, 10% of number of nonvirtual columns in the table).
The number of columns in a column group must be in the range [2, 32].
A column can not appear more than once in a column group.
A column group can not contain expressions.
An expression must contain at least one column.
An expression can not contain a subquery.
The COMPATIBLE
parameter needs to be 11.0.0.0.0 or greater
This procedure creates a table with name stattab
in ownname
's schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.
DBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);
Table 1279 CREATE_STAT_TABLE Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table to create. This value should be passed as the 

Tablespace in which to create the statistics tables. If none is specified, then they are created in the user's default tablespace. 
ORA20000
: Table already exists or insufficient privileges.
ORA20001
: Tablespace does not exist.
This procedure deletes columnrelated statistics.
DBMS_STATS.DELETE_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE, col_stat_type VARCHAR2 DEFAULT 'ALL');
Table 12710 DELETE_COLUMN_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table to which this column belongs 

Name of the column or extension 

Name of the table partition for which to delete the statistics. If the table is partitioned and if 

User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

If the table is partitioned and if 

Schema containing 

Does not invalidate the dependent cursors if set to 

When value of this argument is 

Type of column statistics to be deleted.This argument takes the following values:

ORA20000
: Object does not exist or insufficient privileges.
ORA20005
: Object statistics are locked.
This procedure is used to delete the statistics preferences of all the tables, excluding the tables owned by Oracle. These tables can by included by passing TRUE
for the add_sys
parameter.
DBMS_STATS.DELETE_DATABASE_PREFS ( pname IN VARCHAR2, add_sys IN BOOLEAN DEFAULT FALSE);
Table 12711 DELETE_DATABASE_PREFS Procedure Parameters
Parameter  Description 


Preference name. The default value for following parameters can be deleted:

. 

. 

. 

. 
 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. column_name : name of a column extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is 
. 

. 
'

. 

. 
If the 
. 


Value 
ORA20000
: Insufficient privileges
ORA20001
: Invalid or Illegal input values
To run this procedure, you need to have the SYSDBA
role or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privileges.
All pname
arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
DBMS_STATS.DELETE_DATABASE_PREFS('CASCADE', FALSE); DBMS_STATS.DELETE_DATABASE_PREFS('ESTIMATE_PERCENT',TRUE);
This procedure deletes statistics for all the tables in a database.
DBMS_STATS.DELETE_DATABASE_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12712 DELETE_DATABASE_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

When the value of this argument is 
ORA20000
: Object does not exist or insufficient privileges.
This procedure deletes statistics for all dictionary schemas ('SYS
', 'SYSTEM
' and RDBMS component schemas).
DBMS_STATS.DELETE_DICTIONARY_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12713 DELETE_DICTIONARY_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

When the value of this argument is 
You must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure deletes statistics of all fixed tables.
DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12714 DELETE_FIXED_OBJECTS_STATS Procedure Parameters
Parameter  Description 


The user statistics table identifier describing from where to delete the current statistics. If 

The (optional) identifier to associate with these statistics within stattab. This only applies if 

Schema containing 

Does not invalidate the dependent cursors if set to 

Ignores the statistics lock on objects and deletes the statistics if set to 
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege to execute this procedure.
ORA20000
: Insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure deletes indexrelated statistics.
DBMS_STATS.DELETE_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12715 DELETE_INDEX_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the index 

Name of the index partition for which to delete the statistics. If the index is partitioned and if 

User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

If the index is partitioned and if 

Schema containing 

Does not invalidate the dependent cursors if set to 

When value of this argument is 
ORA20000
: Object does not exist or insufficient privileges.
ORA20005
: Object statistics are locked.
This procedure is used to delete the pending statistics that have been collected but have not been published.
DBMS_STATS.DELETE_PENDING_STATS ( ownname IN VARCHAR2 DEFAULT USER, tabname IN VARCHAR2);
Table 12716 DELETE_PENDING_STATS Procedure Parameters
Parameter  Description 


Owner name 

Table name 
ORA20000
: Insufficient privileges
If the parameter tabname
is NULL
then delete applies to all tables of the specified schema.
The default owner/schema is the user who runs the procedure.
To run this procedure, you need to have the same privilege for gathering statistics on the tables that will be touched by this procedure.
DBMS_STATS.DELETE_PENDING_STATS('SH', 'SALES');
This procedure is used to delete the statistics preferences of all the tables owned by the specified owner name.
DBMS_STATS.DELETE_SCHEMA_PREFS ( ownname IN VARCHAR2, pname IN VARCHAR2);
Table 12717 DELETE_SCHEMA_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Preference name. The default value for following parameters can be deleted:

. 

. 

. 

. 
 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. column_name : name of a column extension: can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is 
. 

. 
'

. 

. 
If the 
. 

ORA20000
: Insufficient privileges / Schema "<schema>" does not exist
ORA20001
: Invalid or Illegal input values
To run this procedure, you need to connect as owner, or have the SYSDBA
privilege, or have the ANALYZE
ANY
system privilege.
All arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
DBMS_STATS.DELETE_SCHEMA_PREFS('SH', 'CASCADE'); DBMS_STATS.DELETE_SCHEMA_PREFS('SH', 'ESTIMATE_PERCENT'); DBMS_STATS.DELETE_SCHEMA_PREFS('SH', 'DEGREE');
This procedure deletes statistics for an entire schema.
DBMS_STATS.DELETE_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12718 DELETE_SCHEMA_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

When value of this argument is 
ORA20000
: Object does not exist or insufficient privileges
This procedure deletes workload statistics (collected using the 'INTERVAL
' or 'START
' and 'STOP
' options) and resets the default to noworkload
statistics (collected using 'NOWORKLOAD
' option) if stattab
is not specified. If stattab
is specified, the subprogram deletes all system statistics with the associated statid
from the stattab
.
DBMS_STATS.DELETE_SYSTEM_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12719 DELETE_SYSTEM_STATS Procedure Parameters
Parameter  Description 


Identifier of the user statistics table where the statistics will be saved 

Optional identifier associated with the statistics saved in the stattab 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table; may need to be upgraded.
This procedure is used to delete the statistics preferences of the specified table in the specified schema.
DBMS_STATS.DELETE_TABLE_PREFS ( ownname IN VARCHAR2, tabname IN VARCHAR2, pname IN VARCHAR2);
Table 12720 DELETE_TABLE_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Table name 

Preference name. The default value for following preferences can be deleted:

. 

. 

. 

. 
 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. column_name : name of a column extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is 
. 

. 
'

. 

. 
If the 
. 

pvalue 
Preference value. If 
ORA20000
: Insufficient privileges
ORA20001
: Invalid or Illegal input values
To run this procedure, you need to connect as owner of the table, be granted ANALYZE
privilege on the table, or ANALYZE
ANY
system privilege.
All arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
DBMS_STATS.DELETE_TABLE_PREFS('SH', 'SALES', 'CASCADE'); DBMS_STATS.DELETE_TABLE_PREFS('SH', 'SALES', 'DEGREE');
This procedure deletes tablerelated statistics.
DBMS_STATS.DELETE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, cascade_columns BOOLEAN DEFAULT TRUE, cascade_indexes BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12721 DELETE_TABLE_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table to which this column belongs 

Name of the table partition from which to get the statistics. If the table is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics. If 

Identifier (optional) to associate with these statistics within 

If the table is partitioned and if 

Indicates that 

Indicates that 

Schema containing 

Does not invalidate the dependent cursors if set to 

When value of this argument is 
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it
ORA
20005
: Object statistics are locked
This function can be used to compare statistics for a table from two timestamps in past and compare the statistics as of that timestamps.
DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY( ownname IN VARCHAR2, tabname IN VARCHAR2, time1 IN TIMESTAMP WITH TIME ZONE, time2 IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, pctthreshold IN NUMBER DEFAULT 10) RETURN DiffRepTab pipelined;
Table 12722 DIFF_TABLE_STATS_IN_HISTORY Procedure Parameters
Parameter  Description 


Owner of the table. Specify 

Table for which statistics are to be compared 

First timestamp 1 

Second timestamp 2 

The function reports difference in statistics only if it exceeds this limit. The default value is 10. 
If the second timestamp is NULL
, the function compares the current statistics in dictionary with the statistics as of the other timestamp.
This function compares pending statistics and statistics as of a timestamp or statistics from dictionary.
DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING( ownname IN VARCHAR2, tabname IN VARCHAR2, timestamp IN TIMESTAMP WITH TIME ZONE, pctthreshold IN NUMBER DEFAULT 10) RETURN DiffRepTab pipelined;
Table 12723 DIFF_TABLE_STATS_IN_PENDING Function Parameters
Parameter  Description 


Owner of the table. Specify 

Table for which statistics are to be compared 

Time stamp to get statistics from the history 

The function reports difference in statistics only if it exceeds this limit. The default value is 10. 
If the second timestamp is NULL
, the function compares the current statistics in dictionary with the statistics as of the other timestamp.
This function can be used to compare statistics for a table from two different sources. The statistics can be drawn from
two different user statistics tables
a single user statistics table containing 2 sets of statistics that can be identified using statids
a user statistics table and dictionary
The function also compares the statistics of the dependent objects (indexes, columns, partitions) as well. It displays statistics of the object(s) from both sources if the difference between those statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the function. The statistics corresponding to the first source (stattab1
or time1
) will be used as basis for computing the difference percentage.
DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB( ownname IN VARCHAR2, tabname IN VARCHAR2, stattab1 IN VARCHAR2, stattab2 IN VARCHAR2 DEFAULT NULL, pctthreshold IN NUMBER DEFAULT 10, statid1 IN VARCHAR2 DEFAULT NULL, statid2 IN VARCHAR2 DEFAULT NULL, stattab1own IN VARCHAR2 DEFAULT NULL, stattab2own IN VARCHAR2 DEFAULT NULL) RETURN DiffRepTab pipelined;
Table 12724 DIFF_TABLE_STATS_IN_STATTAB Procedure Parameters
Parameter  Description 


Owner of the table. Specify 

Table for which statistics are to be compared 

User statistics table 1 

User statistics table 2. If 

The function reports difference in statistics only if it exceeds this limit. The default value is 10. 

(optional) Identifies statistics set within 

(optional) Identifies statistics set within 

Schema containing 

Schema containing 
This function drops the statistics entry that is created for the user specified extension. This cancels the effects of the CREATE_EXTENDED_STATS Function.
DBMS_STATS.DROP_EXTENDED_STATS ( ownname VARCHAR2, tabname VARCHAR2, extension VARCHAR2);
Table 12725 DROP_EXTENDED_STATS Procedure Parameters
Parameter  Description 


Owner name of a table 

Name of the table 

Can be either a column group or an expression. Suppose the specified table has two column 
If no extended statistics set is created for the extension, this function throws an error.
ORA20000
: Insufficient privileges or extension does not exist.
ORA20001
: Error when processing extension
This procedure drops a user statistics table.
DBMS_STATS.DROP_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
Table 12726 DROP_STAT_TABLE Procedure Parameters
Parameter  Description 


Name of the schema 

User statistics table identifier 
ORA20000
: Table does not exists or insufficient privileges.
This procedure retrieves statistics for a particular column and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12727 EXPORT_COLUMN_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table to which this column belongs 

Name of the column or extension 

Name of the table partition. If the table is partitioned and if 

User statistics table identifier describing where to store the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure is used to export the statistics preferences of all the tables, excluding the tables owned by Oracle. These tables can by included by passing TRUE
for the add_sys
parameter.
DBMS_STATS.EXPORT_DATABASE_PREFS ( stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL add_sys IN BOOLEAN DEFAULT FALSE);
Table 12728 EXPORT_DATABASE_PREFS Procedure Parameters
Parameter  Description 


Statistics table name to where statistics should be exported 
statid 
(Optional) Identifier to associate with these statistics within 
statown 
Schema containing 

Value 
ORA20000
: Insufficient privileges
To run this procedure, you need to have the SYSDBA role, or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privileges.
All arguments are of type VARCHAR2
and values are enclosed in quotes.
Oracle does not support export or import of statistics across databases of different character sets.
DBMS_STATS.EXPORT_DATABASE_PREFS('STATTAB', statown=>'SH');
This procedure retrieves statistics for all objects in the database and stores them in the user statistics tables identified by statown
.stattab
.
DBMS_STATS.EXPORT_DATABASE_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12729 EXPORT_DATABASE_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing where to store the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure retrieves statistics for all dictionary schemas ('SYS
', 'SYSTEM
' and RDBMS component schemas) and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_DICTIONARY_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12730 EXPORT_DICTIONARY_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing where to store the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
Oracle does not support export or import of statistics across databases of different character sets.
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for fixed tables and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12731 EXPORT_FIXED_OBJECTS_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing where to store the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure retrieves statistics for a particular index and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12732 EXPORT_INDEX_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the index 

Name of the index partition. If the index is partitioned and if 

User statistics table identifier describing where to store the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure is used to export the statistics gathered and stored as pending.
DBMS_STATS.EXPORT_PENDING_STATS ( ownname IN VARCHAR2 DEFAULT USER, tabname IN VARCHAR2, stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT USER);
Table 12733 EXPORT_PENDING_STATS Procedure Parameters
Parameter  Description 


Owner name 

Table name 

Statistics table name to where to export the statistics 
statid 
(Optional) Identifier to associate with these statistics within 
statown 
Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
If the parameter tabname
is NULL
then export applies to all tables of the specified schema.
The default owner/schema is the user who runs the procedure.
To run this procedure, you need to have the same privilege for gathering statistics on the tables that will be touched by this procedure.
All arguments are of type VARCHAR2
and values are enclosed in quotes.
Oracle does not support export or import of statistics across databases of different character sets.
DBMS_STATS.EXPORT_PENDING_STATS(NULL, NULL, 'MY_STAT_TABLE');
This procedure is used to export the statistics preferences of all the tables owned by the specified owner name.
DBMS_STATS.EXPORT_SCHEMA_PREFS ( ownname IN VARCHAR2, stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Table 12734 EXPORT_SCHEMA_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Statistics table name to where to export the statistics 
statid 
(Optional) Identifier to associate with these statistics within 
statown 
Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
To run this procedure, you need to connect as owner, or have the SYSDBA
privilege, or have the ANALYZE
ANY
system privilege.
All arguments are of type VARCHAR2
and values are enclosed in quotes.
Oracle does not support export or import of statistics across databases of different character sets.
DBMS_STATS.EXPORT_SCHEMA_PREFS('SH', 'STAT');
This procedure retrieves statistics for all objects in the schema identified by ownname
and stores them in the user statistics tables identified by stattab
.
DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12735 EXPORT_SCHEMA_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

User statistics table identifier describing where to store the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure retrieves system statistics and stores them in the user statistics table, identified by stattab
.
DBMS_STATS.EXPORT_SYSTEM_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12736 EXPORT_SYSTEM_STATS Procedure Parameters
Parameter  Description 


Identifier of the user statistics table that describes where the statistics will be stored. 

Optional identifier associated with the statistics stored from the 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to export system statistics.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure is used to export the statistics preferences of the specified table in the specified schema into the specified statistics table.
DBMS_STATS.EXPORT_TABLE_PREFS ( ownname IN VARCHAR2, tabname IN VARCHAR2, stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Table 12737 EXPORT_TABLE_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Table name 

Statistics table name where to export the statistics 
statid 
Optional identifier to associate with these statistics within stattab 
statown 
Schema containing stattab (if other than 
ORA20000
: Object does not exist or insufficient privileges.
To run this procedure, you need to connect as owner of the table, or have the ANALYZE
ANY
system privilege.
All arguments are of type VARCHAR2
and values are enclosed in quotes.
Oracle does not support export or import of statistics across databases of different character sets.
DBMS_STATS.EXPORT_TABLE_PREFS('SH', 'SALES', 'STAT');
This procedure retrieves statistics for a particular table and stores them in the user statistics table. Cascade results in all index statistics associated with the specified table being exported as well.
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);
Table 12738 EXPORT_TABLE_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table 

Name of the table partition. If the table is partitioned and if 

User statistics table identifier describing where to store the statistics 

Identifier (optional) to associate with these statistics within 

If true, then column and index statistics for this table are also exported 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure flushes inmemory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS
, *_TAB_STATISTICS
and *_IND_STATISTICS
views are updated immediately, without waiting for the Oracle database to flush them periodically. This procedure is useful when you need uptodate 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.
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
ORA20000
: Insufficient privileges.
The ANALYZE_ANY
system privilege is required to run this procedure.
This procedure gathers statistics for all objects in the database.
DBMS_STATS.GATHER_DATABASE_STATS ( estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', objlist OUT ObjectTab, statown VARCHAR2 DEFAULT NULL, gather_sys BOOLEAN DEFAULT TRUE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), obj_filter_list ObjectTab DEFAULT NULL); DBMS_STATS.GATHER_DATABASE_STATS ( estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', statown VARCHAR2 DEFAULT NULL, gather_sys BOOLEAN DEFAULT TRUE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), obj_filter_list ObjectTab DEFAULT NULL);
Table 12739 GATHER_DATABASE_STATS Procedure Parameters
Parameter  Description 


Percentage of rows to estimate ( 

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. 

Accepts:
size_clause is defined as size_clause := SIZE {integer  REPEAT  AUTO  SKEWONLY}  integer : Number of histogram buckets. Must be in the range [1,254]. REPEAT : Collects histograms only on the columns that already have histograms. AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gather statistics on the indexes as well. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the database in addition to gathering table and column statistics. Use the constant 

User statistics table identifier describing where to save the current statistics. The statistics table is assumed to reside in the same schema as the object being analyzed, so there must be one such table in each schema to use this option. 

Identifier (optional) to associate with these statistics within 

Further specification of which objects to gather statistics for:


List of objects found to be stale or empty 

Schema containing 

Gathers statistics on the objects owned by the 

Does not invalidate the dependent cursors if set to 

A list of object filters. When provided, 
Statistics for external tables are not collected by this procedure.
ORA20000
: Insufficient privileges.
ORA20001
: Bad input value.
This procedure gathers statistics for dictionary schemas 'SYS
', 'SYSTEM
' and schemas of RDBMS components.
DBMS_STATS.GATHER_DICTIONARY_STATS ( comp_id VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER AUTO', objlist OUT ObjectTab, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), obj_filter_list ObjectTab DEFAULT NULL); DBMS_STATS.GATHER_DICTIONARY_STATS ( comp_id VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type(GET_PARAM('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(GET_PARAM('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(GET_PARAM('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER AUTO', statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')), obj_filter_list ObjectTab DEFAULT NULL);
Table 12740 GATHER_DICTIONARY_STATS Procedure Parameters
Parameter  Description 


The component id of the schema to analyze ( 

Percentage of rows to estimate ( 

Determines whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk then the sample values may be somewhat correlated. Only pertinent when performing estimate statistics. 

Accepts:
size_clause is defined as size_clause :=  integer : Number of histogram buckets. Must be in the range [1,254]. REPEAT : Collects histograms only on the columns that already have histograms. AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gathers statistics on indexes also.Index statistics gathering will not be parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant 

User statistics table identifier describing where to save the current statistics 

The (optional) identifier to associate with these statistics within 

Further specification of objects for which to gather statistics:


The list of objects found to be stale or empty 

Schema containing 

Does not invalidate the dependent cursors if set to 

A list of object filters. When provided, this will gather statistics only on objects which satisfy at least one object filter in the list as needed. In a single object filter, we can specify the constraints on the object attributes. The attribute values specified in the object filter are case insensitive unless doublequoted. Wildcard is allowed in the attribute values. Suppose non 
You must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
ORA20000
: Index does not exist or insufficient privileges.
ORA20001
: Bad input value.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure gathers statistics for all fixed objects (dynamic performance tables).
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')));
Table 12741 GATHER_FIXED_OBJECTS_STATS Procedure Parameters
Parameter  Description 


The user statistics table identifier describing where to save the current statistics 

The (optional) identifier to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege to execute this procedure.
ORA20000
: Insufficient privileges.
ORA20001
: Bad input value.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure gathers index statistics. It attempts to parallelize as much of the work as possible. Restrictions are described in the individual parameters. This operation will not parallelize with certain types of indexes, including cluster indexes, domain indexes, and bitmap join indexes. The granularity
and no_invalidate
arguments are not relevant to these types of indexes.
DBMS_STATS.GATHER_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12742 GATHER_INDEX_STATS Procedure Parameters
Parameter  Description 


Schema of index to analyze 

Name of index 

Name of partition 

Percentage of rows to estimate ( 

User statistics table identifier describing where to save the current statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Does not invalidate the dependent cursors if set to 

Gather statistics on object even if it is locked 
ORA20000
: Index does not exist or insufficient privileges.
ORA20001
: Bad input value.
This procedure gathers statistics for all objects in a schema.
DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', objlist OUT ObjectTab, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE, obj_filter_list ObjectTab DEFAULT NULL); DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE'), force BOOLEAN DEFAULT FALSE, obj_filter_list ObjectTab DEFAULT NULL);
Table 12743 GATHER_SCHEMA_STATS Procedure Parameters
Parameter  Description 


Schema to analyze ( 

Percentage of rows to estimate ( 

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. 

Accepts:
size_clause is defined as size_clause :=  integer : Number of histogram buckets. Must be in the range [1,254]. REPEAT : Collects histograms only on the columns that already have histograms. AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gather statistics on the indexes as well. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant 

User statistics table identifier describing where to save the current statistics 

Identifier (optional) to associate with these statistics within 

Further specification of which objects to gather statistics for:


List of objects found to be stale or empty 

Schema containing 

Does not invalidate the dependent cursors if set to 

Gather statistics on objects even if they are locked 

A list of object filters. When provided, 
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.
ORA20000
: Schema does not exist or insufficient privileges.
ORA20001
: Bad input value.
Applying an Object Filter List
The following example specifies that any table with a "T
" prefix in the SAMPLE
schema and any table in the SYS
schema, if stale, will have statistics gathered upon it.
DECLARE filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB(); BEGIN filter_lst.extend(2); filter_lst(1).ownname := 'SAMPLE'; filter_lst(1).objname := 'T%'; filter_lst(2).ownname := 'SYS'; DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst, options => 'gather_stale'); END;
This procedure gathers system statistics.
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);
Table 12744 GATHER_SYSTEM_STATS Procedure Parameters
Parameter  Description 


Mode values are:


Time, in minutes, to gather statistics. This parameter applies only when 

Identifier of the user statistics table where the statistics will be saved 

Optional identifier associated with the statistics saved in the 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to gather system statistics.
ORA20004
: Error in the INTERVAL
mode: system parameter job_queue_processes
must be >0.
This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters.
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Table 12745 GATHER_TABLE_STATS Procedure Parameters
Parameter  Description 


Schema of table to analyze 

Name of table 

Name of partition 

Percentage of rows to estimate ( 

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. 

Accepts either of the following options, or both in combination:
 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. column_name : name of a column extension : can be either a column group in the format of (column_name , colume_name [, ...]) or an expression
The default is 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gathers statistics on the indexes for this table. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant 

User statistics table identifier describing where to save the current statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

Gather statistics of table even if it is locked 
Index statistics collection can be parellelized except for cluster, domain and join indexes.
ORA20000
: Table does not exist or insufficient privileges.
ORA20001
: Bad input value.
An extension can be either a column group (see Example 1) or an expression (see Example 2).
DBMS_STATS.GATHER_TABLE_STATS( 'SH', 'SALES', method_opt => 'FOR COLUMNS (empno, deptno)');
DBMS_STATS.GATHER_TABLE_STATS( 'SH', 'SALES', method_opt => 'FOR COLUMNS (sal+comm)');
This procedure generates object statistics from previously collected statistics of related objects. The currently supported objects are btree and bitmap indexes.
DBMS_STATS.GENERATE_STATS ( ownname VARCHAR2, objname VARCHAR2, organized NUMBER DEFAULT 7, force BOOLEAN default FALSE);
Table 12746 GENERATE_STATS Procedure Parameters
Parameter  Description 


Schema of object 

Name of object 

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

If 
For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.
ORA20000
: Unsupported object type of object does not exist.
ORA20001
: Invalid option or invalid statistics.
These procedures gets all columnrelated information. In the form of this procedure that deals with userdefined statistics, the statistics type returned is the type stored, in addition to the userdefined statistics.
DBMS_STATS.GET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, distcnt OUT NUMBER, density OUT NUMBER, nullcnt OUT NUMBER, srec OUT StatRec, avgclen OUT NUMBER, statown VARCHAR2 DEFAULT NULL);
Use the following for userdefined 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);
Table 12747 GET_COLUMN_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table to which this column belongs 

Name of the column or extension 

Name of the table partition from which to get the statistics. If the table is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics. If 

Identifier (optional) to associate with these statistics within 

The userdefined statistics 

Schema of the statistics type 

Name of the statistics type 

Number of distinct values 

Column density 

Number of 

Structure holding internal representation of column minimum, maximum, and histogram values 

Average length of the column (in bytes) 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object.
These procedures get all indexrelated information. In the form of this procedure that deals with userdefined statistics, the statistics type returned is the type stored, in addition to the userdefined statistics.
DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numlblks OUT NUMBER, numdist OUT NUMBER, avglblk OUT NUMBER, avgdblk OUT NUMBER, clstfct OUT NUMBER, indlevel OUT NUMBER, statown VARCHAR2 DEFAULT NULL, 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 userdefined 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);
Table 12748 GET_INDEX_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the index 

Name of the index partition for which to get the statistics. If the index is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics. If 

Identifier (optional) to associate with these statistics within 

The userdefined statistics 

Schema of the statistics type 

Name of the statistics type 

Number of rows in the index (partition) 

Number of leaf blocks in the index (partition) 

Number of distinct keys in the index (partition) 

Average integral number of leaf blocks in which each distinct key appears for this index (partition) 

Average integral number of data blocks in the table pointed to by a distinct key for this index (partition) 

Clustering factor for the index (partition) 

Height of the index (partition) 

Schema containing 

Guess quality for the index (partition) 

The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) 

The average cache hit ratio for the segment (index/table/index partition/table partition) 
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.
ORA20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object.
Note:
This subprogram has been replaced by improved technology and is maintained only for purposes of backward compatibility. In this case, use the GET_PREFS Function.See also Deprecated Subprograms.
This function returns the default value of parameters of DBMS_STATS
procedures.
DBMS_STATS.GET_PARAM ( pname IN VARCHAR2) RETURN VARCHAR2;
ORA20001
: Invalid input values
This function returns the default value of the specified preference.
DBMS_STATS.GET_PREFS ( pname IN VARCHAR2, ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
Table 12750 GET_PREFS Function Parameters
Parameter  Description 


Preference name. The default value for following preferences can be retrieved:



. 

. 

. 

. 
 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. column_name : name of a column extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is 
. 

. 
'

. 

. 
If the 
. 


Owner name 

Table name 
ORA20001
: Invalid input values
If the ownname and tabname are provided and a preference has been entered for the table, the function returns the preference as specified for the table. In all other cases it returns the global preference if it has been specified, otherwise the default value is returned.
This function returns oldest timestamp where statistics history is available.Users cannot restore statistics to a timestamp older than this one.
DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY RETURN TIMESTAMP WITH TIMEZONE;
This function returns the current retention value.
DBMS_STATS.GET_STATS_HISTORY_RETENTION RETURN NUMBER;
This procedure gets system statistics from stattab
, or from the dictionary if stattab
is NULL
.
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);
Table 12751 GET_SYSTEM_STATS Procedure Parameters
Parameter  Description 


Output is one of the following:


Date when statistics gathering started. If 

Date when statistics gathering stopped.


The parameter name to get, which can have one of the following values:


The parameter value to get 

Identifier of the user statistics table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary. 

Optional identifier associated with the statistics saved in the 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to gather system statistics.
ORA20004
: Parameter does not exist.
This procedure gets all tablerelated information.
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);
Table 12752 GET_TABLE_STATS Procedure Parameters
Parameter  Description 


Name of the schema. 

Name of the table to which this column belongs. 

Name of the table partition from which to get the statistics. If the table is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics. If 

Identifier (optional) to associate with these statistics within 

Number of rows in the table (partition). 

Number of blocks the table (partition) occupies. 

Average row length for the table (partition). 

Schema containing 

The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) 

The average cache hit ratio for the segment (index/table/index partition/table partition) 
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.
ORA20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object
Oracle does not support export or import of statistics across databases of different character sets.
This procedure retrieves statistics for a particular column from the user statistics table identified by stattab
and stores them in the dictionary.
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);
Table 12753 IMPORT_COLUMN_STATS Procedure Parameters
Parameter  Description 


The name of the schema 

The name of the table to which this column belongs 

Name of the column or extension 

The name of the table partition. If the table is partitioned and if 

The user statistics table identifier describing from where to retrieve the statistics 

The (optional) identifier to associate with these statistics within 

The schema containing 

Does not invalidate the dependent cursors if set to 

If set to 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20005
: Object statistics are locked.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure is used to import the statistics preferences of all the tables, excluding the tables owned by Oracle. These tables can by included by passing TRUE
for the add_sys
parameter.
DBMS_STATS.IMPORT_DATABASE_PREFS ( stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL add_sys IN BOOLEAN DEFAULT FALSE);
Table 12754 IMPORT_DATABASE_PREFS Procedure Parameters
Parameter  Description 


Statistics table name where to import the statistics 
statid 
Optional identifier to associate with these statistics within 
statown 
Schema containing 

Value 
ORA20000
: Insufficient privileges.
To run this procedure, you need to have the SYSDBA role, or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privileges.
Oracle does not support export or import of statistics across databases of different character sets.
DBMS_STATS.IMPORT_DATABASE_PREFS('STATTAB', statown=>'SH');
This procedure retrieves statistics for all objects in the database from the user statistics table(s) and stores them in the dictionary.
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);
Table 12755 IMPORT_DATABASE_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing from where to retrieve the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

Overrides statistics locked at the object (table) level:

ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
Oracle does not support export or import of statistics across databases of different character sets.
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.
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);
Table 12756 IMPORT_DICTIONARY_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing from where to retrieve the statistics 

The (optional) identifier to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

Overrides statistics lock at the object (table) level:

You must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
Oracle does not support export or import of statistics across databases of different character sets.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for fixed tables from the user statistics table(s) and stores them in the dictionary.
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);
Table 12757 IMPORT_FIXED_OBJECTS_STATS Procedure Parameters
Parameter  Description 


User statistics table identifier describing from where to retrieve the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

Overrides statistics lock:

You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege to execute this procedure.
Oracle does not support export or import of statistics across databases of different character sets.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for a particular index from the user statistics table identified by stattab
and stores them in the dictionary.
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);
Table 12758 IMPORT_INDEX_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the index 

Name of the index partition. If the index is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

Imports statistics even if index statistics are locked 
ORA20000
: Object does not exist or insufficient privileges
ORA20001
: Invalid or inconsistent values in the user statistics table
ORA20005
: Object statistics are locked
Oracle does not support export or import of statistics across databases of different character sets.
This procedure is used to import the statistics preferences of all the tables owned by the specified owner name.
DBMS_STATS.IMPORT_SCHEMA_PREFS ( ownname IN VARCHAR2, stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Table 12759 IMPORT_SCHEMA_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Statistics table name from where to import the statistics 
statid 
(Optional) Identifier to associate with these statistics within 
statown 
Schema containing 
ORA20000
: Object does not exist or insufficient privileges
To run this procedure, you need to connect as owner, or have the SYSDBA
privilege, or have the ANALYZE
ANY
system privilege.
All arguments are of type VARCHAR2
and values are enclosed in quotes.
Oracle does not support export or import of statistics across databases of different character sets.
DBMS_STATS.IMPORT_SCHEMA_PREFS('SH', 'STAT');
This procedure retrieves statistics for all objects in the schema identified by ownname from the user statistics table and stores them in the dictionary.
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);
Table 12760 IMPORT_SCHEMA_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

User statistics table identifier describing from where to retrieve the statistics 

Identifier (optional) to associate with these statistics within 

Schema containing 

Does not invalidate the dependent cursors if set to 

Overrides statistics locked at the object (table) level:

ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
Oracle does not support export or import of statistics across databases of different character sets.
http://usunnab06.us.oracle.com:80/servers/MifChecker/Out/Y10312_01.htm retrieves system statistics from the user statistics table, identified by stattab, and stores the statistics in the dictionary.
DBMS_STATS.IMPORT_SYSTEM_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 12761 IMPORT_SYSTEM_STATS Procedure Parameters
Parameter  Description 


Identifier of the user statistics table where the statistics will be retrieved 

Optional identifier associated with the statistics retrieved from the stattab 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to import system statistics.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure is used to set the statistics preferences of the specified table in the specified schema.
DBMS_STATS.IMPORT_TABLE_PREFS ( ownname IN VARCHAR2, tabname IN VARCHAR2, stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Table 12762 IMPORT_TABLE_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Table name 

Statistics table name from where to import the statistics 
statid 
(Optional) Identifier to associate with these statistics within stattab 
statown 
Schema containing stattab (if other than ownname) 
ORA20000
: Object does not exist or insufficient privileges.
To run this procedure, you need to connect as owner of the table, or have the ANALYZE
ANY
system privilege.
All arguments are of type VARCHAR2
and values are enclosed in quotes.
Oracle does not support export or import of statistics across databases of different character sets.
DBMS_STATS.IMPORT_TABLE_PREFS('SH', 'SALES', 'STAT');
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 statistics associated with the specified table being imported as well.
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);
Table 12763 IMPORT_TABLE_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table 

Name of the table partition. If the table is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics 

Identifier (optional) to associate with these statistics within 

If true, then column and index statistics for this table are also imported 

Schema containing 

Does not invalidate the dependent cursors if set to 

Imports statistics even if table statistics are locked 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure enables the user to lock statistics for a partition.
DBMS_STATS.LOCK_PARTITION_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2);
Table 12764 LOCK_PARTITION_STATS Procedure Parameters
Parameter  Description 


Name of the schema to lock 

Name of the table 

Partition name 
This procedure locks the statistics of all tables of a schema.
DBMS_STATS.LOCK_SCHEMA_STATS ( ownname VARCHAR2);
Table 12765 LOCK_SCHEMA_STATS Procedure Parameters
Parameter  Description 


The name of the schema to lock 
See "Usage Notes" for LOCK_TABLE_STATS Procedure.
This procedure locks the statistics on the table.
DBMS_STATS.LOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
Table 12766 LOCK_TABLE_STATS Procedure Parameters
Parameter  Description 


The name of the schema 

The name of the table 
When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
The SET_*,
DELETE_*,
IMPORT_*,
GATHER_*
procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS
) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.
This procedure either freezes the current set of the statistics or keeps the statistics empty (uncollected) to use Dynamic Sampling.
The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS
procedures.
These procedures convert userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using SET_COLUMN_STATS
.
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);
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);
Table 12767 PREPARE_COLUMN_VALUES Procedure Parameters
Parameter  Description 


Number of values specified in The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining heightbalanced or frequency histogram endpoint values (with inbetween values ordered from nextsmallest to nextlargest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to 

If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in 
Datatypespecific input parameters (use one) are shown in Table 12768.
Table 12768 DatatypeSpecific Input Parameters
Type  Description 


The array of values when the column type is characterbased. Up to the first 32 bytes of each string should be provided. Arrays must have between 2 and 256 entries, inclusive. If the datatype is fixed 

The array of values when the column type is datebased 

The array of values when the column type is doublebased 

The array of values when the column type is floatbased 

The array of values when the column type is numericbased 

The array of values when the column type is 

The minimum and maximum values when the column type is national character set based. No histogram information can be provided for a column of this type. If the datatype is fixed 

The minimum and maximum values when the column type is 
Table 12769 PREPARE_COLUMN_VALUES Procedure Output Parameters
Parameter  Description 


Internal representation of the minimum suitable for use in a call to 

Internal representation of the maximum suitable for use in a call to 

Array suitable for use in a call to 

Array suitable for use in a call to 
ORA20001
: Invalid or inconsistent input values.
This procedure converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using SET_COLUMN_STATS
.
DBMS_STATS.PREPARE_COLUMN_VALUES_NVARCHAR2 ( srec IN OUT StatRec, nvmin NVARCHAR2, nvmax NVARCHAR2);
pragma restrict_references(prepare_column_values_nvarchar, WNDS, RNDS, WNPS, RNPS);
Table 12770 PREPARE_COLUMN_VALUES_NVARCHAR2 Procedure Parameters
Parameter  Description 


Number of values specified in The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining heightbalanced or frequency histogram endpoint values (with inbetween values ordered from nextsmallest to nextlargest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to 

If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in 
Datatypespecific input parameters (use one) are shown in Table 12768.
Table 12771 DatatypeSpecific Input Parameters
Type  Description 


The minimum and maximum values when the column type is national character set based. No histogram information can be provided for a column of this type. If the datatype is fixed 
Table 12772 PREPARE_COLUMN_VALUES Procedure Output Parameters
Parameter  Description 


Internal representation of the minimum suitable for use in a call to 

Internal representation of the maximum suitable for use in a call to 

Array suitable for use in a call to 

Array suitable for use in a call to 
ORA20001
: Invalid or inconsistent input values.
This procedure converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using SET_COLUMN_STATS
.
DBMS_STATS.PREPARE_COLUMN_VALUES_ROWID ( srec IN OUT StatRec, rwmin ROWID, rwmax ROWID);
pragma restrict_references(prepare_column_values_rowid, WNDS, RNDS, WNPS, RNPS);
Table 12773 PREPARE_COLUMN_VALUES_ROWID Procedure Parameters
Parameter  Description 


Number of values specified in The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining heightbalanced or frequency histogram endpoint values (with inbetween values ordered from nextsmallest to nextlargest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to 

If you want a frequency distribution, then this array contains the number of occurrences of each distinct value specified in 
Datatypespecific input parameters (use one) are shown in Table 12768.
Table 12774 DatatypeSpecific Input Parameters
Type  Description 


The minimum and maximum values when the column type is 
Table 12775 PREPARE_COLUMN_VALUES Procedure Output Parameters
Parameter  Description 


Internal representation of the minimum suitable for use in a call to 

Internal representation of the maximum suitable for use in a call to 

Array suitable for use in a call to 

Array suitable for use in a call to 
ORA20001
: Invalid or inconsistent input values.
This procedure is used to publish the statistics gathered and stored as pending.
DBMS_STATS.PUBLISH_PENDING_STATS ( ownname IN VARCHAR2 DEFAULT USER, tabname IN VARCHAR2, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')), force IN BOOLEAN DEFAULT FALSE);
Table 12776 PUBLISH_PENDING_STATS Procedure Parameters
Parameter  Description 


Owner name 
tabname 
Table name 
no_invalidate 
Do not invalidate the dependent cursors if set to 
force 
If 
ORA20000
: Insufficient privileges
If the parameter tabname
is NULL
then publish applies to all tables of the specified schema.
The default owner/schema is the user who runs the procedure.
To run this procedure, you need to have the same privilege for gathering statistics on the tables that will be touched by this procedure.
DBMS_STATS.PUBLISH_PENDING_STATS ('SH', null);
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.
DBMS_STATS.PURGE_STATS( before_timestamp TIMESTAMP WITH TIME ZONE);
Table 12777 PURGE_STATS Procedure Parameters
Parameter  Description 


Versions of statistics saved before this timestamp are purged. If 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
This procedures sets global preference, such as CASCADE
, ESTIMATE_PERCENT
and GRANULARITY
, to default values. This reverses the global preferences set by the SET_GLOBAL_PREFS Procedure.
DBMS_STATS.RESET_GLOBAL_PREF_DEFAULTS;
Note:
This subprogram has been replaced by improved technology and is maintained only for purposes of backward compatibility. In this case, use the RESET_GLOBAL_PREF_DEFAULTS Procedure.See also Deprecated Subprograms.
This procedure resets the default values of all parameters to Oracle recommended values.
DBMS_STATS.RESET_PARAM_DEFAULTS;
This procedure restores statistics of all tables of the database as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_DATABSE_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')));
Table 12778 RESTORE_DATABASE_STATS Procedure Parameters
Parameter  Description 


The timestamp to which to restore statistics 

Restores statistics even if their statistics are locked 

Does not invalidate the dependent cursors if set to 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all dictionary tables (tables of 'SYS
', 'SYSTEM
' and RDBMS component schemas) as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_DICTIONARY_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')));
Table 12779 RESTORE_DICTIONARY_STATS Procedure Parameters
Parameter  Description 


The timestamp to which to restore statistics 

Restores statistics even if their statistics are locked 

Does not invalidate the dependent cursors if set to 
To run this procedure, you must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all fixed tables as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')));
Table 12780 RESTORE_FIXED_OBJECTS_STATS Procedure Parameters
Parameter  Description 


The timestamp to which to restore statistics 

Restores statistics even if their statistics are locked 

Does not invalidate the dependent cursors if set to 
To run this procedure, you must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all tables of a schema as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_SCHEMA_STATS( ownname VARCHAR2, as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')));
Table 12781 RESTORE_SCHEMA_STATS Procedure Parameters
Parameter  Description 


The schema of the tables for which the statistics are to be restored 

The timestamp to which to restore statistics 

Restores statistics even if their statistics are locked 

Does not invalidate the dependent cursors if set to 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores system statistics as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_SCHEMA_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE);
Table 12782 RESTORE_SYSTEM_STATS Procedure Parameters
Parameter  Description 


The timestamp to which to restore statistics 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of a table as of a specified timestamp (as_of_timestamp
). The procedure will restore statistics of associated indexes and columns as well. If the table statistics were locked at the specified timestamp the procedure will lock the statistics. The procedure will not restore user defined statistics.
DBMS_STATS.RESTORE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, as_of_timestamp TIMESTAMP WITH TIME ZONE, restore_cluster_index BOOLEAN DEFAULT FALSE, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')));
Table 12783 RESTORE_TABLE_STATS Procedure Parameters
Parameter  Description 


The schema of the table for which the statistics are to be restored 

The table name 

The timestamp to which to restore statistics 

If the table is part of a cluster, restore statistics of the cluster index if set to 

Restores statistics even if the table statistics are locked. If the table statistics were not locked at the specified timestamp, it unlocks the statistics. 

Does not invalidate the dependent cursors if set to 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure sets columnrelated information. In the version of this procedure that deals with userdefined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual userdefined statistics. If this statistics type is NULL
, the statistics type associated with the index or column is stored.
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 userdefined 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);
Table 12784 SET_COLUMN_STATS Procedure Parameters
Parameter  Description 


Name of the schema. 

Name of the table to which this column belongs. 

Name of the column or extension 

Name of the table partition in which to store the statistics. If the table is partitioned and 

User statistics table identifier describing where to store the statistics. If 

Identifier (optional) to associate with these statistics within 

The userdefined statistics 

Schema of the statistics type 

Name of the statistics type 

Number of distinct values 

Column density. If this value is 

Number of 



Average length for the column (in bytes) 

For internal Oracle use (should be left as 

Schema containing 

Does not invalidate the dependent cursors if set to 

Sets the values even if statistics of the column are locked 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent input values.
ORA20005
: Object statistics are locked.
This procedure is used to set the statistics preferences of all the tables, excluding the tables owned by Oracle. These tables can by included by passing TRUE
for the add_sys
parameter.
DBMS_STATS.SET_DATABASE_PREFS ( pname IN VARCHAR2, pvalue IN VARCHAR2, add_sys IN BOOLEAN DEFAULT FALSE);
Table 12785 SET_DATABASE_PREFS Procedure Parameters
Parameter  Description 


Preference name. The default value for following parameters can be set:

. 

. 

. 

. 
 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. column_name : name of a column extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is 
. 

. 
'

. 

. 
If the 
. 


Preference value. If 

Value 
ORA20000
: Insufficient privileges.
ORA20001
: Invalid or illegal input values.
To run this procedure, you need to have the SYSDBA
role or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privileges.
Both arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
DBMS_STATS.SET_DATABASE_PREFS('CASCADE', 'DBMS_STATS.AUTO_CASCADE'); DBMS_STATS.SET_DATABASE_PREFS('ESTIMATE_PERCENT','9'); DBMS_STATS.SET_DATABASE_PREFS('DEGREE','99');
This procedure is used to set the global statistics preferences.
DBMS_STATS.SET_GLOBAL_PREFS ( pname IN VARCHAR2, pvalue IN VARCHAR2);
Table 12786 SET_GLOBAL_PREFS Procedure Parameters
Parameter  Description 


Preference name. The default value for the following preferences can be set:



. 

. 

. 

. 
 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. column_name : name of a column extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is 
. 

. 
'

. 

. 
If the 
. 


Preference value. If 
ORA20000
: Insufficient privileges
ORA20001
: Invalid or illegal input values
This setting is honored only of there is no preference specified for the table to be analyzed.
To run this procedure, you need to have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege.
Both arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
DBMS_STATS.SET_GLOBAL_PREFS('ESTIMATE_PERCENT','9'); DBMS_STATS.SET_GLOBAL_PREFS('DEGREE','99');
These procedures set indexrelated information. In the version of this procedure that deals with userdefined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual userdefined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.
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 userdefined 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);
Table 12787 SET_INDEX_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the index 

Name of the index partition in which to store the statistics. If the index is partitioned and if 

User statistics table identifier describing where to store the statistics. If 

Identifier (optional) to associate with these statistics within 

The userdefined statistics 

Schema of the statistics type 

Name of the statistics type 

Number of rows in the index (partition) 

Number of leaf blocks in the index (partition) 

Number of distinct keys in the index (partition) 

Average integral number of leaf blocks in which each distinct key appears for this index (partition). If not provided, then this value is derived from 

Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, then this value is derived from 

See 

Height of the index (partition) 

For internal Oracle use (should be left as 

Schema containing 

Does not invalidate the dependent cursors if set to 

Guess quality. See the 

The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) 

The average cache hit ratio for the segment (index/table/index partition/table partition) 

Sets the values even if statistics of the index are locked 
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.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20005
: Object statistics are locked.
Note:
This subprogram has been replaced by improved technology and is maintained only for purposes of backward compatibility. In this case, use the SET_GLOBAL_PREFS Procedure.See also Deprecated Subprograms.
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.
DBMS_STATS.SET_PARAM ( pname IN VARCHAR2, pval IN VARCHAR2);
Table 12788 SET_PARAM Procedure Parameters
Parameter  Description 


The parameter name The default value for following parameters can be set.


The parameter value. If

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 even when they represent numbers.
Note also the difference between NULL
and 'NULL'
:
When NULL
is unquoted, this sets the parameter to the value Oracle recommends.
In the case of the quoted 'NULL'
, this sets the value of the parameter to NULL
.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or illegal input value.
DBMS_STATS.SET_PARAM('CASCADE','DBMS_STATS.AUTO_CASCADE'); DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','5'); DBMS_STATS.SET_PARAM('DEGREE','NULL');
This procedure is used to set the statistics preferences of all the tables owned by the specified owner name.
DBMS_STATS.SET_SCHEMA_PREFS ( ownname IN VARCHAR2, pname IN VARCHAR2, pvalue IN VARCHAR2);
Table 12789 SET_SCHEMA_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Preference name. The default value for following parameters can be set:


Preference value. If 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or illegal input value.
To run this procedure, you need to connect as owner, or have the SYSDBA
privilege, or have the ANALYZE
ANY
system privilege.
Both arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
DBMS_STATS.SET_SCHEMA_PREFS('SH','CASCADE', 'DBMS_STATS.AUTO_CASCADE'); DBMS_STATS.SET_SCHEMA_PREFS('SH' 'ESTIMATE_PERCENT','9'); DBMS_STATS.SET_SCHEMA_PREFS('SH', 'DEGREE','99');
This procedure sets systems statistics.
DBMS_STATS.SET_SYSTEM_STATS ( pname VARCHAR2, pvalue NUMBER, stattab IN VARCHAR2 DEFAULT NULL, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Table 12790 SET_SYSTEM_STATS Procedure Parameters
Parameter  Description 


The parameter name to get, which can have one of the following values:


Parameter value to get 

Identifier of the user statistics table where the statistics will be obtained. If stattab is null, the statistics will be obtained from the dictionary. 

Optional identifier associated with the statistics saved in the stattab 

Schema containing 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to set system statistics.
ORA20004
: Parameter does not exist.
This procedure is used to set the statistics preferences of the specified table in the specified schema.
DBMS_STATS.SET_TABLE_PREFS ( ownname IN VARCHAR2, tabname IN VARCHAR2, pname IN VARCHAR2, pvalue IN VARCHAR2);
Table 12791 SET_TABLE_PREFS Procedure Parameters
Parameter  Description 


Owner name 

Table name 

Preference name. The default value for following preferences can be set:

. 

. 

. 

. 
 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. column_name : name of a column extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is 
. 

. 
'

. 

. 
If the 
. 

pvalue 
Preference value. If 
ORA20000
: Object does not exist or insufficient privileges
ORA20001
: Invalid or illegal input values
To run this procedure, you need to connect as owner of the table or should have the ANALYZE
ANY
system privilege.
All arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'CASCADE', 'DBMS_STATS.AUTO_CASCADE'); DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES','ESTIMATE_PERCENT','9'); DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'DEGREE','99');
This procedure sets tablerelated information.
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);
Table 12792 SET_TABLE_STATS Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table 

Name of the table partition in which to store the statistics. If the table is partitioned and 

User statistics table identifier describing where to store the statistics. If 

Identifier (optional) to associate with these statistics within 

Number of rows in the table (partition) 

Number of blocks the table (partition) occupies 

Average row length for the table (partition) 

For internal Oracle use (should be left as 

Schema containing 

Does not invalidate the dependent cursors if set to 

The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) 

The average cache hit ratio for the segment (index/table/index partition/table partition) 

Sets the values even if statistics of the table are locked 
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.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20005
: Object statistics are locked.
This function returns the name of the statistics entry that is created for the userspecified extension. It raises an error if no extension has been created.
DBMS_STATS.SHOW_EXTENDED_STATS_NAME ( ownname VARCHAR2, tabname VARCHAR2, extension VARCHAR2) RETURN VARCHAR2;
Table 12793 SHOW_EXTENDED_STATS_NAME Function Parameters
Parameter  Description 


Owner name of a table 

Name of the table 

Can be either a column group or an expression. Suppose the specified table has two column 
ORA20000
: Object does not exist or insufficient privileges
ORA20001
: Error when processing extension
This procedure enables the user to unlock statistics for a partition.
DBMS_STATS.UNLOCK_PARTITION_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2);
Table 12794 UNLOCK_PARTITION_STATS Procedure Parameters
Parameter  Description 


Name of the schema to unlock 

Name of the table 

Partition name 
This procedure unlocks the statistics on all the tables in schema.
DBMS_STATS.UNLOCK_SCHEMA_STATS ( ownname VARCHAR2);
Table 12795 UNLOCK_SCHEMA_STATS Procedure Parameters
Parameter  Description 


The name of the schema 
When statistics on a table is locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
The SET_*,
DELETE_*,
IMPORT_*,
GATHER_*
procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS
) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.
This procedure unlocks the statistics on the table.
DBMS_STATS.UNLOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
Table 12796 UNLOCK_TABLE_STATS Procedure Parameters
Parameter  Description 


The name of the schema 

The name of the table 
When statistics on a table is locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
The SET_*,
DELETE_*,
IMPORT_*,
GATHER_*
procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
Procedures that operates on multiple objects (such as GATHER_SCHEMA_STATS
) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.
This procedure upgrades a user statistics table from an older version.
DBMS_STATS.UPGRADE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
Table 12797 UPGRADE_STAT_TABLE Procedure Parameters
Parameter  Description 


Name of the schema 

Name of the table 
ORA20000: Unable to upgrade table.