With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
See Also:
Oracle Database SQL Tuning GuideThis chapter contains the following topics:
Overview
Deprecated Subprograms
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 user-defined statistics for tables and domain indexes using this package.
For example, if the DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel
See Also:
Oracle Database SQL Tuning Guide for more information about "Managing Optimizer Statistics".Note:
Oracle recommends that you do not use deprecated subprograms. Support for deprecated features is for backward compatibility only.The following subprogram is deprecated with Oracle Database 12c and later:
GENERATE_STATS
This procdure is replaced by the GATHER_INDEX_STAT procedure.
See Also:
"GATHER_INDEX_STATS Procedure"Types for the minimum and maximum values and histogram endpoints include:
TYPE numarray IS VARRAY(2050) OF NUMBER; TYPE datearray IS VARRAY(2050) OF DATE; TYPE chararray IS VARRAY(2050) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(2050) OF RAW(2000); TYPE fltarray IS VARRAY(2050) OF BINARY_FLOAT; TYPE dblarray IS VARRAY(2050) OF BINARY_DOUBLE;
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 155-1:
Table 155-1 DBMS_STATS Constants
| Name | Type | Description |
|---|---|---|
|
|
|
Copies global preferences |
|
|
|
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 |
|
|
|
Lets Oracle decide when to invalidate dependent cursors |
|
|
|
Indicates that auto-sample size algorithms should be used |
|
|
|
A flag that can be passed to the PURGE_STATS Procedure and unconditionally deletes all the history statistics. The deletion uses |
|
|
|
A constant used for reclaiming synopsis table space. |
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 user-defined statistics for columns and domain indexes.
The statown, stattab, and statid parameters instruct the package to back up current statistics in the specified table before gathering new statistics.
Oracle also provides the following procedure for generating statistics for derived objects when you have sufficient statistics on related objects:
Use the following subprograms to store and retrieve individual column-related, index-related, and table-related statistics:
In the special versions of the SET_*_STATS procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or user statistics table:
User-defined statistics
Owner of statistics type
Name of statistics type
The user-defined statistics and the corresponding statistics type are inserted into the USTATS$ dictionary table. You can specify user-defined statistics without specifying the statistics type name.
The special versions of the GET_*_STATS procedures return user-defined statistics and the statistics type owner and name as OUT arguments corresponding to the schema object specified. If user-defined statistics are not collected, NULL values are returned.
The DELETE_* procedures delete both user-defined statistics and the standard statistics for the given schema object.
DELETE_TABLE_STATS, DELETE_DICTIONARY_STATS, DELETE_DATABASE_STATS and DELETE_SCHEMA_STATS have a parameter stat_category which specifies which statistics to delete. The parameter accepts multiple values separated by comma. The supported values are 'OBJECT_STATS' (table statistics, column statistics and index statistics) and'SYNOPSES' (auxiliary statistics created when statistics are incrementally maintained). The default is 'OBJECT_STATS, SYNOPSES'.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 untouched.When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
Restoring and Purging Statistics History
Use the following procedures to restore statistics as of a specified timestamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION Procedure.
The other DBMS_STATS procedures related to restoring statistics are:
PURGE_STATS Procedure: This procedure lets you manually purge old versions beyond a time stamp.
GET_STATS_HISTORY_RETENTION Function: This function gets the current statistics history retention value.
GET_STATS_HISTORY_AVAILABILITY Function: This function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.
RESTORE_* operations are not supported for user defined statistics.
The DBMS_STATS package supports operations on user-defined statistics. When a domain index or column is associated with a statistics type (using the associate statement), operations on the index or column manipulate user-defined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the GET_INDEX_STATS Procedures invokes the user-defined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate user-defined statistics.
SET_* and GET_* operations for user-defined statistics are also supported using a special version of the SET and GET interfaces for columns and indexes.
EXPORT_*, IMPORT_* and RESTORE_* operations are not supported for user defined statistics.
The package gathers 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_pending_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. 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 column group (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 extended statistics names in place of column names.
Related subprograms:
The following subprograms are obsolete with Oracle Database 11g Release 2 (11.2):
Instead, use GET_PREFS Function
Instead, use SET_GLOBAL_PREFS Procedure
RESET_PARAM_DEFAULTS Procedure
Instead use RESET_GLOBAL_PREF_DEFAULTS Procedure
The DBMS_STATS package defines a RECORD type.
This record type is provided for users in case they want to set column statistics manually. Its fields allow specifying column min/max values, as well as a histogram for a column.
TYPE STATREC IS RECORD ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY, chvals CHARARRAY, eavals RAWARRAY, rpcnts NUMARRAY, eavs NUMBER);
Table 155-2 STAT_REC Attributes
| Field | Description |
|---|---|
|
|
Number of buckets in histogram |
|
|
Minimum value |
|
|
Maximum value |
|
|
Array of bucket numbers |
|
|
Array of normalized end point values |
|
|
Array of dumped end point values |
|
|
Array of end point actual values |
|
|
Array of end point value frequencies |
|
|
A number indicating whether actual end point values are needed in the histogram. If using the PREPARE_COLUMN_VALUES Procedures, this field will be automatically filled. |
Table 155-3 DBMS_STATS Package Subprograms
| Subprogram | Description |
|---|---|
|
Changes the statistics history retention value |
|
|
Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value |
|
|
Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value |
|
|
Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific 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 column-related statistics |
|
|
Deletes the statistics preferences of all the tables |
|
|
Deletes statistics for the entire database |
|
|
Deletes statistics for all dictionary schemas (' |
|
|
Deletes statistics of all fixed tables |
|
|
Deletes index-related statistics |
|
|
Deletes the private statistics that have been collected but have not been published |
|
|
Deletes the processing rate of a given statistics source. If the source is not specified, it deletes the statistics of all the sources |
|
|
Deletes the statistics preferences of all the tables owned by the specified owner name |
|
|
Deletes schema-related statistics |
|
|
Deletes system statistics |
|
|
Deletes statistics preferences of the specified table in the specified schema |
|
|
Deletes table-related 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 |
|
|
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 |
|
|
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 in-memory monitoring information for all the tables to the dictionary |
|
|
Gathers statistics for all objects in the database |
|
|
Gathers statistics for dictionary schemas ' |
|
|
Gathers statistics of fixed objects |
|
|
Gathers index statistics |
|
|
Starts the job of gathering the processing rates which end after |
|
|
Gathers statistics for all objects in a schema |
|
|
Gathers system statistics |
|
|
Gathers table and column (and index) statistics |
|
|
Generates object statistics from previously collected statistics of related objects |
|
|
Gets all column-related information |
|
|
Gets all index-related information |
|
|
Gets the default value of parameters of |
|
|
Gets the default value of the specified preference |
|
|
Gets the oldest timestamp where statistics history is available |
|
|
Returns the current statistics history retention value |
|
|
Gets system statistics from |
|
|
Gets all table-related information |
|
|
Retrieves statistics for a particular column from the user statistics table identified by |
|
|
Imports the statistics preferences of all the tables |
|
|
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 |
|
|
Merges column usage information from a source database, by means of a |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure |
|
|
Publishes the statistics gathered and stored as pending |
|
|
Purges old versions of statistics saved in the dictionary |
|
|
Remaps the names of objects in the user statistics table |
|
|
Reports the recorded column (group) usage information |
|
|
Runs the auto statistics gathering job in reporting mode |
|
|
Runs the GATHER_DATABASE_STATS Procedures in reporting mode. |
|
|
Runs the GATHER_DICTIONARY_STATS Procedure in reporting mode |
|
|
Runs the GATHER_FIXED_OBJECTS_STATS Procedure in reporting mode |
|
|
Runs the GATHER_SCHEMA_STATS Procedures in reporting mode |
|
|
Runs the GATHER_TABLE_STATS Procedure in reporting mode |
|
|
Generates a report for the provided operation optionally in a particular pluggable database (PDB) in a multitenant environment |
|
|
Generates a report of all statistics operations that take place between two timestamps which may or may not have been provided |
|
|
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 ( |
|
|
Iterates over the SQL statements in the specified SQL tuning set, compiles them and seeds column usage information for the columns that appear in these statements |
|
|
Sets column-related information |
|
|
Sets the statistics preferences of all the tables |
|
|
Sets the global statistics preferences |
|
|
Sets index-related information |
|
|
Sets default values for parameters of |
|
|
Sets the value of rate of processing for a given operation |
|
|
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 table-related information |
|
|
Returns the name of the virtual column that is created for the user-specified extension |
|
|
Transfers statistics for specified table(s) from a remote database specified by |
|
|
Unlocks the statistics for a partition |
|
|
Unlocks the statistics on all the tables in 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.
Table 155-4 ALTER_STATS_HISTORY_RETENTION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The retention time in days. The statistics history will be retained for at least these many number of days.The valid range is [1,365000]. Also you can use the following values for special purposes:
|
To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
This procedure converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value. The minval, maxval, and eavals 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);
This procedure converts the internal representation of a a minimum value, maximum value, or histogram end point actual value. The minval, maxval and eavals fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.
This procedure converts the internal representation of a a minimum value, maximum value, or histogram end point actual value. The minval, maxval and eavals fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.
This procedure copies the statistics of the source [sub] partition to the destination [sub] partition. It also copies statistics of all dependent object such as columns and local indexes. If the statistics for source are not available then nothing is copied. It can optionally scale the statistics (such as the number of blks, or number of rows) based on the given scale_factor.
DBMS_STATS.COPY_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, srcpartname VARCHAR2, dstpartname VARCHAR2, scale_factor VARCHAR2 DEFAULT 1, flags NUMBER DEFAULT NULL, force BOOLEAN DEFAULT FALSE);
Table 155-8 COPY_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of the table of source and destination [sub] partitions |
|
|
Table name of source and destination [sub] partitions |
|
|
Source [sub] partition |
|
|
Destination [sub] partition |
|
|
Scale factor to scale |
|
|
For internal Oracle use (should be left as |
|
|
When value of this argument is |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
This procedure updates the minimum and maximum values of destination partition for the first partitioning column as follows:
If the partitioning type is HASH the minimum and maximum values of the destination partition are same as that of the source partition.
If the partitioning type is LIST then
If the destination partition is a NOT DEFAULT partition then
The minimum value of the destination partition is set to the minimum value of the value list that describes the destination partition
The maximum value of the destination partition is set to the maximum value of the value list that describes the destination partition.
Alternatively, if the destination partition is a DEFAULT partition, then
The minimum value of the destination partition is set to the minimum value of the source partition
The maximum value of the destination partition is set to the maximum value of the source partition
If the partitioning type is RANGE then
The minimum value of the destination partition is set to the high bound of previous partition unless the destination partition is the first partition. For the first partition, the minimum value is set to the high bound of the destination partition.
The maximum value of the destination partition is set to the high bound of the destination partition unless the high bound of the destination partition is MAXVALUE, in which case the maximum value of the destination partition is set to the high bound of the previous partition.
If the source partition column's minimum value is equal to its maximum value, and both are equal to the source partition's lower bound, and it has a single distinct value, then the destination partition column's minimum and maximum values are both set to the destination partition's lower bound. This is done for all partitioning columns.
If the above condition does not apply, second and subsequent partitioning columns are updated as follows. The destination partition column's maximum value is set to the greater of the destination partition upper bound and the source partition column's maximum value, with one exception. If the destination partition is D and its preceding partition is D-1 and the key column to be adjusted is Cn, the maximum value for Cn is set to the upper bound of D (ignoring the maximum value of the source partition column) provided that the upper bounds of the previous key column Cn-1 are the same in partitions D and D-1.
If the minimum and maximum values are different for a column after modifications, and if the number of distinct values is less than 1, the number of distinct values is updated as 2.
This procedure does not copy statistics of the underlying subpartitions if the source/destination is a partition of a composite partitioned table.
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.
This second form creates statistics extension based on the column group usage recorded by the SEED_COL_USAGE Procedure. This function returns a report of extensions created.
DBMS_STATS.CREATE_EXTENDED_STATS ( ownname VARCHAR2, tabname VARCHAR2, extension VARCHAR2) RETURN VARCHAR2; DBMS_STATS.CREATE_EXTENDED_STATS ( ownname VARCHAR2, tabname VARCHAR2, extension VARCHAR2) RETURN CLOB;
Table 155-9 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 |
ORA-20000: Insufficient privileges / creating extension is not supported
ORA-20001: Error when processing extension
ORA-20007: Extension already exists
ORA-20008: Reached the upper limit on number of extensions
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 non-virtual 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, global_temporary BOOLEAN DEFAULT FALSE);
Table 155-10 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. |
|
|
Whether or not the table should be created as a global temporary table |
ORA-20000: Table already exists or insufficient privileges
ORA-20001: Tablespace does not exist
This procedure deletes column-related statistics.
DBMS_STATS.DELETE_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
col_stat_type VARCHAR2 DEFAULT 'ALL');
Table 155-11 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:
|
ORA-20000: Object does not exist or insufficient privileges
ORA-20005: 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.
Table 155-12 DELETE_DATABASE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Preference name. The existing value for following preferences can be deleted and default preference values will be used:
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
|
|
|
|
|
|
. |
|
|
|
|
|
|
|
|
|
|
|
|
Determines whether |
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,
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Table 155-13 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 |
|
|
Statistics to delete. It accepts multiple values separated by comma:
The default is |
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')),
stattype VARCHAR2 DEFAULT 'ALL',
force BOOLEAN DEFAULT FALSE,
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Table 155-14 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 |
|
|
Statistics type |
|
|
When the value of this argument is |
|
|
Statistics to delete. It accepts multiple values separated by comma:
The default is |
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
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 155-15 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 |
|
|
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.
This procedure deletes index-related statistics.
DBMS_STATS.DELETE_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'ALL',
force BOOLEAN DEFAULT FALSE);
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Table 155-16 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 |
|
|
Statistics type |
|
|
When value of this argument is |
|
|
Statistics to delete. It accepts multiple values separated by comma:
The default is |
ORA-20000: Object does not exist or insufficient privileges
ORA-20005: Object statistics are locked
This procedure is used to delete the pending statistics that have been collected but have not been published.
If the parameter tabname is NULL 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.
This procedure deletes the processing rate of a given statistics source. If the source is not specified, it deletes the statistics of all the sources.
Table 155-18 DELETE_PROCESSING_RATE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Source of processing rates:
|
You require the OPTIMIZER_PROCESSING_RATE role to run this procedure since AUTO DOP uses processing rates to determine the optimal degree of parallelism for a SQL statement.
This procedure is used to delete the statistics preferences of all the tables owned by the specified owner name.
Table 155-19 DELETE_SCHEMA_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Preference name. The existing value for following preferences can be deleted and default preference values will be used:
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
|
|
|
|
|
|
. |
|
|
|
|
|
|
|
|
|
ORA-20000: Insufficient privileges / Schema "<schema>" does not exist
ORA-20001: 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.
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 155-20 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 |
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 155-21 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 |
|
|
Schema containing |
ORA-20000: Object does not exist or insufficient privileges
ORA-20002: 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 155-22 DELETE_TABLE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Table name |
|
|
Preference name. The existing value for following preferences can be deleted and default preference values will be used:
|
|
|
|
|
. |
|
|
|
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
. |
|
|
|
|
|
|
|
|
. |
|
|
|
|
|
|
|
|
|
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.
This procedure deletes table-related statistics.
DBMS_STATS.DELETE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
cascade_columns BOOLEAN DEFAULT TRUE,
cascade_indexes BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Table 155-23 DELETE_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table to which this column belongs |
|
|
Name of the table [sub]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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20002: 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 155-24 DIFF_TABLE_STATS_IN_HISTORY Function 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. |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 155-25 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. |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 155-26 DIFF_TABLE_STATS_IN_STATTAB Function 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.
Table 155-27 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 |
ORA-20000: Insufficient privileges or extension does not exist
ORA-20001: Error when processing extension
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
If no extended statistics set is created for the extension, this function throws an error.
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 155-29 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 |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 155-30 EXPORT_DATABASE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Statistics table name to where statistics should be exported |
|
|
(Optional) Identifier to associate with these statistics within |
|
|
Schema containing |
|
|
Value |
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.
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, stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-31 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 |
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
If |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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, stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-32 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 |
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
If |
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.
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 155-33 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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20002: Bad user statistics table, may need to upgrade it
This procedure retrieves statistics for a particular index and stores them in the user statistics table identified by stattab.
DBMS_STATS.EXPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 155-34 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 |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 155-35 EXPORT_PENDING_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Table name |
|
|
Statistics table name to where to export the statistics |
|
|
(Optional) Identifier to associate with these statistics within |
|
|
Schema containing |
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.
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);
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.
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, stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-37 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 |
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
If |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 155-38 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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20002: Bad user statistics table; may need to be upgraded
ORA-20003: Unable to export system statistics
This procedure 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 155-39 EXPORT_TABLE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Table name |
|
|
Statistics table name where to export the statistics |
|
|
Optional identifier to associate with these statistics within |
|
|
Schema containing |
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.
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, stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-40 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 |
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
If |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure flushes in-memory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS views are updated immediately, without waiting for the Oracle database to flush them periodically. This procedure is useful when you need up-to-date information in those views. Because the GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.
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 155-41 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. |
|
|
When setting preference on global, schema, database or dictionary level, only
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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, |
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 155-42 GATHER_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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:
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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 double-quoted. 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.
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 155-43 GATHER_FIXED_OBJECTS_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing where to save the current statistics |
|
|
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.
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 155-44 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 |
This procedure starts the job of gathering the processing rates which end after an interval defined in minutes.
DBMS_STATS.GATHER_PROCESSING_RATE ( gathering_mode IN VARCHAR2 DEFAULT 'START', interval IN NUMBER DEFAULT NULL);
Table 155-45 GATHER_PROCESSING_RATE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Mode: |
|
|
Time interval (number of minutes) for which the processing must be gathered |
You require the OPTIMIZER_PROCESSING_RATE role to run this procedure.
AUTO DOP uses processing rates to determine the optimal degree of parallelism for a SQL statement.
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 155-46 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:
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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, |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
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 := 'SH'; filter_lst(1).objname := 'SALES'; filter_lst(2).ownname := 'SH'; filter_lst(2).objname := 'COSTS'; DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SH',obj_filter_list=>filter_lst); 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 155-47 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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid input value
ORA-20002: Bad user statistics table; may need to be upgraded
ORA-20003: Unable to gather system statistics
ORA-20004: Error in the INTERVAL mode: system parameter job_queue_processes must be >0
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;
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')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
options VARCHAR2 DEFAULT 'GATHER');
Table 155-48 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. |
|
|
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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 |
|
|
Statistics type. The only value allowed is |
|
|
Gather statistics of table even if it is locked |
|
|
[Non-operative] |
|
|
Further specification of which objects to gather statistics:
|
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
Index statistics collection can be parellelized except for cluster, domain and join indexes.
Note:
This subprogram has been deprecated and replaced by improved technology. It is maintained only for purposes of backward compatibility. As an alternative, use theGATHER_INDEX_STAT procedure. See "GATHER_INDEX_STATS Procedure".This procedure generates object statistics from previously collected statistics of related objects. The currently supported objects are b-tree and bitmap indexes.
DBMS_STATS.GENERATE_STATS ( ownname VARCHAR2, objname VARCHAR2, organized NUMBER DEFAULT 7, force BOOLEAN default FALSE);
Table 155-49 GENERATE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of object |
|
|
Name of object |
|
|
Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have consecutive keys referencing different table blocks on disk. This parameter is only used for b-tree indexes. The number can be in the range of 0-10, with 0 representing a completely organized index and 10 a completely disorganized one. |
|
|
If |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.
These procedures gets all column-related information. In the form of this procedure that deals with user-defined statistics, the statistics type returned is the type stored, in addition to the user-defined statistics.
DBMS_STATS.GET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, distcnt OUT NUMBER, density OUT NUMBER, nullcnt OUT NUMBER, srec OUT StatRec, avgclen OUT NUMBER, statown VARCHAR2 DEFAULT NULL);
Use the following for user-defined statistics:
DBMS_STATS.GET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, ext_stats OUT RAW, stattypown OUT VARCHAR2 DEFAULT NULL, stattypname OUT VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Table 155-50 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 user-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of distinct values |
|
|
Column density |
|
|
Number of |
|
|
Structure holding internal representation of column minimum, maximum, and histogram values |
|
|
Average length of the column (in bytes) |
|
|
Schema containing |
ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object
Before invoking this procedure, ensure that the table exists.
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
These procedures get all index-related information. In the form of this procedure that deals with user-defined statistics, the statistics type returned is the type stored, in addition to the user-defined statistics.
DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numlblks OUT NUMBER, numdist OUT NUMBER, avglblk OUT NUMBER, avgdblk OUT NUMBER, clstfct OUT NUMBER, indlevel OUT NUMBER, statown VARCHAR2 DEFAULT NULL, cachedblk OUT NUMBER, cachehit OUT NUMBER); DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numlblks OUT NUMBER, numdist OUT NUMBER, avglblk OUT NUMBER, avgdblk OUT NUMBER, clstfct OUT NUMBER, indlevel OUT NUMBER, statown VARCHAR2 DEFAULT NULL, guessq OUT NUMBER, cachedblk OUT NUMBER, cachehit OUT NUMBER);
Use the following for user-defined statistics:
DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, ext_stats OUT RAW, stattypown OUT VARCHAR2 DEFAULT NULL, stattypname OUT VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, cachedblk OUT NUMBER, cachehit OUT NUMBER);
Table 155-51 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 |
|
|
User-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of rows in the index (partition) |
|
|
Number of leaf blocks in the index (partition) |
|
|
Number of distinct keys in the index (partition) |
|
|
Average integral number of leaf blocks in which each distinct key appears for this index (partition) |
|
|
Average integral number of data blocks in the table pointed to by a distinct key for this index (partition) |
|
|
Clustering factor for the index (partition) |
|
|
Height of the index (partition) |
|
|
Schema containing |
|
|
Guess quality for the index (partition) |
|
|
The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) |
|
|
The average cache hit ratio for the segment (index/table/index partition/table partition) |
ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object
Before invoking this procedure, ensure that the table exists.
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
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.
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 155-53 GET_PREFS Function Parameters
| Parameter | Description |
|---|---|
|
|
Preference name. The existing value for following preferences can be deleted and default preference values will be used:
|
|
|
|
|
. |
|
|
See Usage Notes below. |
|
|
. |
|
|
. |
|
|
|
When setting preference on global, schema, database or dictionary level, only
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
The default is |
|
. |
|
|
. |
'
|
|
. |
|
|
. |
If the |
|
|
|
|
|
|
|
. |
|
|
|
|
|
|
|
|
|
Further specification of which objects to gather statistics:
|
|
|
Owner name |
|
|
Table name |
ORA-20000: Unable to gather statistics concurrently: Resource Manager is not enabled.
ORA-20001: Invalid input values
No special privilege or role is needed to invoke this procedure, however note the following with regard to CONCURRENT preference.
The CONCURRENT preference determines whether the statistics of tables or (sub)partitions of tables to be gathered concurrently when user issues GATHER_*_STATS procedures. DBMS_STATS has the ability to collect statistics for a single object (table, (sub)partition) in parallel based on the value of degree parameter. However the parallelism is limited to one object. The CONCURRENT preference extends the scope of parallelization to multiple database objects by enabling users to concurrently gather statistics for multiple tables in a schema or database and multiple (sub)partitions within a table. Note that this is primarily intended for multi CPU systems and it may not be suitable for small databases on single CPU machines.
To gather statistics concurrently,
-The user must have DBA role or have the following privileges in addition to privileges that are required for gathering statistics: CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE.
Resource Manager should be enabled.
The setting for the job_queue_processes parameter must be at least 4.
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.
This function returns the current statistics history retention value.
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 IN VARCHAR2, pvalue OUT NUMBER, stattab IN VARCHAR2 DEFAULT NULL, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Table 155-54 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:
|
|
|
Parameter value to get |
|
|
Identifier of the user statistics table where the statistics will be obtained. If |
|
|
Optional identifier associated with the statistics saved in the |
|
|
Schema containing |
ORA-20000: Object does not exist or insufficient privileges
ORA-20002: Bad user statistics table; may need to be upgraded
ORA-20003: Unable to gather system statistics
ORA-20004: Parameter does not exist
This procedure gets all table-related information.
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 155-55 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) |
Before invoking this procedure, ensure that the table exists.
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
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 155-56 IMPORT_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 from where to retrieve the statistics |
|
|
Identifier to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
If set to |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values in the user statistics table
ORA-20005: Object statistics are locked
This procedure 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 155-57 IMPORT_DATABASE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Statistics table name where to import the statistics |
|
|
Optional identifier to associate with these statistics within |
|
|
Schema containing |
|
|
Value |
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.
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,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-58 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:
|
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
|
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values in the user statistics table
This procedure retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) from the user statistics table and stores them in the dictionary.
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,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-59 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:
|
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
|
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.
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 155-60 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.
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 155-61 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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values in the user statistics table
ORA-20005: Object statistics are locked
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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);
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.
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,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-63 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:
|
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
|
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values in the user statistics table
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure 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 155-64 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 |
|
|
Schema containing |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values in the user statistics table
ORA-20002: Bad user statistics table; may need to be upgraded
ORA-20003: Unable to import system statistics
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 155-65 IMPORT_TABLE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Table name |
|
|
Statistics table name from where to import the statistics |
|
|
(Optional) Identifier to associate with these statistics within stattab |
|
|
Schema containing |
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.
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,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Table 155-66 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, 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 |
|
|
Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:
|
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values in the user statistics table
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
Oracle does not support export or import of statistics across databases of different character sets.
This procedure locks the statistics of all tables of a schema.
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 statistics.
The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS procedures.
Neither the UNLOCK_SCHEMA_STATS Procedure nor the UNLOCK_TABLE_STATS Procedure is designed to unlock statistics of corresponding partitions. When you invoke the LOCK_TABLE_STATS Procedure, it sets the statistics lock bit at the table level. In that case, you cannot gather statistics on dependent objects such as partitions and indexes. By the same token, if table statistics are locked, the dependents are locked and you do not need to explicitly invoke the LOCK_PARTITION_STATS Procedure.
This procedure locks the statistics on the table.
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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 statistics.
The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS procedures.
Neither the UNLOCK_SCHEMA_STATS Procedure nor the UNLOCK_TABLE_STATS Procedure is designed to unlock statistics of corresponding partitions. When you invoke the LOCK_TABLE_STATS Procedure, it sets the statistics lock bit at the table level. In that case, you cannot gather statistics on dependent objects such as partitions and indexes. By the same token, if table statistics are locked, the dependents are locked and you do not need to explicitly invoke the LOCK_PARTITION_STATS Procedure.
This procedure merges column usage information from a source database by means of a dblink into the local database. If column usage information already exists for a given table or column MERGE_COL_USAGE will combine both the local and the remote information.
User must be SYS to execute this procedure. addition the user specified during the creation of the dblink is expected to have privileges to select from tables in the SYS schema.
These procedures convert user-specified minimum, maximum, and histogram endpoint actual 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);
Table 155-71 PREPARE_COLUMN_VALUES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Number of values specified in The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to |
|
|
If you want a frequency or hybrid histogram, this array contains the number of occurrences of each distinct value specified in |
|
|
If you want a hybrid histogram, this array contains the total frequency of values that are less than or equal to each distinct value specified in As an example, for a given array Note:
|
Datatype-specific input parameters (use one) are shown in Table 155-72.
Table 155-72 Datatype-Specific Input Parameters
| Type | Description |
|---|---|
|
|
The array of values when the column type is character-based. Up to the first 64 bytes of each string should be provided. Arrays must have between 2 and 2050 entries, inclusive. If the datatype is fixed |
|
|
Array of values when the column type is date-based |
|
|
Array of values when the column type is double-based |
|
|
Array of values when the column type is float-based |
|
|
Array of values when the column type is numeric-based |
|
|
Array of values when the column type is |
|
|
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 |
|
|
Minimum and maximum values when the column type is |
Table 155-73 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 |
|
|
Array suitable for use in a call to |
|
|
Array suitable for use in a call to |
This procedure converts user-specified minimum, maximum, and histogram endpoint actual values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures.
DBMS_STATS.PREPARE_COLUMN_VALUES_NVARCHAR ( srec IN OUT StatRec, nvmin NVARCHAR2, nvmax NVARCHAR2);
Table 155-74 PREPARE_COLUMN_VALUES_NVARCHAR Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Number of values specified in The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to |
|
|
If you want a frequency or hybrid histogram, then this array contains the number of occurrences of each distinct value specified in |
|
|
If you want a hybrid histogram, this array contains the total frequency of values that are less than or equal to each distinct value specified in As an example, for a given array Note:
|
Datatype-specific input parameters (use one) are shown in Table 155-72.
Table 155-75 PREPARE_COLUMN_VALUES_NVARCHAR Datatype-Specific Input Parameters
| Type | Description |
|---|---|
|
|
The minimum and maximum values when the column type is national character set based. No histogram information can be provided for a column of this type. If the datatype is fixed |
Table 155-76 PREPARE_COLUMN_VALUES_NVARCHAR 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 |
|
|
Array suitable for use in a call to |
|
|
Array suitable for use in a call to |
This procedure converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using SET_COLUMN_STATS.
Table 155-77 PREPARE_COLUMN_VALUES_ROWID Procedure Parameters
| Parameter | Description |
|---|---|
|
srec |
Values (
Values (
|
|
Number of values specified in The first corresponding array entry should hold the minimum value for the column, and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest). This value may be adjusted to account for compression, so the returned value should be left as is for a call to |
|
|
If you want a frequency or hybrid histogram, this array contains the number of occurrences of each distinct value specified in |
|
|
If you want a hybrid histogram, this array contains the total frequency of values that are less than or equal to each distinct value specified in As an example, for a given array Note:
|
|
|
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 |
|
|
Array suitable for use in a call to |
|
|
Array suitable for use in a call to |
Datatype-specific input parameters (use one) are shown in Table 155-72.
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 155-79 PUBLISH_PENDING_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Table name |
|
|
Do not invalidate the dependent cursors if set to |
|
|
If |
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.
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.
Table 155-80 PURGE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Versions of statistics saved before this timestamp are purged. If |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values
This procedure remaps the names of objects in the user statistics table. It allows you to import the statistics to objects with same definition but with different names.
DBMS_STATS.REMAP_STAT_TABLE ( ownname IN VARCHAR2, stattab IN VARCHAR2, src_own IN VARCHAR2, src_tab IN VARCHAR2, tgt_own IN VARCHAR2, tgt_tab IN VARCHAR2);
Table 155-81 REMAP_STAT_TABLE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner of the statistics table. |
|
|
User statistics table identifier |
|
|
Owner of the table to be renamed. This argument cannot be |
|
|
Name of the table to be renamed. If |
|
|
New name of the owner of the table. The owner name is also updated for the dependent objects such as columns and indexes. Note that an index of |
|
|
New name of the table. This argument is valid only if |
The following statement remaps all objects of sh to shsave in user statistics table sh.ustat:
DBMS_STATS.REMAP_STAT_TABLE ('sh', 'ustat', 'sh', NULL, 'shsave', NULL);
The following statement can be used to import statistics into objects of shsave once the preceding remap procedure is completed:
DBMS_STATS.IMPORT_SCHEMA_STATS ('shsave', 'ustat', statown => 'sh');
The following statement remaps sh.customers to shsave.customers_sav:
DBMS_STATS.REMAP_STAT_TABLE ('sh', 'ustat', 'sh', 'customers','shsave', 'customers_sav');
This function runs the auto statistics gathering job in reporting mode. That is, statistics are not actually collected, but all the objects that will be affected when auto statistics gathering is invoked are reported.
DBMS_STATS.REPORT_GATHER_AUTO_STATS ( detail_level VARCHAR2 DEFAULT 'TYPICAL', format VARCHAR2 DEFAULT 'TEXT') RETURN CLOB;
Table 155-83 REPORT_GATHER_AUTO_STATS Function Parameters
| Parameter | Description |
|---|---|
|
|
Detail level for the content of the report
Note that several fields (such as job name, estimated task cost) in the report are populated only when an operation is executed concurrently ( |
|
|
Report format:
|
This function runs the GATHER_DATABASE_STATS Procedures in reporting mode. That is, statistics are not actually collected, but all the objects that will be affected when GATHER_DATABASE_STATS is invoked are reported. The input set of parameters are exactly the same as in GATHER_DATABASE_STATS with two extra parameters.
DBMS_STATS.REPORT_GATHER_DATABASE_STATS (
estimate_percent IN NUMBER DEFAULT to_estimate_percent_type (
GET_PARAM('ESTIMATE_PERCENT')),
block_sample IN BOOLEAN DEFAULT FALSE,
method_opt IN VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
degree IN NUMBER DEFAULT TO_DEGREE_TYPE(
GET_PARAM('DEGREE')),
granularity IN VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade IN BOOLEAN DEFAULT to_cascade_type (
GET_PARAM('CASCADE')),
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
options IN VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown IN VARCHAR2 DEFAULT NULL,
gather_sys IN BOOLEAN DEFAULT TRUE,
no_invalidate IN BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE (
GET_PARAM('NO_INVALIDATE')),
obj_filter_list IN ObjectTab DEFAULT NULL,
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
DBMS_STATS.REPORT_GATHER_DATABASE_STATS (
estimate_percent IN NUMBER DEFAULT to_estimate_percent_type (
GET_PARAM('ESTIMATE_PERCENT')),
block_sample IN BOOLEAN DEFAULT FALSE,
method_opt IN VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
degree IN NUMBER DEFAULT TO_DEGREE_TYPE(
GET_PARAM('DEGREE')),
granularity IN VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade IN BOOLEAN DEFAULT to_cascade_type (
GET_PARAM('CASCADE')),
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
options IN VARCHAR2 DEFAULT 'GATHER',
statown IN VARCHAR2 DEFAULT NULL,
gather_sys IN BOOLEAN DEFAULT TRUE,
no_invalidate IN BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE (
GET_PARAM('NO_INVALIDATE')),
obj_filter_list IN ObjectTab DEFAULT NULL,
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
Table 155-84 REPORT_GATHER_DATABASE_STATS Function 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:
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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, |
|
|
Detail level for the content of the report
Note that several fields (such as job name, estimated task cost) in the report are populated only when an operation is executed concurrently ( |
|
|
Report format:
|
This function runs the GATHER_DICTIONARY_STATS Procedure in reporting mode. That is, statistics are not actually collected, but all objects affected when GATHER_DICTIONARY_STATS is invoked are reported. The input set of parameters are exactly the same as in GATHER_DICTIONARY_STATS with two extra parameters.
DBMS_STATS.REPORT_GATHER_DICTIONARY_STATS (
comp_id IN VARCHAR2 DEFAULT NULL,
estimate_percent IN NUMBER DEFAULT TO_ESTIMATE_PERCENT_TYPE
(GET_PARAM('ESTIMATE_PERCENT')),
block_sample IN BOOLEAN DEFAULT FALSE,
method_opt IN VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
degree IN NUMBER DEFAULT TO_DEGREE_TYPE
(GET_PARAM('DEGREE')),
granularity IN VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade IN BOOLEAN DEFAULT TO_CASCADE_TYPE
(GET_PARAM('CASCADE')),
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
options IN VARCHAR2 DEFAULT 'GATHER AUTO',
objlist OUT ObjectTab,
statown IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE
(GET_PARAM('NO_INVALIDATE')),
obj_filter_list IN ObjectTab DEFAULT NULL,
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
DBMS_STATS.REPORT_GATHER_DICTIONARY_STATS (
comp_id IN VARCHAR2 DEFAULT NULL,
estimate_percent IN NUMBER DEFAULT TO_ESTIMATE_PERCENT_TYPE
(GET_PARAM('ESTIMATE_PERCENT')),
block_sample IN BOOLEAN DEFAULT FALSE,
method_opt IN VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
degree IN NUMBER DEFAULT TO_DEGREE_TYPE
(GET_PARAM('DEGREE')),
granularity IN VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade IN BOOLEAN DEFAULT TO_CASCADE_TYPE
(GET_PARAM('CASCADE')),
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
options IN VARCHAR2 DEFAULT 'GATHER AUTO',
statown IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE
(GET_PARAM('NO_INVALIDATE')),
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
Table 155-85 REPORT_GATHER_DICTIONARY_STATS Function Parameters
| Parameter | Description |
|---|---|
|
|
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:
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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 double-quoted. Wildcard is allowed in the attribute values. Suppose non- |
|
|
Detail level for the content of the report
Note that several fields (such as job name, estimated task cost) in the report are populated only when an operation is executed concurrently ( |
|
|
Report format:
|
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
This function runs the GATHER_FIXED_OBJECTS_STATS Procedure in reporting mode. That is, statistics are not actually collected, but all the objects that will be affected when GATHER_FIXED_OBJ_STATS is invoked are reported. The input set of parameters are exactly the same as in GATHER_FIXED_OBJ_STATS with two extra parameters.
DBMS_STATS.REPORT_GATHER_FIXED_OBJ_STATS (
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE (
GET_PARAM('NO_INVALIDATE')),
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
Table 155-86 REPORT_GATHER_FIXED_OBJ_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing where to save the current statistics |
|
|
Identifier to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Detail level for the content of the report
Note that several fields (such as job name, estimated task cost) in the report are populated only when an operation is executed concurrently ( |
|
|
Report format:
|
You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
This function runs the GATHER_SCHEMA_STATS Procedures in reporting mode. That is, statistics are not actually collected, but all the objects that will be affected when GATHER_SCHEMA_STATS is invoked are reported. The input set of parameters are exactly the same as in GATHER_SCHEMA_STATS with two extra parameters.
DBMS_STATS.REPORT_GATHER_SCHEMA_STATS (
ownname IN VARCHAR2,
estimate_percent IN NUMBER DEFAULT TO_ESTIMATE_PERCENT_TYPE (
GET_PARAM ('ESTIMATE_PERCENT')),
block_sample IN BOOLEAN DEFAULT FALSE,
method_opt IN VARCHAR2 DEFAULT GET_PARAM ('METHOD_OPT'),
degree IN NUMBER DEFAULT TO_DEGREE_TYPE (
GET_PARAM('DEGREE')),
granularity IN VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade IN BOOLEAN DEFAULT TO_CASCADE_TYPE (
GET_PARAM ('CASCADE')),
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
options IN VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE (
GET_PARAM ('NO_INVALIDATE')),
force IN BOOLEAN DEFAULT FALSE,
obj_filter_list IN ObjectTab DEFAULT NULL,
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
DBMS_STATS.REPORT_GATHER_SCHEMA_STATS (
ownname IN VARCHAR2,
estimate_percent IN NUMBER DEFAULT TO_ESTIMATE_PERCENT_TYPE (
GET_PARAM ('ESTIMATE_PERCENT')),
block_sample IN BOOLEAN DEFAULT FALSE,
method_opt IN VARCHAR2 DEFAULT GET_PARAM ('METHOD_OPT'),
degree IN NUMBER DEFAULT TO_DEGREE_TYPE (
GET_PARAM('DEGREE')),
granularity IN VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade IN BOOLEAN DEFAULT TO_CASCADE_TYPE (
GET_PARAM ('CASCADE')),
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
options IN VARCHAR2 DEFAULT 'GATHER',
statown IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE (
GET_PARAM ('NO_INVALIDATE')),
force IN BOOLEAN DEFAULT FALSE,
obj_filter_list IN ObjectTab DEFAULT NULL,
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
Table 155-87 REPORT_GATHER_SCHEMA_STATS Function 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:
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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, |
|
|
Detail level for the content of the report
Note that several fields (such as job name, estimated task cost) in the report are populated only when an operation is executed concurrently ( |
|
|
Report format:
|
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
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 runs the GATHER_TABLE_STATS Procedure in reporting mode. That is, statistics are not actually collected, but all the objects that will be affected when GATHER_TABLE_STATS is invoked are reported.
DBMS_STATS.REPORT_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')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE)
detail_level VARCHAR2 DEFAULT 'TYPICAL', format VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
Table 155-88 REPORT_GATHER_TABLE_STATS Function 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,2048].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which 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 |
|
|
Statistics type. The only value allowed is |
|
|
Gather statistics of table even if it is locked |
|
|
Detail level for the content of the report
Note that several fields (such as job name, estimated task cost) in the report are populated only when an operation is executed concurrently ( |
|
|
Report format:
|
This function generates a report for the provided operation optionally in a particular pluggable database (PDB) in a multitenant environment.
DBMS_STATS.REPORT_SINGLE_STATS_OPERATIONS ( opid NUMBER, detail_level VARCHAR2 DEFAULT 'TYPICAL', format VARCHAR2 DEFAULT 'TEXT' container_id NUMBER DEFAULT NULL) RETURN CLOB;
Table 155-89 REPORT_SINGLE_STATS_OPERATION Function Parameters
| Parameter | Description |
|---|---|
|
|
Operation ID |
|
|
Detail level for the content of the report
Note that several fields (such as job name, estimated task cost) in the report are populated only when an operation is executed concurrently ( |
|
|
Report format:
|
|
|
ID of the pluggable database (PDB) on which this operation was performed. Note that in a multitenant environment, operation ID does not uniquely identify an operation. That is, different operations from distinct PDBs may have the same operation ID. Hence, in a multitenant environment, if a PDB ID is not provided, then the report may contain multiple operations. In a typical (non-CDB) database environment, operation ID is unique to each operation. |
This function generates a report of all statistics operations that take place between two timestamps which may or may not have been provided. It allows the scope of the report to be narrowed down so that report will include only auto statistics gathering runs. Furthermore, in a multitenant environment, users may optionally provide a set of pluggable database (PDB) IDs so that only statistics operations from the specified pluggable databases will be reported.
DBMS_STATS.REPORT_STATS_OPERATIONS ( detail_level VARCHAR2 DEFAULT 'TYPICAL', format VARCHAR2 DEFAULT 'TEXT', latestN NUMBER DEFAULT NULL, since TIMESTAMP WITH TIME ZONE DEFAULT NULL, until TIMESTAMP WITH TIME ZONE DEFAULT NULl, auto_only BOOLEAN DEFAULT FALSE, container_ids DBMS_UTILITY.NUMBER_ARRAY DEFAULT DBMS_STATS.NULL_NUMTAB) RETURN CLOB;
Table 155-90 REPORT_STATS_OPERATIONS Function Parameters
| Parameter | Description |
|---|---|
|
|
Detail level for the content of the report
|
|
|
Report format:
|
|
|
Restricts the report to contain only the latest N operations that took place between the provided time points (since and until). The default value is |
|
|
The report will include only statistics operations that started after this timestamp. |
|
|
The report will include only statistics operations that before after this timestamp. |
|
|
When |
|
|
A multitenant environment contains one or more pluggable databases (PDBs). |
To invoke this procedure you need the ANALYZE ANY privilege and the ANALYZE ANY DICTIONARY privilege.
Note that the type for container_ids input parameter is DBMS_UTILITY.NUMBER_ARRAY which is an associative PL/SQL array collection. Although associative array type allows for more flexible harvals table-like organization of entries, this function treats container_ids as a regular table collection with the first ID located at index 1 and the last id located at index container_ids.count without any empty array slot left between any two IDs. An example for 3 container ids is provided.
DECLARE
conid_tab DBMS_UTILITY.NUMBER_ARRAY;
report clob;
BEGIN
conid_tab(1) := 124;
conid_tab(2) := 63;
conid_tab(3) := 98;
report := DBMS_STATS.REPORT_STATS_OPERATIONS (container_ids => conid_tab);
END;
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.
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.
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 155-91 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 |
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 155-92 RESTORE_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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.
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 155-93 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.
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 155-94 RESTORE_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values
ORA-20006: Unable to restore statistics, statistics history not available
This procedure restores system statistics as of a specified timestamp (as_of_timestamp).
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values
ORA-20006: Unable to restore statistics, statistics history not available
This procedure restores statistics of a table as of a specified timestamp (as_of_timestamp). The procedure will restore statistics of associated indexes and columns as well. If the table statistics were locked at the specified timestamp the procedure will lock the statistics. The procedure will not restore user defined statistics.
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 155-96 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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent values
ORA-20006: Unable to restore statistics, statistics history not available
This procedure seeds column usage information from a statements in the specified SQL tuning set. It iterates over the SQL statements in the specified SQL tuning set, compiles them and seeds column usage information for the columns that appear in these statements. You can monitor the workload on the system for given amount of time and seed the and seed the column usage information based on the columns that appear in statements executed during the monitoring window.
DBMS_STATS.SEED_COL_USAGE ( sqlset_name IN VARCHAR2, owner_name IN VARCHAR2, time_limit IN POSITIVE DEFAULT NULL);
To invoke this procedure you need the ANALYZE ANY privilege and the ANALYZE ANY DICTIONARY privilege.
This procedure also records group of columns. Extensions for the recorded group of columns can be created using the CREATE_EXTENDED_STATS Function procedure. If sqlset_name and owner_name are NULL, it records the column (group) usage information for the statements executed in the system in next time_limit seconds.
This procedure sets column-related information. In the version of this procedure that deals with user-defined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual user-defined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.
DBMS_STATS.SET_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
distcnt NUMBER DEFAULT NULL,
density NUMBER DEFAULT NULL,
nullcnt NUMBER DEFAULT NULL,
srec StatRec DEFAULT NULL,
avgclen NUMBER DEFAULT NULL,
flags NUMBER DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Use the following for user-defined statistics:
DBMS_STATS.SET_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
ext_stats RAW,
stattypown VARCHAR2 DEFAULT NULL,
stattypname VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Table 155-98 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 |
|
|
User-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of distinct values |
|
|
Column density. If this value is |
|
|
Number of |
|
|
|
|
|
Average length for the column (in bytes) |
|
|
For internal Oracle use (should be left as |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Sets the values even if statistics of the column are locked |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or inconsistent input values
ORA-20005: 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 155-99 SET_DATABASE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Preference name. The existing value for following preferences can be deleted and default preference values will be used:
|
|
. |
|
|
. |
|
|
. |
|
|
|
When setting preference on global, schema, database or dictionary level, only
- integer : Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
The default is |
|
. |
|
|
. |
'
|
|
. |
|
|
. |
If the |
|
|
|
|
|
|
|
. |
|
|
|
|
|
|
|
|
|
|
|
|
Preference value. If |
|
|
Value |
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.
This procedure is used to set the global statistics preferences.
Table 155-100 SET_GLOBAL_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Preference name. The default value for the following preferences can be set:
|
|
|
|
|
. |
|
|
|
|
|
. |
|
|
. |
|
|
|
|
|
. |
'
|
|
. |
If the |
|
. |
- integer: Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns- SKEWONLY : Oracle determines the columns on which 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 |
|
. |
|
|
. |
|
|
. |
|
|
|
|
|
|
|
|
|
Preference value. If |
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.
These procedures set index-related information. In the version of this procedure that deals with user-defined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual user-defined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.
DBMS_STATS.SET_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
numrows NUMBER DEFAULT NULL,
numlblks NUMBER DEFAULT NULL,
numdist NUMBER DEFAULT NULL,
avglblk NUMBER DEFAULT NULL,
avgdblk NUMBER DEFAULT NULL,
clstfct NUMBER DEFAULT NULL,
indlevel NUMBER DEFAULT NULL,
flags NUMBER DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
guessq NUMBER DEFAULT NULL,
cachedblk NUMBER DEFAULT NULL,
cachehit NUMBER DEFUALT NULL,
force BOOLEAN DEFAULT FALSE);
Use the following for user-defined statistics:
DBMS_STATS.SET_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
ext_stats RAW,
stattypown VARCHAR2 DEFAULT NULL,
stattypname VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
cachedblk NUMBER DEFAULT NULL,
cachehit NUMBER DEFUALT NULL,
force BOOLEAN DEFAULT FALSE);
Table 155-101 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 |
|
|
User-defined statistics |
|
|
Schema of the statistics type |
|
|
Name of the statistics type |
|
|
Number of rows in the index (partition) |
|
|
Number of leaf blocks in the index (partition) |
|
|
Number of distinct keys in the index (partition) |
|
|
Average integral number of leaf blocks in which each distinct key appears for this index (partition). If not provided, then this value is derived from |
|
|
Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, then this value is derived from |
|
|
See |
|
|
Height of the index (partition) |
|
|
For internal Oracle use (should be left as |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Guess quality. See the |
|
|
The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) |
|
|
The average cache hit ratio for the segment (index/table/index partition/table partition) |
|
|
Sets the values even if statistics of the index are locked |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
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.
Table 155-102 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.
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid or illegal input value
This procedure sets the value of rate of processing for a given operation.
Table 155-103 SET_PROCESSING_RATE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the operation |
|
|
Processing rate. Valid values are as follows: |
You require the OPTIMIZER_PROCESSING_RATE role to run this procedure.
AUTO DOP uses processing rates to determine the optimal degree of parallelism for a SQL statement.
This procedure is used to set the statistics preferences of all the tables owned by the specified owner name.
Table 155-104 SET_SCHEMA_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Preference name. The default value for the following preferences can be set:
|
|
. |
|
|
. |
|
|
. |
|
|
|
|
|
. |
'
|
|
. |
If the |
|
|
|
|
|
|
|
. |
- integer: Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns- SKEWONLY : Oracle determines the columns on which 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 |
|
. |
|
|
. |
|
|
. |
|
|
|
|
|
|
|
|
|
Preference value. If |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: 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.
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 155-105 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 |
|
|
Optional identifier associated with the statistics saved in the |
|
|
Schema containing |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid input value
ORA-20002: Bad user statistics table; may need to be upgraded
ORA-20003: Unable to set system statistics
ORA-20004: Parameter does not exist
This procedure 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 155-106 SET_TABLE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Table name |
|
|
Preference name. The default value for following preferences can be set:
|
|
. |
|
|
|
|
|
. |
|
|
. |
|
|
|
|
|
. |
'
|
|
. |
If the |
|
|
|
|
|
|
|
. |
- integer: Number of histogram buckets. Must be in the range [1,2048].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns- SKEWONLY : Oracle determines the columns on which 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 |
|
. |
|
|
. |
|
|
. |
|
|
|
|
|
|
Preference value. If |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: 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.
This procedure sets table-related 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 155-107 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 |
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
This function returns the name of the statistics entry that is created for the user-specified 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 155-108 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 |
ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Error when processing extension
This procedure transfers statistics for specified table(s) from a remote database specified by dblink to the local database. The statistics at the source database are retained. It likewise transfers statistics-related structures such as synopses and DML monitoring information.
DBMS_STATS.TRANSFER_STATS ( ownname IN VARCHAR2, tabname IN VARCHAR2, dblink IN VARCHAR2, options IN NUMBER DEFAULT NULL);
Table 155-109 TRANSFER_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name of a table. If |
|
|
Name of the table. If |
|
|
Database link name |
|
|
By default the procedure does not transfer the global preferences. Specifying |
This procedure enables the user to unlock statistics for a partition.
This procedure unlocks the statistics on all the tables in schema.
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
Neither the UNLOCK_SCHEMA_STATS Procedure nor the UNLOCK_TABLE_STATS Procedure is designed to unlock statistics of corresponding partitions. When you invoke theLOCK_TABLE_STATS Procedure, it sets the statistics lock bit at the table level. In that case, you cannot gather statistics on dependent objects such as partitions and indexes. By the same token, if table statistics are locked, the dependents are locked and you do not need to explicitly invoke the LOCK_PARTITION_STATS Procedure.
This procedure unlocks the statistics on the table.
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.
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.
Neither the UNLOCK_SCHEMA_STATS Procedure nor the UNLOCK_TABLE_STATS Procedure is designed to unlock statistics of corresponding partitions. When you invoke theLOCK_TABLE_STATS Procedure, it sets the statistics lock bit at the table level. In that case, you cannot gather statistics on dependent objects such as partitions and indexes. By the same token, if table statistics are locked, the dependents are locked and you do not need to explicitly invoke the LOCK_PARTITION_STATS Procedure.