| Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1) Part Number B28419-03 |
|
|
View PDF |
| Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1) Part Number B28419-03 |
|
|
View PDF |
With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
See Also:
Oracle Database Performance Tuning GuideThis chapter contains the following topics:
Overview
Types
Constants
Operational Notes
Deprecated Subprograms
Examples
This section contains topics which relate to using the DBMS_STATS package.
The Oracle RDBMS allows you to collect statistics of many different kinds as an aid to improving performance. This package is concerned with optimizer statistics only. Given that Oracle sets automatic statistics collection of this kind on by default, this package is intended for only specialized cases.
The statistics of interest to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package.
For example, if the DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel
See Also:
Oracle Database Performance Tuning Guide for more information about "Managing Optimizer Statistics".Types for the minimum and maximum values and histogram endpoints include:
TYPE numarray IS VARRAY(256) OF NUMBER; TYPE datearray IS VARRAY(256) OF DATE; TYPE chararray IS VARRAY(256) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(256) OF RAW(2000); TYPE fltarray IS VARRAY(256) OF BINARY_FLOAT; TYPE dblarray IS VARRAY(256) OF BINARY_DOUBLE; TYPE StatRec IS RECORD ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY);
Types for listing stale tables include:
TYPE ObjectElem IS RECORD ( ownname VARCHAR2(30), -- owner objtype VARCHAR2(6), -- 'TABLE' or 'INDEX' objname VARCHAR2(30), -- table/index partname VARCHAR2(30), -- partition subpartname VARCHAR2(30)); -- subpartition 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 127-1:
Table 127-1 DBMS_STATS Constants
| Name | Type | Value | Description |
|---|---|---|---|
|
|
|
|
Lets Oracle decide whether to collect statistics for indexes or not |
|
|
|
|
Lets Oracle select the degree of parallelism based on size of the object, number of CPUs and initialization parameters |
|
|
|
NULL |
Lets Oracle decide when to invalidate dependent cursors |
|
|
|
|
Indicates that auto-sample size algorithms should be used |
The DBMS_STATS subprograms perform the following general operations:
Most of the DBMS_STATS procedures include the three parameters statown, stattab, and statid. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
The stattab parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown parameter is specified). You can create multiple tables with different stattab identifiers to hold separate sets of statistics.
Additionally, you can maintain different sets of statistics within a single stattab by using the statid parameter, which avoids cluttering the user's schema.
For the SET and GET procedures, if stattab is not provided (that is, NULL), then the operation works directly on the dictionary statistics; therefore, you do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if stattab is not NULL, then the SET or GET operation works on the specified user statistics table, and not the dictionary.
You can change the default values of some of the parameters of DBMS_STATS procedures using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
Most of the procedures in this package commit the current transaction, perform the operation, and then commit again.
Most of the procedures have a parameter, force which allows you to override any lock on statistics.Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring.
Gathering Optimizer Statistics
Use the following subprograms to gather certain classes of optimizer statistics, with possible performance improvements over the ANALYZE command:
The GATHER_* procedures also collect user-defined statistics for columns and domain indexes.
The statown, stattab, and statid parameters instruct the package to back up current statistics in the specified table before gathering new statistics.
Oracle also provides the following procedure for generating statistics for derived objects when you have sufficient statistics on related objects:
Setting or Getting Statistics
Use the following subprograms to store and retrieve individual column-related, index-related, and table-related statistics:
In the special versions of the SET_*_STATS procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or external statistics table:
User-defined statistics (extstats)
The statistics type schema name (statsschema)
The statistics type name (statsname)
The user-defined statistics and the corresponding statistics type are inserted into the USTATS$ dictionary table. You can specify user-defined statistics without specifying the statistics type name.
The special versions of the GET_*_STATS procedures return user-defined statistics and the statistics type owner and name as OUT arguments corresponding to the schema object specified. If user-defined statistics are not collected, NULL values are returned.
Deleting Statistics
The DELETE_* procedures delete both user-defined statistics and the standard statistics for the given schema object.
Transferring Statistics
Use the following procedures for creating and dropping the user statistics table.
Use the following procedures to transfer statistics
from the dictionary to a user statistics table (EXPORT_*)
from a user statistics table to the dictionary (IMPORT_*)
Note:
Oracle does not support export or import of statistics across databases of different character sets.Locking or Unlocking Statistics
Use the following procedures to lock and unlock statistics on objects.
The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
Restoring and Purging Statistics History
Use the following procedures to restore statistics as of a specified timestamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION Procedure.
The other DBMS_STATS procedures related to restoring statistics are:
PURGE_STATS Procedure: This procedure lets you manually purge old versions beyond a time stamp.
GET_STATS_HISTORY_RETENTION Function: This function gets the current statistics history retention value.
GET_STATS_HISTORY_AVAILABILITY Function: This function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.
RESTORE_* operations are not supported for user defined statistics.
User-Defined Statistics
The DBMS_STATS package supports operations on user-defined statistics. When a domain index or column is associated with a statistics type (using the associate statement), operations on the index or column manipulate user-defined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the GET_INDEX_STATS Procedures invokes the user-defined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate user-defined statistics.
SET_* and GET_* operations for user-defined statistics are also supported using a special version of the SET and GET interfaces for columns and indexes.
EXPORT_*, IMPORT_* and RESTORE_* operations are not supported for user defined statistics.
Pending Statistics
The package gather statistics and stores it in the dictionary by default. User's can store these statistics in the system's private area instead of the dictionary by turning the PUBLISH option to FALSE using the SET*PREFS procedures. The default value for PUBLISH is TRUE.The statistics stored in private area are not used by Cost Based Optimizer unless parameter optimizer_use_private_statistics is set to TRUE. The default value of this parameter is FALSE and this boolean parameter can be set at the session/system level. Users can verify the impact of the new statistics on query plans by using the pending statistics on a session.
Pending statistics provide a mechanism to verify the impact of the new statistics on query plans before making them available for general use. There are two scenarios to verify the query plans:
Export the pending statistics (use the EXPORT_PENDING_STATS Procedure) to a test system, then run the query workload and check the performance or plans.
Set optimizer_use_pending_statistics to TRUE in a session on the system where pending statistics have been gathered, run the workload, and check the performance or plans.
Once the performance or query plans have been verified, the pending statistics can be published (run the PUBLISH_PENDING_STATS Procedure) if the performance is acceptable or delete (run the DELETE_PENDING_STATS Procedure) if not.
Pending statistics can be published, exported, or deleted. The following procedures are provided to manage pending statistics:
Comparing 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.
Extended Statistics
This package allows you to collect statistics for column groups and expressions (known as "statistics extensions"). The statistics collected for column groups and expressions are called "extended statistics".
Statistics on Column groups are used by optimizer for accounting correlation between columns. For example, if a query has predicates c1=1 and c2=1 and if there are statistics on (c1, c2), the optimizer will use this statistics for estimating the combined selectivity of the predicates.The expression statistics are used by optimizer for estimating selectivity of predicates on those expressions. The extended statistics are similar to column statistics and the procedures that take columns names will accept extension names in place of column names.
Related subprograms:
The following subprograms are obsolete with Release 11g:
Instead, use GET_PREFS Function
Instead, use SET_GLOBAL_PREFS Procedure
RESET_PARAM_DEFAULTS Procedure
Instead use RESET_GLOBAL_PREF_DEFAULTS Procedure
Using Pending Statistics
Assume many modifications have been made to the employees table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:
EXEC DBMS_STATS.SET_TABLE_PREFS('hr', 'employees', 'PUBLISH', 'false');
By setting the employees tables publish preference to FALSE, any statistics gather from now on will not be automatically published. The newly gathered statistics will be marked as pending.
EXEC DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees');
To test the newly gathered statistics, set optimizer_pending_statistics to TRUE in a session and run sample queries.
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
If the pending statistics generate sound execution plans, they can be published:
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('hr', 'employees');
EXEC DBMS_STATS.SET_TABLE_PREF('hr', 'employees', 'PUBLISH', 'true');
Gathering Daytime System Statistics
Assume that you want to perform database application processing OLTP transactions during the day and run reports at night.
To collect daytime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS table.
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS (
interval => 720,
stattab => 'mystats',
statid => 'OLTP');
END;
To collect nighttime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS table.
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS (
interval => 720,
stattab => 'mystats',
statid => 'OLAP');
END;
Update the dictionary with the gathered statistics.
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS
(''mystats'',''OLTP'');'
sysdate, 'sysdate + 1');
COMMIT;
END;
BEGIN
DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS
(''mystats'',''OLAP'');'
sysdate + 0.5, 'sysdate + 1');
COMMIT;
END;
Table 127-2 DBMS_STATS Package Subprograms
| Subprogram | Description |
|---|---|
|
Changes the statistics history retention value |
|
|
Convert the internal representation of a minimum or maximum value into a datatype-specific value |
|
|
Convert the internal representation of a minimum or maximum value into a datatype-specific value |
|
|
Convert the internal representation of a minimum or maximum value into a datatype-specific value |
|
|
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, excluding the tables owned by Oracle. |
|
|
Deletes statistics for the entire database |
|
|
Deletes statistics for all dictionary schemas (' |
|
|
Deletes statistics of all fixed tables |
|
|
Deletes index-related statistics |
|
|
Deletes the private statistics that have been collected but have not been published |
|
|
Deletes the statistics preferences of all the tables owned by the specified owner name |
|
|
Deletes 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, excluding the tables owned by Oracle. |
|
|
Retrieves statistics for all objects in the database and stores them in the user statistics table identified by |
|
|
Retrieves statistics for all dictionary schemas (' |
|
|
Retrieves statistics for fixed tables and stores them in the user statistics table identified by |
|
|
Retrieves statistics for a particular index and stores them in the user statistics table identified by |
|
|
Exports the statistics gathered and stored as pending |
|
|
Exports the statistics preferences of all the tables owned by the specified owner name |
|
|
Retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics table identified by |
|
|
Retrieves system statistics and stores them in the user statistics table |
|
|
Exports statistics preferences of the specified table in the specified schema into the specified statistics table |
|
|
Retrieves statistics for a particular table and stores them in the user statistics table |
|
|
Flushes in-memory monitoring information for all the tables to the dictionary |
|
|
Gathers statistics for all objects in the database |
|
|
Gathers statistics for dictionary schemas ' |
|
|
Gathers statistics of fixed objects |
|
|
Gathers index statistics |
|
|
Gathers statistics for all objects in a schema |
|
|
Gathers system statistics |
|
|
Gathers table and column (and index) statistics |
|
|
Generates object statistics from previously collected statistics of related objects |
|
|
Gets all column-related information |
|
|
Gets all index-related information |
|
|
Gets the default value of parameters of |
|
|
Gets the oldest timestamp where statistics history is available |
|
|
Returns the current retention value |
|
|
Gets system statistics from stattab, or from the dictionary if stattab is |
|
|
Gets all table-related information |
|
|
Retrieves statistics for a particular column from the user statistics table identified by |
|
|
Imports the statistics preferences of all the tables, excluding the tables owned by Oracle. |
|
|
Retrieves statistics for all objects in the database from the user statistics table and stores them in the dictionary |
|
|
Retrieves statistics for all dictionary schemas (' |
|
|
Retrieves statistics for fixed tables from the user statistics table identified by |
|
|
Retrieves statistics for a particular index from the user statistics table identified by |
|
|
Imports the statistics preferences of all the tables owned by the specified owner name |
|
|
Retrieves statistics for all objects in the schema identified by |
|
|
Retrieves system statistics from the user statistics table and stores them in the dictionary |
|
|
Sets the statistics preferences of the specified table in the specified schema. |
|
|
Retrieves statistics for a particular table from the user statistics table identified by |
|
|
Locks statistics for a partition |
|
|
Locks the statistics of all tables of a schema |
|
|
Locks the statistics on the table |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures |
|
|
Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SET_COLUMN_STATS Procedures |
|
|
Publishes the statistics gathered and stored as pending |
|
|
Purges old versions of statistics saved in the dictionary |
|
|
Resets the default values of all parameters to Oracle recommended values |
|
|
Resets global preferences to default values [see Deprecated Subprograms ] |
|
|
Restores statistics of all dictionary tables (tables of ' |
|
|
Restores statistics of all fixed tables as of a specified timestamp |
|
|
Restores statistics of all tables of a schema as of a specified timestamp |
|
|
Restores statistics of all tables of a schema as of a specified timestamp |
|
|
Restores statistics of a table as of a specified timestamp (as_of_timestamp), as well as statistics of associated indexes and columns |
|
|
Sets column-related information |
|
|
Sets the statistics preferences of all the tables, excluding the tables owned by Oracle |
|
|
Sets the global statistics preferences |
|
|
Sets index-related information |
|
|
Sets default values for parameters of |
|
|
Sets the statistics preferences of all the tables owned by the specified owner name |
|
|
Sets system statistics |
|
|
Sets the statistics preferences of the specified table in the specified schema |
|
|
Sets table-related information |
|
|
Returns the name of the virtual column that is created for the user-specified extension |
|
|
Unlocks the statistics for a partition |
|
|
Unlocks the statistics on all the table in a schema |
|
|
Unlocks the statistics on the table |
|
|
Upgrades user statistics on an older table |
This procedure changes the statistics history retention value. Statistics history retention is used by both the automatic purge and PURGE_STATS Procedure.
Syntax
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( retention IN NUMBER);
Parameters
Table 127-3 ALTER_STATS_HISTORY_RETENTION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The retention time in days. The statistics history will be retained for at least these many number of days.The valid range is [1,365000]. Also you can use the following values for special purposes:
|
Usage Notes
To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
Exceptions
ORA-20000: Insufficient privileges.
This procedure converts the internal representation of a minimum or maximum value into a datatype-specific value. The minval and maxval fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.
Syntax
DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_FLOAT); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_DOUBLE); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT DATE); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT NUMBER); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT VARCHAR2);
Pragmas
pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 127-4 CONVERT_RAW_VALUE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The raw representation of a column minimum or maximum datatype-specific output parameters |
|
|
The converted, type-specific value |
This procedure converts the internal representation of a minimum or maximum value into a datatype-specific value. The minval and maxval fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.
Syntax
DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR ( rawval RAW, resval OUT NVARCHAR2);
Pragmas
pragma restrict_references(convert_raw_value_nvarchar, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 127-5 CONVERT_RAW_VALUE_NVARCHAR Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The raw representation of a column minimum or maximum datatype-specific output parameters |
|
|
The converted, type-specific value |
This procedure converts the internal representation of a minimum or maximum value into a datatype-specific value. The minval and maxval fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.
Syntax
DBMS_STATS.CONVERT_RAW_VALUE_ROWID ( rawval RAW, resval OUT ROWID);
Pragmas
pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);
Parameters
Table 127-6 CONVERT_RAW_VALUE_ROWID Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The raw representation of a column minimum or maximum datatype-specific output parameters |
|
|
The converted, type-specific value |
This procedure copies the statistics of the source [sub] partition to the destination [sub] partition after scaling (the number of blks, number of rows). It sets the high bound partitioning value as the maximum value of the first partitioning column and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for a range partitioned table. It finds the maximum and minimum from the list of values for the list partitioned table. It also sets the normalized maximum and minimum values. If the destination partition is the first partition then minimum values are equal to maximum values. If the statistics for source are not available then nothing is copied.
Syntax
DBMS_STATS.COPY_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, srcpartname VARCHAR2, dstpartname VARCHAR2, scale_factor VARCHAR2 DEFAULT 1, force BOOLEAN DEFAULT FALSE);
Parameters
Table 127-7 COPY_TABLE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of index to analyze |
|
|
Table name of source and destination [sub] partitions |
|
|
Source [sub] partition |
|
|
Destination [sub] partition |
|
|
Scale factor to scale nblks, nrows etc. in |
|
|
When value of this argument is |
Exceptions
ORA-20000: Invalid partition name
ORA-20001: Bad input value
This function creates a column statistics entry in the system for a user specified column group or an expression in a table. Statistics for this extension will be gathered when user or auto statistics gathering job gathers statistics for the table. We call statistics for such an extension, "extended statistics". This function returns the name of this newly created entry for the extension.
Syntax
DBMS_STATS.CREATE_EXTENDED_STATS ( ownname VARCHAR2, tabname VARCHAR2, extension VARCHAR2) RETURN VARCHAR2;
Parameters
Table 127-8 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 |
Return Values
This function returns the name of this newly created entry for the extension.
Exceptions
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
Usage Notes
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.
Syntax
DBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);
Parameters
Table 127-9 CREATE_STAT_TABLE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the table to create. This value should be passed as the |
|
|
Tablespace in which to create the statistics tables. If none is specified, then they are created in the user's default tablespace. |
Exceptions
ORA-20000: Table already exists or insufficient privileges.
ORA-20001: Tablespace does not exist.
This procedure deletes column-related statistics.
Syntax
DBMS_STATS.DELETE_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
col_stat_type VARCHAR2 DEFAULT 'ALL');
Parameters
Table 127-10 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:
|
Exceptions
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.
Syntax
DBMS_STATS.DELETE_DATABASE_PREFS (
pname IN VARCHAR2,
add_sys IN BOOLEAN DEFAULT FALSE);
Parameters
Table 127-11 DELETE_DATABASE_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Preference name. The default value for following parameters can be deleted:
|
|
. |
|
|
. |
|
|
. |
|
|
. |
- integer : Number of histogram buckets. Must be in the range [1,254].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.- column_name : name of a column- extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is |
|
. |
|
|
. |
'
|
|
. |
|
|
. |
If the |
|
. |
|
|
|
Value |
Exceptions
ORA-20000: Insufficient privileges
ORA-20001: Invalid or Illegal input values
Usage Notes
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.
Examples
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.
Syntax
DBMS_STATS.DELETE_DATABASE_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 127-12 DELETE_DATABASE_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When the value of this argument is |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
This procedure deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas).
Syntax
DBMS_STATS.DELETE_DICTIONARY_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 127-13 DELETE_DICTIONARY_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When the value of this argument is |
Usage Notes
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure deletes statistics of all fixed tables.
Syntax
DBMS_STATS.DELETE_FIXED_OBJECTS_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 127-14 DELETE_FIXED_OBJECTS_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The user statistics table identifier describing from where to delete the current statistics. If |
|
|
The (optional) identifier to associate with these statistics within stattab. This only applies if |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
Ignores the statistics lock on objects and deletes the statistics if set to |
Usage Notes
You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
Exceptions
ORA-20000: Insufficient privileges.
ORA-20002: Bad user statistics table, may need to upgrade it.
This procedure deletes index-related statistics.
Syntax
DBMS_STATS.DELETE_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 127-15 DELETE_INDEX_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
Name of the index |
|
|
Name of the index partition for which to delete the statistics. If the index is partitioned and if |
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
If the index is partitioned and if |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When value of this argument is |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20005: Object statistics are locked.
This procedure is used to delete the pending statistics that have been collected but have not been published.
Syntax
DBMS_STATS.DELETE_PENDING_STATS (
ownname IN VARCHAR2 DEFAULT USER,
tabname IN VARCHAR2);
Parameters
Table 127-16 DELETE_PENDING_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Table name |
Exceptions
ORA-20000: Insufficient privileges
Usage Notes
If the parameter tabname is NULL then delete applies to all tables of the specified schema.
The default owner/schema is the user who runs the procedure.
To run this procedure, you need to have the same privilege for gathering statistics on the tables that will be touched by this procedure.
Examples
DBMS_STATS.DELETE_PENDING_STATS('SH', 'SALES');
This procedure is used to delete the statistics preferences of all the tables owned by the specified owner name.
Syntax
DBMS_STATS.DELETE_SCHEMA_PREFS (
ownname IN VARCHAR2,
pname IN VARCHAR2);
Parameters
Table 127-17 DELETE_SCHEMA_PREFS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner name |
|
|
Preference name. The default value for following parameters can be deleted:
|
|
. |
|
|
. |
|
|
. |
|
|
. |
- integer : Number of histogram buckets. Must be in the range [1,254].- REPEAT : Collects histograms only on the columns that already have histograms.- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.- column_name : name of a column- extension: can be either a column group in the format of (column_name, colume_name [, ...])or an expression
The default is |
|
. |
|
|
. |
'
|
|
. |
|
|
. |
If the |
|
. |
|
Exceptions
ORA-20000: Insufficient privileges / Schema "<schema>" does not exist
ORA-20001: Invalid or Illegal input values
Usage Notes
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.
Examples
DBMS_STATS.DELETE_SCHEMA_PREFS('SH', 'CASCADE');
DBMS_STATS.DELETE_SCHEMA_PREFS('SH', 'ESTIMATE_PERCENT');
DBMS_STATS.DELETE_SCHEMA_PREFS('SH', 'DEGREE');
This procedure deletes statistics for an entire schema.
Syntax
DBMS_STATS.DELETE_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Parameters
Table 127-18 DELETE_SCHEMA_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the schema |
|
|
User statistics table identifier describing from where to delete the statistics. If |
|
|
Identifier (optional) to associate with these statistics within |
|
|
Schema containing |
|
|
Does not invalidate the dependent cursors if set to |
|
|
When value of this argument is |
Exceptions
ORA-20000: Object does not exist or insufficient privileges
This procedure deletes workload statistics (collected using the 'INTERVAL' or 'START' and 'STOP' options) and resets the default to noworkload statistics (collected using 'NOWORKLOAD' option) if stattab is not specified. If stattab is specified, the subprogram deletes all system statistics with the associated statid from the stattab.
Syntax
DBMS_STATS.DELETE_SYSTEM_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 127-19 DELETE_SYSTEM_STATS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Identifier of the user statistics table where the statistics will be saved |
|
|
Optional identifier associated with the statistics saved in the stattab |
|
|
Schema containing |
Exceptions
ORA-20000: Object does not exist or insufficient privileges.
ORA-20002: Bad user statistics table; may need to be upgraded.
This procedure is used to delete the statistics preferences of the specified table in the specified schema.
Syntax
DBMS_STATS.DELETE_TABLE_PREFS (
ownname IN VARCHAR2, tabname IN VARCHAR2, pname IN VARCHAR2);
Parameters