192 DBMS_STATS
With the DBMS_STATS
package you can view and modify optimizer statistics gathered for database objects.
Users can also collect statistics on Global Temporary Tables (GTTs) using the DBMS_STATS package. However, DBMS_STATS
cannot collect statistics on Private Temporary Tables (PTTs).
This chapter contains the following topics:
192.1 DBMS_STATS Overview
To improve performance, the database enables you to collect optimizer statistics.
Note:
By default, the database collects statistics automatically, so this package is intended only for specialized cases.
Optimizer statistics can reside in the data dictionary or in a table created in the user's schema. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if you invoke the DELETE_COLUMN_STATS
procedure on a column for which an association is defined, the database deletes both user-defined and standard statistics for this column.
Only optimizer statistics stored in the data dictionary have an effect on the cost-based optimizer. You can also use DBMS_STATS
to gather statistics in parallel.
Optimizer Statistics Advisor inspects the statistics gathering process, automatically diagnoses problems in the existing practices for gathering statistics, and then generates a report of findings and recommendations. The advisor task runs automatically in the maintenance window. However, you can also run the job on demand.
See Also:
Oracle Database SQL Tuning Guide to learn how to manage optimizer statistics
192.2 DBMS_STATS Deprecated Subprograms
Oracle recommends that you do not use deprecated subprograms. Support for deprecated features is for backward compatibility only.
Starting with Oracle Database 11g Release 2 (11.2), the following subprograms are obsolete:
-
Instead, use GET_PREFS Function
-
Instead, use SET_GLOBAL_PREFS Procedure
-
RESET_PARAM_DEFAULTS Procedure
Instead use RESET_GLOBAL_PLSQL_PREFS_DEF Procedure
The following subprogram is deprecated with Oracle Database 12c and later:
-
GENERATE_STATS
This procedure is replaced by the
GATHER_INDEX_STAT
procedure.See Also:
192.3 DBMS_STATS Types
Histograms
Types for the minimum and maximum values and histogram endpoints include the following:
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;
Stale Tables
Types for listing stale tables include the following:
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;
Statistics Difference Reports
Use the following type to displays a statistics difference report:
TYPE DiffRepElem IS RECORD ( report CLOB, -- stats difference report maxdiffpct NUMBER); -- max stats difference (percentage) TYPE DiffRepTab IS TABLE OF DiffRepElem;
Optimizer Statistics Advisor
The following type represents database objects for which you can gather statistics:
TYPE ObjectElem IS RECORD (
ownname dbms_quoted_id, -- owner
objtype VARCHAR2(6), -- 'TABLE' or 'INDEX'
objname dbms_quoted_id, -- table/index
partname dbms_quoted_id, -- partition
subpartname dbms_quoted_id -- subpartition
);
TYPE ObjectTab IS TABLE OF ObjectElem;
Note:
Make sure to maintain satisfy_obj_filter
when the ObjectElem
type is changed
The following type represents an operation:
TYPE StatsAdvOpr IS RECORD (
name VARCHAR2(64), -- name of the operation
param VARCHAR2(4000)
); -- XML containing parameters and their values
TYPE StatsAdvOprTab IS TABLE OF StatsAdvOpr;
The following type represents a filter list:
TYPE StatsAdvFilter IS RECORD (
rulename VARCHAR2(64), -- rule name
objlist ObjectTab, -- object filter list
oprlist StatsAdvOprTab, -- operation filter list
include BOOLEAN); -- include/exclude elements in the list
TYPE StatsAdvFilterTab IS TABLE OF StatsAdvFilter;
192.4 DBMS_STATS Constants
The DBMS_STATS
package defines several constants to use specifying parameter values.
Table 192-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. |
192.5 DBMS_STATS Operational Notes
Observe these operational notes when using the DBMS_STATS
package.
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 enable 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 procedures in this package commit the current transaction, perform the operation, and then commit again.
Most of the procedures have a force
parameter that enables you to override a 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:
- GATHER_DATABASE_STATS Procedures
- GATHER_DICTIONARY_STATS Procedure
- GATHER_FIXED_OBJECTS_STATS Procedure
- GATHER_INDEX_STATS Procedure
- GATHER_SCHEMA_STATS Procedures
- GATHER_SYSTEM_STATS Procedure
- GATHER_TABLE_STATS Procedure
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:
- PREPARE_COLUMN_VALUES Procedures
- PREPARE_COLUMN_VALUES_NVARCHAR Procedure
- PREPARE_COLUMN_VALUES_ROWID Procedure
- SEED_COL_USAGE Procedure
- SET_INDEX_STATS Procedures
- SET_SYSTEM_STATS Procedure
- SET_TABLE_STATS Procedure
- GET_COLUMN_STATS Procedures
- GET_INDEX_STATS Procedures
- GET_SYSTEM_STATS Procedure
- GET_TABLE_STATS Procedure
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.
Deleting Statistics
The DELETE_*
procedures delete both user-defined statistics and the standard statistics for the given schema object.
- DELETE_COLUMN_STATS Procedure
- DELETE_DATABASE_STATS Procedure
- DELETE_DICTIONARY_STATS Procedure
- DELETE_FIXED_OBJECTS_STATS Procedure
- DELETE_INDEX_STATS Procedure
- DELETE_SCHEMA_STATS Procedure
- DELETE_SYSTEM_STATS Procedure
- DELETE_TABLE_STATS Procedure
- Note that
DELETE_TABLE_STATS
,DELETE_DICTIONARY_STATS
,DELETE_DATABASE_STATS
andDELETE_SCHEMA_STATS
have a parameterstat_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'
.
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 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.
- RESET_GLOBAL_PLSQL_PREFS_DEF Procedure
- RESTORE_DATABASE_STATS Procedure
- RESTORE_DICTIONARY_STATS Procedure
- RESTORE_FIXED_OBJECTS_STATS Procedure
- RESTORE_SCHEMA_STATS Procedure
- RESTORE_SYSTEM_STATS Procedure
- RESTORE_TABLE_STATS Procedure
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 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
toTRUE
in a session on the system where pending statistics have been gathered, run the workload, and check the performance or plans.
After the performance or query plans have been verified, you can publish the pending statistics using the PUBLISH_PENDING_STATS Procedure if the performance is acceptable, or delete the pending statistics using DELETE_PENDING_STATS Procedure if it is not.
Pending statistics can be published, exported, or deleted. The following procedures are provided to manage pending statistics:
Comparing Statistics
You can use the DIFF_TABLE_STATS_
* statistics 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 display statistics of the objects 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 database uses the statistics corresponding to the first source (stattab1
or time1
) as a basis for computing the differential percentage.
Extended Statistics
This package enables 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 uses these statistics for estimating the combined cardinality of the predicates. The optimizer uses the expression statistics to estimate cardinality of predicates on those expressions. The extended statistics are similar to column statistics. The procedures that take columns names accept extended statistics names in place of column names.
Related subprograms:
Optimizer Statistics Advisor
Optimizer Statistics Advisor is built-in diagnostic software that helps use to use best practices to manage optimizer statistics. The advisor analyzes how you are currently gathering statistics (using manual scripts, explicitly setting parameters, and so on), the effectiveness of existing statistics gathering jobs, and the quality of the gathered statistics. The advisor generates findings for any issues it finds. Based on these findings, the advisor provides recommendations, which it stores in DBA_ADVISOR_RECOMMENDATIONS
.
The advisor organizes rules into the following classes:
-
System
This class checks the preferences for statistics collection, status of the automated statistics gathering job, use of SQL plan directives, and so on. Rules in this class have the value
SYSTEM
inV$STATS_ADVISOR_RULES.RULE_TYPE
. -
Operation
This class checks whether statistics collection uses the defaults, test statistics are created using the
SET_*_STATS
procedures, and so on. Rules in this class have the valueOPERATION
inV$STATS_ADVISOR_RULES.RULE_TYPE
. -
Object
This class checks for the quality of the statistics, staleness of statistics, unnecessary collection of statistics, and so on. Rules in this class have the value
OBJECT
inV$STATS_ADVISOR_RULES.RULE_TYPE
.
All Optimizer Statistics Advisor subprograms require the ADVISOR
privilege. All procedures and functions execute using the invoker's privilege for the operation instead of the task owner's privilege. For example, if a user without the ANALYZE ANY DICTIONARY
privilege creates a task t1
, and if a DBA then executes this task, then the task execution checks for SYS
objects. Another example is a task that is executed by user1
, interrupted, and then resumed by user2
. In this case, the checks of the resumed execution are based on the privilege of user2
rather than user1
.
You can use the following subprograms to manage Optimizer Statistics Advisor:
See Also:
Oracle Database SQL Tuning Guideto learn how to analyze statistics using Optimizer Statistics Advisor
192.6 DBMS_STATS Data Structures
The DBMS_STATS
package defines a RECORD
type.
RECORD Types
192.6.1 DBMS_STATS STAT_REC 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.
Syntax
TYPE STATREC IS RECORD ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY, chvals CHARARRAY, eavals RAWARRAY, rpcnts NUMARRAY, eavs NUMBER);
Fields of the Record type COMPARISON_TYPE (STAT_REC Attributes)
Table 192-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. |
192.7 Summary of DBMS_STATS Subprograms
This table lists the DBMS_STATS
subprograms and briefly describes them.
Table 192-3 DBMS_STATS Package Subprograms
Subprogram | Description |
---|---|
Changes the statistics history retention value |
|
Cancels an Optimizer Statistics Advisor execution |
|
Configures the filter list for an Optimizer Statistics Advisor task |
|
Configures an object filter for an Optimizer Statistics Advisor task |
|
Configures an operation filter for an Optimizer Statistics Advisor task |
|
Configures a rule filter for an Optimizer Statistics Advisor task |
|
Creates an advisor task for the Optimizer Statistics Advisor |
|
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 |
|
DELETE_PLSQL_PREFS Procedure | Deletes the preferences for a given function. The values then default to the global preferences. |
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 specified Optimizer Statistics Advisor task |
|
Drops the statistics entry that is created for the user specified extension |
|
Drops a user statistics table created by |
|
Executes a previously created Optimizer Statistics Advisor task |
|
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 |
|
Creates an operation filter for an Optimizer Statistics Advisor operation |
|
Generates a recommendation report for the specified item |
|
Gets all column-related information |
|
Gets all index-related information |
|
Gets the default value of parameters of |
|
GET_PLSQL_PREFS Procedure | Get the preferences for the given function. |
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 |
|
Implements the recommendations made by Optimizer Statistics Advisor |
|
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 |
|
Interrupts a currently executing Optimizer Statistics Advisor task. |
|
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 results of an Optimizer Advisor Task. |
|
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 of all statistics operations that take place between two timestamps which may or may not have been provided |
|
Resets an Optimizer Statistics Advisor task execution to its initial state. Only reset a task that is not currently executing |
|
Resets the recorded column (group) usage information |
|
RESET_GLOBAL_PLSQL_PREFS_DEF Procedure | Reset all global PLSQL preferences for dynamic sampling to the defaults. |
Resets global preferences to default values (see DBMS_STATS 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 ( |
|
Resumes an interrupted task. It only resumes the execution that was most recently interrupted |
|
Retrieves the script that implements the recommended actions for the problems found by Optimizer Statistics Advisor |
|
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 |
|
Updates the value of an Optimizer Statistics Advisor task parameter |
|
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 |
|
SET_PLSQL_PREFS Procedure | Sets preferences for a specific function – ownername, package_name, function_name, value. |
SET_GLOBAL_PLSQL_PREFS Procedure | Sets the global preference for performing dynamic sampling for PL/SQL
functions - pname (dynamic_stats by default), pvalue
(ON , OFF , CHOOSE ,
DEFAULT ).
|
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 |
192.7.1 ALTER_STATS_HISTORY_RETENTION Procedure
This procedure changes the statistics history retention value.
Statistics history retention is used by both the automatic purge and PURGE_STATS Procedure.
Syntax
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( retention IN NUMBER);
Parameters
Table 192-4 ALTER_STATS_HISTORY_RETENTION Procedure Parameters
Parameter | Description |
---|---|
|
The retention time in days. The statistics history will be retained for at least these many number of days.The valid range is [1,365000]. Also you can use the following values for special purposes:
|
Usage Notes
To run this procedure, you must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege.
Exceptions
ORA-20000
: Insufficient privileges
192.7.2 CANCEL_ADVISOR_TASK Procedure
This procedure cancels an Optimizer Statistics Advisor execution. The advisor removes all intermediate results of the current execution from the task.
Syntax
DBMS_STATS.CANCEL_ADVISOR_TASK (
task_name IN VARCHAR2);
Parameters
Table 192-5 CANCEL_ADVISOR_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the Optimizer Statistics Advisor task. |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Consider a case in which a task is executed by one user, interrupted, and then resumed by a different user. In this case, Optimizer Statistics Advisor bases its checks of the resumed execution on the privilege of the user who resumed the task.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Usage Notes
To be canceled or interrupted, the specified task must be currently executing.
Example 192-1 Canceling an Optimizer Statistics Advisor
In this example, you start a SQL*Plus session, and then create and execute an advisor task named my_task
:
DECLARE
v_tname VARCHAR2(128) := 'my_task';
v_ename VARCHAR2(128) := NULL;
BEGIN
-- create a task
v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
-- execute the task
v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/
In a separate terminal, you start a second SQL*Plus session, and then execute the following program:
EXEC DBMS_STATS.CANCEL_ADVISOR_TASK('my_task');
The first session returns an ORA-13632
to indicate the cancelation of the task:
ORA-13632: The user cancelled the current operation.
Note:
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.3 CONFIGURE_ADVISOR_FILTER Function
This function configures the filter list for an Optimizer Statistics Advisor task. Filters are useful for excluding irrelevant findings from a report.
Syntax
DBMS_STATS.CONFIGURE_ADVISOR_FILTER ( task_name IN VARCHAR2, stats_adv_opr_type IN VARCHAR2, configuration_type IN VARCHAR2, filter IN StatsAdvFilterTab := NULL) RETURN CLOB;
Parameters
Table 192-6 CONFIGURE_ADVISOR_FILTER Function Parameters
Parameter | Description |
---|---|
|
The name of the Optimizer Statistics Advisor task. |
|
The type of operation to configure. Possible values are |
|
The type of configuration. Possible values are as follows:
|
|
The list of filter items for the script. |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Return Values
This function returns a CLOB that contains the configuration of the provided filter in XML format.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Usage Notes
To provide fine-grained control and a unified interface across all procedures, DBMS_STATS
provides the StatsAdvFilter
type. You can use this data type to instantiate and construct a table of filters. You can then pass a parameter of type StatsAdvFilter
to CONFIGURE_ADVISOR_FILTER
along with a Boolean variable that specifies either of the following:
-
Inclusion list
Only include these objects in the check.
-
Exclusion list
Do not include these objects in the check.
You can also pass in a parameter specifying whether to replace the existing list. This list only filters object-level and operation-level items. The advisor always checks system-level rules.
You can create the following types of filters:
-
Rule filter
This filter takes a rule name as input. Obtain rule names from the
V$STATS_ADVISOR_RULES
view. -
Operation filter
This filter is an exact match filter that takes in the name of the operation and an XML string representation of all the parameter values in the call. To obtain the XML, see the notes section of the
DBA_OPTSTAT_OPERATIONS
view. To obtain the filter for an operation, useDBMS_STATS.GET_ADVISOR_OPR_FILTER
. -
Object filter
This filter accept an owner name and an object name. Wildcards (
%
) are supported in the owner name and object name. When an object name is null or%
, it means a filter for all the objects in the specified schema. If the owner name is also null or%
, it means a default filter for all objects in the system.
If none of the filters is specified, then the function recognizes the filter as setting the global default value of filtering (include or exclude). During the check, if no filter has been specified for a rule, operation, or object, then the function uses the default value to determine whether to include or exclude it.
Example 192-2 Enabling and Disabling Rules
You may want to turn off checks for all rules except for a specific rule. In this example, you want to check whether SQL plan directives have been disabled.
DECLARE
v_task_name VARCHAR2(128) := 'my_task';
v_ret VARCHAR2(128);
filter DBMS_STATS.StatsAdvFilter := null;
filterTab DBMS_STATS.StatsAdvFilterTab := null;
v_counter NUMBER := 0;
v_filterReport CLOB;
BEGIN
-- Create the advisor task
v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_task_name);
-- Initialize the filter table
filterTab := DBMS_STATS.StatsAdvFilterTab();
-- First filter: set filters to be FALSE by default
filter.include := FALSE;
-- Add this filter to the filter table
v_counter := v_counter + 1;
filterTab.extend;
filterTab(v_counter) := filter;
-- Second filter: turn on filter for one rule
filter.include := TRUE;
filter.rulename := 'TurnOnSQLPlanDirective';
-- Add the SQL plan directive filter to the filter table
v_counter := v_counter + 1;
filterTab.extend;
filterTab(v_counter) := filter;
v_filterReport := DBMS_STATS.CONFIGURE_ADVISOR_FILTER(
task_name => v_task_name,
stats_adv_opr_type => NULL,
configuration_type => 'SET',
filter => filterTab);
-- Drop the task
DBMS_STATS.DROP_ADVISOR_TASK(v_task_name);
END;
Example 192-3 Configuring an Operations Filter
In this example, your shop uses customized scripts to gather statistics for a table. If you do not want to see a specific statistics operation in the report, then you can specify an operations filter.
DECLARE
v_task_name VARCHAR2(128) := 'my_task';
v_ret VARCHAR2(128);
filter DBMS_STATS.StatsAdvFilter := null;
filterTab DBMS_STATS.StatsAdvFilterTab := null;
opr DBMS_STATS.StatsAdvOpr;
oprTab DBMS_STATS.StatsAdvOprTab;
v_oprCnt NUMBER := 0;
TYPE numTab IS TABLE OF NUMBER;
opr_tab numTab;
v_filterReport CLOB;
BEGIN
-- Create the advisor task
v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_task_name);
-- Initialize filter table
filterTab := DBMS_STATS.StatsAdvFilterTab();
-- Initialize operations filter
oprTab := DBMS_STATS.StatsAdvOprTab();
SELECT ID
BULK COLLECT INTO opr_tab
FROM WRI$_OPTSTAT_OPR
WHERE OPERATION = 'set_table_stats'
AND TARGET = 'HR.EMPLOYEES';
-- Populate the operations table
FOR i IN 1..opr_tab.count LOOP
-- Use the procedure GET_ADVISOR_OPR_FILTER to construct
-- an operation filter
DBMS_STATS.GET_ADVISOR_OPR_FILTER(opr_tab(i), opr);
v_oprCnt := v_oprCnt + 1;
oprTab.extend;
oprTab(v_oprCnt) := opr;
END LOOP;
filter.include := FALSE;
filter.oprlist := oprTab;
-- Add to filter table
filterTab.extend;
filterTab(1) := filter;
v_filterReport := DBMS_STATS.CONFIGURE_ADVISOR_FILTER(
task_name => v_task_name,
stats_adv_opr_type => NULL,
configuration_type => 'SET',
filter => filterTab);
-- Drop the task
DBMS_STATS.DROP_ADVISOR_TASK(v_task_name);
END;
Example 192-4 Reporting on a Specific Schema
In this example, you want to generate a report only for the sh
schema. Also, you want to skip the sh.products
table. You create an object filter as follows:
DECLARE
v_task_name VARCHAR2(128) := 'my_task';
v_ret VARCHAR2(128);
filter DBMS_STATS.StatsAdvFilter := null;
filterTab DBMS_STATS.StatsAdvFilterTab := null;
v_filterReport CLOB;
v_counter NUMBER := 0;
obj DBMS_STATS.ObjectElem;
objTab DBMS_STATS.ObjectTab;
v_objCnt NUMBER := 0;
BEGIN
-- Create the advisor task
v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_task_name);
-- Initialize filter table
filterTab := DBMS_STATS.StatsAdvFilterTab();
-- Set object filter to be off by default
filter.include := FALSE;
objTab := DBMS_STATS.ObjectTab();
obj.ownname := NULL;
obj.objname := NULL;
-- Add to the object table
v_objCnt := v_objCnt + 1;
objTab.extend;
objTab(v_objCnt) := obj;
filter.objlist := objTab;
-- Add the object filter to the filter table
v_counter := v_counter + 1;
filterTab.extend;
filterTab(v_counter) := filter;
-- In filter 1, turn on the check only for schema SH
filter.include := TRUE;
objTab := DBMS_STATS.ObjectTab();
v_objCnt := 0;
obj.ownname := 'SH';
obj.objname := NULL;
-- add to the object table
v_objCnt := v_objCnt + 1;
objTab.extend;
objTab(v_objCnt) := obj;
filter.objlist := objTab;
-- Add the object filter to the filter table
v_counter := v_counter + 1;
filterTab.extend;
filterTab(v_counter) := filter;
-- In filter 2, exclude the check for object sh.products
filter.include := FALSE;
objTab := dbms_stats.ObjectTab();
v_objCnt := 0;
-- Specify another object filter for sh.products
obj.ownname := 'SH';
obj.objname := 'PRODUCTS';
-- Add to the object table
v_objCnt := v_objCnt + 1;
objTab.extend;
objTab(v_objCnt) := obj;
filter.objlist := objTab;
-- Add the object filter to the filter table
v_counter := v_counter + 1;
filterTab.extend;
filterTab(v_counter) := filter;
v_filterReport :=
DBMS_STATS.CONFIGURE_ADVISOR_FILTER(
task_name => v_task_name,
stats_adv_opr_type => NULL,
configuration_type => 'SET',
filter => filterTab);
-- Drop the task
DBMS_STATS.DROP_ADVISOR_TASK(v_task_name);
END;
Note:
Oracle Database SQL Tuning Guide to learn how to configure Optimizer Statistics Advisor
192.7.4 CONFIGURE_ADVISOR_OBJ_FILTER Function
This function configures an object filter for an Optimizer Statistics Advisor task.
Syntax
DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER (
task_name IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2,
rule_name IN VARCHAR2,
ownname IN VARCHAR2,
tabname IN VARCHAR2,
action IN VARCHAR2)
RETURN CLOB;
Parameters
Table 192-7 CONFIGURE_ADVISOR_OBJ_FILTER Function Parameters
Parameter | Description |
---|---|
|
The name of the Optimizer Statistics Advisor task. |
|
The type of operation to configure. Possible values are |
|
The name of the rule to configure. If null, the function applies the filter to all operation-level rules. |
|
The owner name of the operation target. If null, the function applies the filter to all owner names. |
|
The table name of the operation target. |
|
The configuration action to take for the specified rule. See"CONFIGURE_ADVISOR_RULE_FILTER Function". |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Return Values
This function returns an XML CLOB that contains the updated values of the filter.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Note:
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.5 CONFIGURE_ADVISOR_OPR_FILTER Functions
This overloaded function configures an operation filter for an Optimizer Statistics Advisor task.
Syntax
DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER (
task_name IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2,
rule_name IN VARCHAR2,
operation_name IN VARCHAR2,
action IN VARCHAR2)
RETURN CLOB;
DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER (
task_name IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2,
rule_name IN VARCHAR2,
operation_name IN VARCHAR2,
ownname IN VARCHAR2,
tabname IN VARCHAR2,
action IN VARCHAR2)
RETURN CLOB;
DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER (
task_name IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2,
rule_name IN VARCHAR2,
operation_id IN NUMBER,
action IN VARCHAR2)
RETURN CLOB;
Parameters
Table 192-8 CONFIGURE_ADVISOR_OPR_FILTER Function Parameters
Parameter | Description |
---|---|
|
The name of the Optimizer Statistics Advisor task. |
|
The type of operation to configure. Possible values are |
|
The name of the rule to configure. If null, the function applies the filter to all operation-level rules. |
|
The name of the operation. For example, an operation name could be |
|
The ID of the operation to configure. The filter applies to any operation with the same signature as the specified operation ID. If two operations have the same signature, then they have the same value for every parameter. View the operation ID in |
|
The owner name of the operation target. This value cannot be null. |
|
The table name of the operation target. |
|
The configuration action to take for the specified rule. See"CONFIGURE_ADVISOR_RULE_FILTER Function". |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Return Values
This function returns an XML CLOB that contains the updated values of the filter.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Example 192-5 Excluding Operations for Gathering Table Statistics
In this example, your goal is to exclude operations that gather table
statistics in the hr
schema. User account stats
has been
granted the DBA
role, ADVISOR
privilege, and
SELECT ON DBA_OPTSTAT_OPERATIONS
privilege. You perform the following
steps:
-
Log in to the database as
stats
. -
Drop any existing task named
opt_adv_task1
.DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := 'opt_adv_task1'; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; /
-
Create a procedure named
opr_filter
that configures a task to advise on all operations except those that gather statistics for tables in thehr
schema.CREATE OR REPLACE PROCEDURE opr_filter(p_tname IN VARCHAR2) IS v_retc CLOB; BEGIN -- For all rules, prevent the advisor from operating -- on the operations selected in the following query FOR rec IN (SELECT ID FROM DBA_OPTSTAT_OPERATIONS WHERE OPERATION = 'gather_table_stats' AND TARGET LIKE 'HR.%') LOOP v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER( task_name => p_tname , stats_adv_opr_type => NULL , rule_name => NULL , operation_id => rec.id , action => 'DISABLE'); END LOOP; END; / SHOW ERRORS
-
Create a task named
opt_adv_task1
, and then execute theopr_filter
procedure for this task.DECLARE v_tname VARCHAR2(32767); v_ret VARCHAR2(32767); BEGIN v_tname := 'opt_adv_task1'; v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname); opr_filter(v_tname); END; /
-
Execute the task
opt_adv_task1
.DECLARE v_tname VARCHAR2(32767); v_ret VARCHAR2(32767); begin v_tname := 'opt_adv_task1'; v_ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname); END; /
-
Print the report.
SPOOL /tmp/rep.txt SET LONG 1000000 COLUMN report FORMAT A200 SET LINESIZE 250 SET PAGESIZE 1000 SELECT DBMS_STATS.REPORT_ADVISOR_TASK( task_name => 'opt_adv_task1' , execution_name => NULL , type => 'TEXT' , section => 'ALL' ) AS report FROM DUAL; SPOOL OFF
See Also:
-
Oracle Database Reference to learn more about
DBA_OPTSTAT_OPERATIONS
-
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.6 CONFIGURE_ADVISOR_RULE_FILTER Function
This function configures a rule filter for an Optimizer Statistics Advisor task.
Syntax
DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER ( task_name IN VARCHAR2, stats_adv_opr_Type IN VARCHAR2, rule_name IN VARCHAR2, action IN VARCHAR2) RETURN CLOB;
Parameters
Table 192-9 SCRIPT_ADVISOR_TASK Function Parameters
Parameter | Description |
---|---|
|
The name of the Optimizer Statistics Advisor task. |
|
The type of operation to configure. Possible values are |
|
The name of the rule to configure. If null, the function applies the filter to all rules. |
|
The configuration action to take for the specified rule. Possible values are:
|
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Return Values
This function returns an XML CLOB that contains the updated values of the filter.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Note:
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.7 CONVERT_RAW_VALUE Procedures
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.
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);
Parameters
Table 192-10 CONVERT_RAW_VALUE Procedure Parameters
Parameter | Description |
---|---|
|
Raw representation of a column minimum, maximum, histogram end point actual value |
|
Converted, type-specific value |
Usage Notes
No special privilege or role is needed to invoke this procedure.
192.7.8 CONVERT_RAW_VALUE_NVARCHAR Procedure
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.
Syntax
DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR ( rawval RAW, resval OUT NVARCHAR2);
Parameters
Table 192-11 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 |
Usage Notes
No special privilege or role is needed to invoke this procedure.
192.7.9 CONVERT_RAW_VALUE_ROWID Procedure
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.
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 192-12 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 |
Usage Notes
No special privilege or role is needed to invoke this procedure.
192.7.10 COPY_TABLE_STATS Procedure
This procedure 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
.
Syntax
DBMS_STATS.COPY_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
srcpartname VARCHAR2,
dstpartname VARCHAR2,
scale_factor VARCHAR2 DEFAULT 1,
flags NUMBER DEFAULT 0,
force BOOLEAN DEFAULT FALSE);
Parameters
Table 192-13 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. |
|
When value of this argument is |
Security Model
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.
Exceptions
ORA-20000
: Invalid [sub]partition name
ORA-20001
: Bad input value
Usage Notes
This procedure updates the minimum and maximum values of destination partition for the first partitioning column as follows:
-
If the partitioning type is
HASH
, then the minimum and maximum values of the destination partition are same as that of the source partition. -
If the partitioning type is
LIST
, then the behavior depends on the setting of the destination partition:-
If the destination partition is a
NOT DEFAULT
partition, then the following statements are true:-
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 following statements are true:-
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 following statements are true:-
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 if both are equal to the source partition's lower bound, and if 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 isD-1
and the key column to be adjusted isCn
, the maximum value forCn
is set to the upper bound ofD
(ignoring the maximum value of the source partition column) provided that the upper bounds of the previous key columnCn-1
are the same in partitionsD
andD-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, then the number of distinct values is updated as 2.
-
If the source or destination is a partition of a composite partitioned table, then this procedure does not copy statistics of the underlying subpartitions.
192.7.11 CREATE_ADVISOR_TASK Function
This function creates an advisor task for the Optimizer Statistics Advisor.
Syntax
DBMS_STATS.CREATE_ADVISOR_TASK (
task_name IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Parameters
Table 192-14 CREATE_ADVISOR_TASK Function Parameters
Parameter | Description |
---|---|
|
Name of the task. If the task name is already specified, then the function uses the specified task name. Otherwise, the function generates a new task name automatically. |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
This subprogram executes using invoker's rights.
Return Values
This function returns the unique name of the Optimizer Statistics Advisor task.
Exceptions
ORA-20000
: Insufficient privileges / creating extension is not supported
ORA-20001
: Error when processing extension
ORA-20012
: Optimizer Statistics Advisor errors
Example 192-6 Creating and Executing a Task
This example creates an Optimizer Statistics Advisor task named my_task
, and then executes it.
DECLARE
v_tname VARCHAR2(128) := 'my_task';
BEGIN
-- create a task
v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
-- execute the task
v_tname := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
Note:
Oracle Database SQL Tuning Guide to learn how manage Optimizer Statistics Advisor192.7.12 CREATE_EXTENDED_STATS Function
This function creates a column statistics entry in the system for a user-specified column group or an expression in a table.
This second form of this function creates statistics extensions based on the column group usage recorded by the SEED_COL_USAGE Procedure. This function returns a report of extensions created.
Syntax
DBMS_STATS.CREATE_EXTENDED_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
extension VARCHAR2)
RETURN VARCHAR2;
DBMS_STATS.CREATE_EXTENDED_STATS (
ownname VARCHAR2,
tabname VARCHAR2)
RETURN CLOB;
Parameters
Table 192-15 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 columns |
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
To invoke this procedure you must be owner of the table, or have the ANALYZE ANY
privilege. For objects owned by SYS
, you must be either the owner of the table, or have either the ANALYZE ANY DICTIONARY
or SYSDBA
privilege.
The extension has the following restrictions:
-
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 cannot appear more than once in a column group.
-
The extension can contain an expression only if a corresponding virtual column has been created.
-
An expression must contain at least one column.
-
An expression cannot contain a subquery.
-
The
COMPATIBLE
parameter must be 11.0.0.0.0 or greater.
192.7.13 CREATE_STAT_TABLE Procedure
This procedure creates a table with name stattab
in ownname
's schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.
Syntax
DBMS_STATS.CREATE_STAT_TABLE (
ownname VARCHAR2,
stattab VARCHAR2,
tblspace VARCHAR2 DEFAULT NULL,
global_temporary BOOLEAN DEFAULT FALSE);
Parameters
Table 192-16 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 |
Security Model
To invoke this procedure you need whichever privileges are required for creating a table in the specified schema.
Exceptions
ORA-20000
: Table already exists or insufficient privileges
ORA-20001
: Tablespace does not exist
192.7.14 DELETE_COLUMN_STATS Procedure
This procedure deletes column-related statistics.
Syntax
DBMS_STATS.DELETE_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE, col_stat_type VARCHAR2 DEFAULT 'ALL');
Parameters
Table 192-17 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 |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
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
Usage Notes
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.
192.7.15 DELETE_DATABASE_PREFS Procedure
This procedure deletes the statistics preferences set for all non-system tables. You can include system tables 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 192-18 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 |
Table 192-19 Statistics Preferences
Preference | Description |
---|---|
|
Specifies the synopsis generation algorithm. A synopsis is special type of statistic that tracks the number of distinct values (NDV) for each column in a partition. Consider a synopsis as an internal management structure that samples distinct values. You can specify the following preferences:
|
|
Controls the automatic creation of extensions when database statistics are gathered. You can set the following values:
|
|
Determines whether index statistics are collected as part of gathering table statistics. |
|
Determines degree of parallelism used for gathering statistics. |
|
Determines the percentage of rows to estimate. |
|
Controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics. |
|
Determines the granularity of statistics to collect. This value is only relevant for partitioned tables. |
|
Determines whether the global statistics of a partitioned table will be maintained without doing a full table scan. |
|
Controls which synopses to collect when |
|
Specifies when a partition
or subpartition is considered stale. This parameter takes an enumeration of values, such as
The parameter accepts the following values:
Note that the following two executions are different:
The first execution uses
single quotes to set the preference to the value |
|
Controls column statistics collection and histogram creation. When setting preference on global, schema, database or dictionary level, only |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
Determines the |
|
Determines whether to override the input value of a parameter with the preference value of that parameter for a statistics operation. Possible values are:
Specifying this preference does not change the order of precedence of table, global, and default. |
|
Determines whether the database publishes newly gathered statistics after the gathering job completes. You can gather statistics without publishing them immediately. This technique enables you to test new statistics before publishing them. |
|
Determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. |
|
Specifies which statistics to import or export, accepting
multiple values separated by a comma. Values supported:
The value The default value is ' |
|
Specifies the average number
of blocks assumed to be cached in the buffer cache when calculating the index clustering
factor. The preference applies only when gathering statistics using
|
Security Model
To run this procedure, you must have the SYSDBA
role or both ANALYZE ANY DICTIONARY
and ANALYZE ANY
system privileges.
Exceptions
ORA-20000
: Insufficient privileges
ORA-20001
: Invalid or Illegal input values
Usage Notes
All pname
arguments are of type VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
Example 192-7 Examples
DBMS_STATS.DELETE_DATABASE_PREFS('CASCADE', FALSE);
DBMS_STATS.DELETE_DATABASE_PREFS('ESTIMATE_PERCENT', TRUE);
See Also:
Oracle Database SQL Tuning Guide to learn how to manage optimizer statistics preferences
192.7.16 DELETE_DATABASE_STATS Procedure
This procedure deletes statistics for all the tables in a database.
Syntax
DBMS_STATS.DELETE_DATABASE_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE, stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Parameters
Table 192-20 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 |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
When the value of this argument is |
|
Statistics to delete. It accepts multiple values separated by comma:
The default is ' |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
To run this procedure, you need to have the SYSDBA
role or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privileges.
192.7.17 DELETE_DICTIONARY_STATS Procedure
This procedure deletes statistics for all dictionary schemas ('SYS
', 'SYSTEM
' and RDBMS component schemas).
Syntax
DBMS_STATS.DELETE_DICTIONARY_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), stattype VARCHAR2 DEFAULT 'ALL', force BOOLEAN DEFAULT FALSE, stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Parameters
Table 192-21 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 |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
Statistics type |
|
When the value of this argument is |
|
Statistics to delete. It accepts multiple values separated by comma:
The default 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
192.7.18 DELETE_FIXED_OBJECTS_STATS Procedure
This procedure deletes statistics of all fixed tables.
Syntax
DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameters
Table 192-22 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 |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
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
192.7.19 DELETE_INDEX_STATS Procedure
This procedure deletes index-related statistics.
Syntax
DBMS_STATS.DELETE_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), stattype VARCHAR2 DEFAULT 'ALL', force BOOLEAN DEFAULT FALSE); stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Parameters
Table 192-23 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 |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
Statistics type |
|
When value of this argument is |
|
Statistics to delete. It accepts multiple values separated by comma:
The default is |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20005
: Object statistics are locked
Usage Notes
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.
192.7.20 DELETE_PENDING_STATS Procedure
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 192-24 DELETE_PENDING_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Owner name |
|
Table name |
Security Model
To run this procedure, you need to have the same privilege for gathering statistics on the tables that will be affected by this procedure. The default owner is the user who runs the procedure.
Exceptions
ORA-20000
: Insufficient privileges
Usage Notes
If the parameter tabname
is NULL
delete applies to all tables of the specified schema.
Examples
DBMS_STATS.DELETE_PENDING_STATS('SH', 'SALES');
192.7.21 DELETE_PLSQL_PREFS Procedure
Deletes the named preference from the named PL/SQL function or from all functions in the same package.
Syntax
DBMS_STATS.DELETE_PLSQL_PREFS(
user IN VARCHAR2,
package_name IN VARCHAR2,
function_name IN VARCHAR2,
pname IN VARCHAR2
);
Parameters
Table 192-25 DELETE_PLSQL_PREFS Procedure Parameters
Parameter | Description |
---|---|
ownname |
Owner of the function. If NULL, the current schema is used. |
package_name |
The PL/SQL package name. If NULL, this specifies a stand-alone, top-level function. |
function_name |
Name of the PL/SQL function. The preference will be deleted on this function. If NULL, the preference is deleted on all functions in the package. |
pname |
The preference that you want to delete. Currently only
'dynamic_stats' is accepted.
|
Examples
In this example the owner is the current schema and so the
user
parameter is set to NULL
. The dynamic statistics
preference for the named function is deleted and the global preference is used for this
function.
EXEC DELETE_PLSQL_PREFS(NULL, '<PACKAGE_NAME>', '<FUNCTION_NAME>', 'dynamic_stats')
In this example, the function_name
parameter is set to NULL.
Therefore the dynamic statistics preference is deleted on all functions within the named
PL/SQL package.
EXEC DELETE_PLSQL_PREFS('<USER>', '<PACKAGE_NAME>', NULL,'dynamic_stats')
192.7.22 DELETE_PROCESSING_RATE 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.
Syntax
DBMS_STATS.DELETE_PROCESSING_RATE ( stat_source IN VARCHAR2 DEFAULT NULL);
Parameters
Table 192-26 DELETE_PROCESSING_RATE Procedure Parameters
Parameter | Description |
---|---|
|
Source of processing rates:
|
Usage Notes
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.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20001
: Invalid or illegal input value
192.7.23 DELETE_SCHEMA_PREFS Procedure
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 192-27 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:
|
Table 192-28 Statistics Preferences
Preference | Description |
---|---|
|
Specifies the synopsis generation algorithm. A synopsis is special type of statistic that tracks the number of distinct values (NDV) for each column in a partition. You can consider a synopsis as an internal management structure that samples distinct values. You can set the following values:
|
|
Controls the automatic creation of extensions when database statistics are gathered. You can set the following values:
|
|
Determines whether index statistics are collected as part of gathering table statistics. |
|
User of each shard uses this preference to determine whether to allow shard coordinator to interact with the statistics gathering in each shards. While gathering the statistics in shard coordinator, if the statistics in one of the shards are not up to date, the shard coordinator will try to trigger the statistics gathering in that shard. By using this preference, user can execute or ignore that command from the shard coordinator. You can set the following values:
The default value is |
|
Determines the degree of parallelism used for gathering statistics. |
|
The value determines the percentage of rows to estimate. |
|
Controls column statistics collection and histogram creation. When setting preferences at the global, schema, database, or dictionary level, only |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
The value determines granularity of statistics to collect (only pertinent if the table is partitioned) |
|
This value determines whether or not newly gathered statistics will be published once the gather job has completed. |
|
This value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. |
|
This value controls what synopses to collect when |
|
Specifies when a partition
or subpartition is considered stale. This parameter takes an enumeration of values, such as
The parameter accepts the following values:
Note that the following two executions are different:
The first execution uses
single quotes to set the preference to the value |
|
The application PDB user, uses this preference to determine whether to allow the application root to interact with the statics gathering in PDB. During the statistics gathering of a metadata linked table in the application root, if the statistics in a PDB are in stale state, the application root triggers the statistics gathering for the particular PDB. Using this preference, the user can either execute or ignore the command from the application root. You can set the following values:
The default value is Note: CDB root, different from application root, never triggers statistics gathering on the PDBs and it is not controlled by this preference. |
|
This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. |
|
This controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics. |
|
Specifies the average number
of blocks assumed to be cached in the buffer cache when calculating the index clustering
factor. The preference applies only when gathering statistics using
|
|
Determines the |
Security Model
To run this procedure, you must be the object owner, or have the SYSDBA
privilege, or have the ANALYZE
ANY
system privilege.
Exceptions
ORA-20000
: Insufficient privileges / Schema "<schema>" does not exist
ORA-20001
: Invalid or Illegal input values
Usage Notes
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');
See Also:
Oracle Database SQL Tuning Guide to learn how to manage optimizer statistics preferences
192.7.24 DELETE_SCHEMA_STATS Procedure
This procedure deletes statistics for an entire schema.
Syntax
DBMS_STATS.DELETE_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Parameters
Table 192-29 DELETE_SCHEMA_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the schema. |
|
Identifies the table where statistics are stored. If |
|
Specifies the identifier (optional) associated with these statistics within |
|
Specifies the schema containing |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
You can change the default using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
Indicates whether to force the deletion for locked statistics. When the value is |
|
Specifies which statistics to process. The following values are
supported:
You can specify a list of comma-delimited values. For
example, ' |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
192.7.25 DELETE_SYSTEM_STATS Procedure
This procedure deletes workload statistics (collected using the 'INTERVAL
' or 'START
' and 'STOP
' options) and resets the default to noworkload
statistics (collected using 'NOWORKLOAD
' option), if stattab
is not specified. If stattab
is specified, the subprogram deletes all system statistics with the associated statid
from the stattab
.
Syntax
DBMS_STATS.DELETE_SYSTEM_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 192-30 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 |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20002
: Bad user statistics table; may need to be upgraded
Usage Notes
To run this procedure, you need the GATHER_SYSTEM_STATISTICS
role.
192.7.26 DELETE_TABLE_PREFS Procedure
This procedure deletes the optimizer 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
Table 192-31 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:
|
Table 192-32 Statistics Preferences
Preference | Description |
---|---|
|
Specifies the synopsis generation algorithm. A synopsis is special type of statistic that tracks the number of distinct values (NDV) for each column in a partition. Consider a synopsis as an internal management structure that samples distinct values. You can specify the following preferences:
|
|
Controls the automatic creation of extensions when database statistics are gathered. You can set the following values:
|
|
Determines whether index statistics are collected as part of gathering table statistics. |
|
Determines the degree of parallelism used for gathering statistics. |
|
Determines the percentage of rows to estimate. |
|
Determines granularity of statistics to collect. This value is only relevant for partitioned tables. |
|
Controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics. |
|
This value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. |
|
This value controls what synopses to collect when |
|
Specifies when a partition
or subpartition is considered stale. This parameter takes an enumeration of values, such as
The parameter accepts the following values:
Note that the following two executions are different:
The first execution uses
single quotes to set the preference to the value |
|
Controls column statistics collection and histogram creation. When setting preference at the global, schema, database, or dictionary level, only |
|
The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. |
|
Determines the |
PREFERENCE_OVERRIDES_PARAMETER |
Determines whether to override the input value of a parameter with the preference value of that parameter for a statistics operation. Possible values are:
Specifying this preference does not change the order of precedence of table, global, and default. |
|
Determines whether newly gathered statistics will be published after the statistics gathering job has completed. |
|
Determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. |
|
Specifies which statistics to import or export, accepting
multiple values separated by a comma. Values supported:
The value The default value is ' |
|
Specifies the average number
of blocks assumed to be cached in the buffer cache when calculating the index clustering
factor. The preference applies only when gathering statistics using
|
Exceptions
ORA-20000
: Insufficient privileges
ORA-20001
: Invalid or Illegal input values
Usage Notes
-
To run this procedure, you need to connect as owner of the table, be granted
ANALYZE
privilege on the table, orANALYZE
ANY
system privilege. -
All arguments are of type
VARCHAR2
and values are enclosed in quotes, even when they represent numbers.
Examples
DBMS_STATS.DELETE_TABLE_PREFS('SH', 'SALES', 'CASCADE'); DBMS_STATS.DELETE_TABLE_PREFS('SH', 'SALES', 'DEGREE');
See Also:
Oracle Database SQL Tuning Guide to learn how to manage optimizer statistics preferences
192.7.27 DELETE_TABLE_STATS Procedure
This procedure deletes table-related statistics.
Syntax
DBMS_STATS.DELETE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
cascade_columns BOOLEAN DEFAULT TRUE,
cascade_indexes BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
stat_category VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
Parameters
Table 192-33 DELETE_TABLE_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the schema. |
|
Specifies the name of the table to which this column belongs. |
|
Specifies the name of the table partition or subpartition from which to get the statistics. If the table is partitioned and if |
|
Identifies the user statistics table where statistics will be retrieved. If |
|
Specifies the identifier (optional) associated with these statistics within |
|
Specifies whether the procedure should operate on underlying partitions. If the table is partitioned, and if |
|
Indicates whether to invoke the |
|
Indicates whether to invoke the |
|
Specifies the schema containing |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
You can change the default using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
Indicates whether to force the deletion for locked statistics. When the value is |
|
Specifies which statistics to process. The following values are
supported:
You can specify a list of comma-delimited values. For
example, ' The default value is ' |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
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
192.7.28 DIFF_TABLE_STATS_IN_HISTORY Function
This function compares statistics for a table as of two specified timestamps.
Syntax
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;
Parameters
Table 192-34 DIFF_TABLE_STATS_IN_HISTORY Function Parameters
Parameter | Description |
---|---|
|
Specifies the owner of the table. Specify |
|
Specifies the table for which statistics are to be compared. |
|
Specifies the first timestamp for comparison. |
|
Specifies the second timestamp for comparison. |
|
Specifies the threshold limit. The function reports differences in statistics only if the change percentage exceeds this limit. The default value is |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Usage Notes
If the second timestamp is NULL
, then the function compares the current statistics in the data dictionary with the statistics as of the first timestamp.
192.7.29 DIFF_TABLE_STATS_IN_PENDING Function
This function compares pending statistics to either the current statistics in the data dictionary, or user-specified historical statistics.
Syntax
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;
Parameters
Table 192-35 DIFF_TABLE_STATS_IN_PENDING Function Parameters
Parameter | Description |
---|---|
|
Owner of the table. Specify |
|
Table for which statistics are to be compared. |
|
Timestamp in the statistics history that corresponds to the desired statistics. If the timestamp is |
|
Limit for reporting. The function reports difference in statistics only if it exceeds the specified limit. The default value is |
Security Model
To invoke this procedure you must be owner of the table, or you must have the ANALYZE ANY
privilege. For objects owned by SYS
, you must be either the owner of the table, or you must have either the ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
192.7.30 DIFF_TABLE_STATS_IN_STATTAB Function
This function compares table statistics from two sources.
The function can obtain statistics from the following sources:
-
Two 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
The function also compares the statistics of the dependent objects: indexes, columns, and partitions. It displays statistics of the objects from both sources when the difference between those statistics exceeds a certain threshold (%). You can specify this threshold as an argument to the function. The function uses the statistics corresponding to the first source (stattab1
or time1
) as the basis for computing the difference percentage.
Syntax
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;
Parameters
Table 192-36 DIFF_TABLE_STATS_IN_STATTAB Function Parameters
Parameter | Description |
---|---|
|
Specifies the owner of the table. Specify |
|
Specifies the table for which statistics are to be compared. |
|
Specifies the user statistics table 1. |
|
Specifies the user statistics table 2. If |
|
Specifies the percent thresholds for comparison. The function reports difference in statistics only if it exceeds this limit. The default value is |
|
(optional) Identifies statistics set within |
|
(optional) Identifies statistics set within |
|
Specifies the schema containing |
|
Specifies the schema containing |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
192.7.31 DROP_ADVISOR_TASK Procedure
This procedure drops the specified Optimizer Statistics Advisor task.
Syntax
DBMS_STATS.DROP_ADVISOR_TASK (
task_name IN VARCHAR2);
Parameters
Table 192-37 DROP_ADVISOR_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the Optimizer Statistics Advisor task. |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Example 192-8 Dropping an Optimizer Statistics Advisor Task
This example drops the Optimizer Statistics Advisor task named my_task
:
EXEC DBMS_STATS.DROP_ADVISOR_TASK('my_task');
Note:
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.32 DROP_EXTENDED_STATS Procedure
This function drops the statistics entry that is created for the user specified extension.
This cancels the effects of the CREATE_EXTENDED_STATS Function.
Syntax
DBMS_STATS.DROP_EXTENDED_STATS ( ownname VARCHAR2, tabname VARCHAR2, extension VARCHAR2);
Parameters
Table 192-38 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 |
Exceptions
-
ORA-20000
: Insufficient privileges or extension does not exist -
ORA-20001
: Error when processing extension
Usage Notes
-
To invoke this procedure you must be owner of the table, or you need the
ANALYZE
ANY
privilege. For objects owned bySYS
, you need to be either the owner of the table, or you need theANALYZE
ANY
DICTIONARY
privilege or theSYSDBA
privilege. -
If no extended statistics set is created for the extension, this function throws an error.
192.7.33 DROP_STAT_TABLE Procedure
This procedure drops a user statistics table.
Syntax
DBMS_STATS.DROP_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
Parameters
Table 192-39 DROP_STAT_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the schema |
|
User statistics table identifier |
Exceptions
ORA-20000
: Table does not exists or insufficient privileges.
Usage Notes
To invoke this procedure you need the privileges for dropping the specified table.
192.7.34 EXECUTE_ADVISOR_TASK Function
This function executes a previously created Optimizer Statistics Advisor task.
Syntax
DBMS_STATS.EXECUTE_ADVISOR_TASK (
task_name IN VARCHAR2,
execution_name IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Parameters
Table 192-40 EXECUTE_ADVISOR_TASK Parameters
Parameter | Description |
---|---|
task_name |
Name of the Optimizer Statistics Advisor task. |
execution_name |
A name that qualifies and identifies an advisor execution. If not specified, then the advisor automatically generates it. If the specified execution conflicts with the name of an existing execution, then the function returns an error. |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
You can execute this subprogram for
AUTO_STATS_ADVISOR_TASK
, which is predefined. -
This subprogram executes using invoker's rights.
The results of performing this task depend on the privileges of the executing user:
-
SYSTEM
levelOnly users with both the
ANALYZE ANY
andANALYZE ANY DICTIONARY
privileges can perform this task on system-level rules. -
Operation level
The results depend on the following privileges:
-
Users with both the
ANALYZE ANY
andANALYZE ANY DICTIONARY
privileges can perform this task for all statistics operations. -
Users with the
ANALYZE ANY
privilege but not theANALYZE ANY DICTIONARY
privilege can perform this task for statistics operations related to any schema exceptSYS
. -
Users with the
ANALYZE ANY DICTIONARY
privilege but not theANALYZE ANY
privilege can perform this task for statistics operations related to their own schema and theSYS
schema. -
Users with neither the
ANALYZE ANY
nor theANALYZE ANY DICTIONARY
privilege can only perform this operation for statistics operations relating to their own schema.
-
-
Object level
Users can perform this task for any object for which they have statistics collection privileges.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Returns
This function returns the name of the new execution.
Usage Notes
The results of the execution depend on user privileges and the type of rules:
-
System
To perform the operation on system-level rules, you must have both the
ANALYZE ANY
andANALYZE ANY DICTIONARY
privileges. -
Operation
If you have the
ANALYZE ANY
andANALYZE ANY DICTIONARY
privileges, then you can execute this function for all operations. If you have only theANALYZE ANY
privilege, then you can execute this function for operations related to any schemas exceptSYS
. If you have only theANALYZE ANY DICTIONARY
privilege, then you can execute this function for operations related to any schemas, includingSYS
. If you have neither theANALYZE ANY
nor theANALYZE ANY DICTIONARY
privilege, then you can execute this function only for operations in your own schema. -
Object
If you have the privilege to collect statistics for an object, then you can execute this function for the object.
Example 192-9 Creating and Executing a Task
This example creates an Optimizer Statistics Advisor task named my_task
, and then executes it.
DECLARE
v_tname VARCHAR2(128) := 'my_task';
BEGIN
-- create a task
v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
-- execute the task
v_tname := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
Note:
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.35 EXPORT_COLUMN_STATS Procedure
This procedure exports statistics for a
specified column and stores them in the user statistics table identified by
stattab
.
Syntax
DBMS_STATS.EXPORT_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameters
Table 192-41 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 |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
Oracle Database does not support export or import of statistics across databases of different character sets.
192.7.36 EXPORT_DATABASE_PREFS Procedure
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.
Syntax
DBMS_STATS.EXPORT_DATABASE_PREFS ( stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL add_sys IN BOOLEAN DEFAULT FALSE);
Parameters
Table 192-42 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 |
Exceptions
ORA-20000
: Insufficient privileges
Usage Notes
-
To run this procedure, you need to have the
SYSDBA
role, or bothANALYZE
ANY
DICTIONARY
andANALYZE
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.
Examples
DBMS_STATS.EXPORT_DATABASE_PREFS('STATTAB', statown=>'SH');
192.7.37 EXPORT_DATABASE_STATS Procedure
This procedure exports statistics for
all objects in the database and stores them in the user statistics tables identified by
statown.stattab
.
Syntax
DBMS_STATS.EXPORT_DATABASE_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Parameters
Table 192-43 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 |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
Oracle Database does not support export or import of statistics across databases of different character sets.
192.7.38 EXPORT_DICTIONARY_STATS Procedure
This procedure exports statistics for
all data dictionary schemas (SYS
, SYSTEM
, and RDBMS
component schemas) and stores them in the user statistics table identified by
stattab
.
Syntax
DBMS_STATS.EXPORT_DICTIONARY_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Parameters
Table 192-44 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 |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20002
: Bad user statistics table, may need to upgrade it
Usage Notes
Oracle Database does not support export or import of statistics across databases of different character sets.
192.7.39 EXPORT_FIXED_OBJECTS_STATS Procedure
This procedure exports statistics for
fixed tables and stores them in the user statistics table identified by
stattab
.
Syntax
DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameters
Table 192-45 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 |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20002
: Bad user statistics table, may need to upgrade it
Usage Notes
Oracle Database does not support export or import of statistics across databases of different character sets.
192.7.40 EXPORT_INDEX_STATS Procedure
This procedure retrieves statistics for a particular index and stores them in the user statistics table identified by stattab
.
Syntax
DBMS_STATS.EXPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Parameters
Table 192-46 EXPORT_INDEX_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Name of the schema |
|
Name of the index |
|
Name of the index partition. If the index is partitioned and if |
|
User statistics table identifier describing where to store the statistics |
|
Identifier (optional) to associate with these statistics within |
|
Schema containing |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
-
To invoke this procedure you must be owner of the table, or you need the
ANALYZE
ANY
privilege. For objects owned bySYS
, you need to be either the owner of the table, or you need theANALYZE
ANY
DICTIONARY
privilege or theSYSDBA
privilege. -
Oracle does not support export or import of statistics across databases of different character sets.
192.7.41 EXPORT_PENDING_STATS Procedure
This procedure is used to export the statistics gathered and stored as pending.
Syntax
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);
Parameters
Table 192-47 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 |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
-
If the parameter
tabname
isNULL
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.
Examples
DBMS_STATS.EXPORT_PENDING_STATS(NULL, NULL, 'MY_STAT_TABLE');
192.7.42 EXPORT_SCHEMA_PREFS Procedure
This procedure is used to export the statistics preferences of all the tables owned by the specified owner name.
Syntax
DBMS_STATS.EXPORT_SCHEMA_PREFS ( ownname IN VARCHAR2, stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Parameters
Table 192-48 EXPORT_SCHEMA_PREFS Procedure Parameters
Parameter | Description |
---|---|
|
Owner name |
|
Statistics table name to where to export the statistics |
|
(Optional) Identifier to associate with these statistics within |
|
Schema containing |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
-
To run this procedure, you need to connect as owner, or have the
SYSDBA
privilege, or have theANALYZE
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.
Examples
DBMS_STATS.EXPORT_SCHEMA_PREFS('SH', 'STAT');
192.7.43 EXPORT_SCHEMA_STATS Procedure
This procedure exports statistics for
all objects in the schema identified by ownname
and stores them in the
user statistics tables identified by stattab
.
Syntax
DBMS_STATS.EXPORT_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Parameters
Table 192-49 EXPORT_SCHEMA_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the schema. |
|
Identifies the user statistics table in which to store the exported statistics. |
|
Specifies the identifier (optional) associated with these statistics within |
|
Specifies the schema containing |
|
Specifies which statistics to process. The following values are
supported:
You can specify a list of comma-delimited values. For example,
' The default value is ' |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
Oracle Database does not support export or import of statistics across databases of different character sets.
192.7.44 EXPORT_SYSTEM_STATS Procedure
This procedure retrieves system statistics and stores them in the user statistics table, identified by stattab
.
Syntax
DBMS_STATS.EXPORT_SYSTEM_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameters
Table 192-50 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 |
Security Model
To run this procedure, you must have the GATHER_SYSTEM_STATISTICS
role.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20002
: Bad user statistics table; may need to be upgraded
ORA-20003
: Unable to export system statistics
Usage Notes
Oracle Database does not support the export or import of statistics across databases of different character sets.
192.7.45 EXPORT_TABLE_PREFS Procedure
This procedure is used to export the statistics preferences of the specified table in the specified schema into the specified statistics table.
Syntax
DBMS_STATS.EXPORT_TABLE_PREFS ( ownname IN VARCHAR2, tabname IN VARCHAR2, stattab IN VARCHAR2, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Parameters
Table 192-51 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 |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
-
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.
Examples
DBMS_STATS.EXPORT_TABLE_PREFS('SH', 'SALES', 'STAT');
192.7.46 EXPORT_TABLE_STATS Procedure
This procedure exports statistics for a
specified table (including associated index statistics) and stores them in the user
statistics table identified by stattab
.
Syntax
DBMS_STATS.EXPORT_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
cascade BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
Parameters
Table 192-52 EXPORT_TABLE_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the schema. |
|
Specifies the name of the table. |
|
Specifies the name of the table partition. If the table is partitioned, and if |
|
Specifies the identifier (optional) associated with these statistics within |
|
Specifies the identifier (optional) associated with these statistics within |
|
Indicates whether to export column and index statistics. If |
|
Specifies the schema containing |
|
Specifies which statistics to process. The following values are
supported:
You can specify a list of comma-delimited values. For example,
' The default value is ' |
Security Model
To invoke this procedure you
must be owner of the table or have the ANALYZE ANY
privilege. For objects
owned by SYS
, you must be either the owner of the table or have either the
ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
Usage Notes
Oracle Database does not support export or import of statistics across databases of different character sets.
192.7.47 FLUSH_DATABASE_MONITORING_INFO Procedure
This procedure saves monitoring information for all tables in the dictionary. The database immediately updates corresponding entries in the *_TAB_MODIFICATIONS
, *_TAB_STATISTICS
and *_IND_STATISTICS
views.
Syntax
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
Security Model
The ANALYZE_ANY
system privilege is required to run this procedure.
Exceptions
ORA-20000
: Insufficient privileges
Usage Notes
Starting in Oracle Database 12c Release 2 (12.2), you do not need to call FLUSH_DATABASE_MONITORING_
to view the latest information in INFO
*_TAB_STATISTICS
and *_IND_STATISTICS
because these views show statistics cached in the SGA and stored on disk. Because the GATHER_*_STATS
procedures internally save monitoring information to disk, it is not necessary to run this procedure before gathering statistics.
See Also:
Oracle Database SQL Tuning Guide to learn how to set optimizer statistics preferences
192.7.48 GATHER_DATABASE_STATS Procedures
This procedure gathers statistics for all objects in the database.
Syntax
DBMS_STATS.GATHER_DATABASE_STATS ( estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 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);
Parameters
Table 192-53 GATHER_DATABASE_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Determines the percentage of rows to sample. The valid range is between
0.000001 and 100. Use the constant You can change the default value using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
Determines whether the database uses
random block sampling ( Random block sampling is more efficient, but if the data is not randomly distributed on disk, then sample values may be somewhat correlated. This parameter is only relevant when estimating statistics. |
|
When setting preference on global, schema, database or dictionary level, only
The default is |
|
Determines the degree of parallelism used for gathering statistics. The default for Use the constant
|
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
Determines whether to collect index statistics as part of gathering table statistics. Specifying this option is
equivalent to running the |
|
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 |
|
Specifies which objects require statistics to be gathered. Valid values are as follows:
|
|
List of objects found to be stale or empty |
|
Schema containing |
|
Gathers statistics on the objects owned by the |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
If set to
|
|
A list of object filters. When provided, |
Exceptions
ORA-20000
: Insufficient privileges
ORA-20001
: Bad input value
Usage Notes
To run this procedure, you need to have the SYSDBA
role or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privileges.
If the GATHER AUTO
option is used then frequency histograms will be created using a sample rather than a full scan. The following scenario shows where GATHER AUTO
is used:
-
A table is created like this:
CREATE TABLE NEWTAB as SELECT * FROM ....
.This will create statistics on
NEWTAB
but no histograms. -
Next, the DBA creates the histograms using
GATHER AUTO
ongather_table_stats
. -
The
FREQUENCY
histograms onNEWTAB
will be created using a sample rather than a full table scan.
192.7.49 GATHER_DICTIONARY_STATS Procedure
This procedure gathers statistics for dictionary schemas SYS
, SYSTEM
and schemas of RDBMS components.
Syntax
DBMS_STATS.GATHER_DICTIONARY_STATS ( comp_id VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 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);
Parameters
Table 192-54 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. |
|
The method options. This parameter accepts the following values:
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 |
|
Specifies which objects require statistics to be gathered. Valid values are as follows:
|
|
The list of objects found to be stale or empty. |
|
Schema containing |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
You can change the default using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
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- |
Usage Notes
You must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
If the GATHER AUTO
option is used then frequency histograms will be created using a sample rather than a full scan. The following scenario shows where GATHER AUTO
is used:
-
A table is created like this:
CREATE TABLE NEWTAB as SELECT * FROM ....
.This will create statistics on
NEWTAB
but no histograms. -
Next, the DBA creates the histograms using
GATHER AUTO
ongather_table_stats
. -
The
FREQUENCY
histograms onNEWTAB
will be created using a sample rather than a full table scan.
Exceptions
ORA-20000
: Index does not exist or insufficient privileges
ORA-20001
: Bad input value
ORA-20002
: Bad user statistics table, may need to upgrade it
192.7.50 GATHER_FIXED_OBJECTS_STATS Procedure
This procedure gathers statistics for all fixed objects (dynamic performance tables).
Syntax
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')));
Parameters
Table 192-55 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 |
Usage Notes
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege to execute this procedure.
Exceptions
ORA-20000
: Insufficient privileges
ORA-20001
: Bad input value
ORA-20002
: Bad user statistics table, may need to upgrade it
192.7.51 GATHER_INDEX_STATS Procedure
This procedure gathers index statistics. It attempts to parallelize as much of the work as possible.
Restrictions are described in the individual parameters. This operation will not parallelize with certain types of indexes, including cluster indexes, domain indexes, and bitmap join indexes. The granularity
and no_invalidate
arguments are not relevant to these types of indexes.
Syntax
DBMS_STATS.GATHER_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameters
Table 192-56 GATHER_INDEX_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Schema of index to analyze |
|
Name of index |
|
Name of partition |
|
Percentage of rows to estimate ( |
|
User statistics table identifier describing where to save the current statistics |
|
Identifier (optional) to associate with these statistics within |
|
Schema containing |
|
Degree of parallelism. The default for |
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
Does not invalidate the dependent cursors if set to |
|
Gather statistics on object even if it is locked |
Exceptions
ORA-20000
: Index does not exist or insufficient privileges
ORA-20001
: Bad input value
Usage Notes
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.
192.7.52 GATHER_PROCESSING_RATE Procedure
This procedure starts the job of gathering the processing rates which end after an interval
defined in minutes.
Syntax
DBMS_STATS.GATHER_PROCESSING_RATE ( gathering_mode IN VARCHAR2 DEFAULT 'START', interval IN NUMBER DEFAULT NULL);
Parameters
Table 192-57 GATHER_PROCESSING_RATE Procedure Parameters
Parameter | Description |
---|---|
|
Mode: |
|
Time interval (number of minutes) for which the processing must be gathered |
Usage Notes
-
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.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20001
: Invalid or illegal input value
192.7.53 GATHER_SCHEMA_STATS Procedures
This procedure gathers statistics for all objects in a schema.
Syntax
DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 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);
Parameters
Table 192-58 GATHER_SCHEMA_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Schema to analyze ( |
|
Determines the percentage of rows to sample. The valid range is between
0.000001 and 100. Use the constant You can change the default value using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
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:
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 |
|
Specifies which objects require statistics to be gathered. Valid values are as follows:
|
|
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, |
Usage Notes
To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY
privilege. For objects owned by SYS
, you must be either the owner of the table, or you need the ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
If the GATHER AUTO
option is used then frequency histograms will be created using a sample rather than a full scan. The following scenario shows where GATHER AUTO
is used:
-
A table is created like this:
CREATE TABLE NEWTAB as SELECT * FROM ....
.This will create statistics on
NEWTAB
but no histograms. -
Next, the DBA creates the histograms using
GATHER AUTO
ongather_table_stats
. -
The
FREQUENCY
histograms onNEWTAB
will be created using a sample rather than a full table scan.
Exceptions
ORA-20000
: Schema does not exist or insufficient privileges
ORA-20001
: Bad input value
Examples
Applying an Object Filter List
The following example specifies that the tables SH.SALES
and SH.COSTS
, if stale, will have statistics gathered upon them.
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;
192.7.54 GATHER_SYSTEM_STATS Procedure
This procedure gathers system statistics.
Syntax
DBMS_STATS.GATHER_SYSTEM_STATS (
gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD',
interval INTEGER DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameters
Table 192-59 GATHER_SYSTEM_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the mode in which the database gathers system statistics. Possible values are:
|
|
Specifies the number of minutes in which to gather system statistics. This parameter applies only when |
|
Specifies the table in which the database stores the statistics. |
|
Specifies an optional identifier associated with the statistics saved in |
|
Specifies the schema containing |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20001
: Invalid input value
ORA-20002
: Bad user statistics table; may need to be upgraded
ORA-20003
: Unable to gather system statistics
ORA-20004
: Error in the INTERVAL
mode: system parameter job_queue_processes
must be >0
Usage Notes
To run this procedure, you must have the GATHER_SYSTEM_STATISTICS
role.
Examples
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;
192.7.55 GATHER_TABLE_STATS Procedure
This procedure gathers table, column, and index statistics. It attempts to parallelize as much work as possible, but there are some restrictions, which are described in the individual parameters.
Syntax
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), stattype VARCHAR2 DEFAULT 'DATA', force BOOLEAN DEFAULT FALSE, context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative options VARCHAR2 DEFAULT get_param('OPTIONS'));
Parameters
Table 192-60 GATHER_TABLE_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Schema containing the table. |
|
Name of the table. |
|
Name of the partition. |
|
Determines the percentage of rows to sample. The valid range is between
0.000001 and 100. Use the constant You can change the default value using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
Determines whether the database uses
random block sampling ( Random block sampling is more efficient, but if the data is not randomly distributed on disk, then sample values may be somewhat correlated. This parameter is only relevant when estimating statistics. |
|
The default is |
|
Determines the degree of parallelism used for gathering statistics. The default for Use the constant
|
|
Granularity of statistics to collect (only pertinent if the table is partitioned).
'
|
|
Determines whether to collect index statistics as part of gathering table statistics. Specifying this option is
equivalent to running the |
|
User statistics table identifier describing where to save the current statistics |
|
Identifier (optional) to associate with these statistics within |
|
Schema containing |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
If set to
|
|
Statistics type. The only value allowed is |
|
Gather statistics of table even if it is locked |
|
Not used. |
|
Determines the
|
Usage Notes
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.
If the GATHER AUTO
option is used then frequency histograms will be created using a sample rather than a full scan. The following scenario shows where GATHER AUTO
is used:
-
A table is created like this:
CREATE TABLE NEWTAB as SELECT * FROM ....
.This will create statistics on
NEWTAB
but no histograms. -
Next, the DBA creates the histograms using
GATHER AUTO
ongather_table_stats
. -
The
FREQUENCY
histograms onNEWTAB
will be created using a sample rather than a full table scan.
Exceptions
ORA-20000
: Table does not exist or insufficient privileges
ORA-20001
: Bad input value
Examples
An extension can be either a column group (see Example 1) or an expression (see Example 2).
Example 1
DBMS_STATS.GATHER_TABLE_STATS( 'SH', 'SALES', method_opt => 'FOR COLUMNS (empno, deptno)');
Example 2
DBMS_STATS.GATHER_TABLE_STATS( 'SH', 'SALES', method_opt => 'FOR COLUMNS (sal+comm)');
192.7.56 GENERATE_STATS Procedure
This deprecated procedure generates object statistics from previously collected statistics of related objects. The currently supported objects are b-tree and bitmap 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 the GATHER_INDEX_STAT
procedure. See "GATHER_INDEX_STATS Procedure".
Syntax
DBMS_STATS.GENERATE_STATS ( ownname VARCHAR2, objname VARCHAR2, organized NUMBER DEFAULT 7, force BOOLEAN default FALSE);
Parameters
Table 192-61 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 |
Usage Notes
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.
Exceptions
ORA-20000
: Unsupported object type of object does not exist
ORA-20001
: Invalid option or invalid statistics
192.7.57 GET_ADVISOR_OPR_FILTER Procedure
This procedure creates an operation filter for an Optimizer Statistics Advisor task.
Syntax
DBMS_STATS.GET_ADVISOR_OPR_FILTER (
opr_id IN NUMBER,
opr_filter IN OUT NOCOPY StatsAdvOpr);
Parameters
Table 192-62 GET_ADVISOR_OPR_FILTER Procedure Parameters
Parameter | Description |
---|---|
|
The ID of the statistics operation stored in the |
|
The Optimizer Statistics Advisor filter that is generated based on the specified statistics operation. |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Usage Notes
You can specify the filter using either the operation ID or the filter ID, but not both at the same time.
Note:
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.58 GET_ADVISOR_RECS Function
This function generates a recommendation report for the specified item.
Syntax
DBMS_STATS.GET_ADVISOR_RECS(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
rec IN VARCHAR2,
type IN VARCHAR2 := 'TEXT')
RETURN CLOB;
Parameters
Table 192-63 GET_ADVISOR_RECS Function Parameters
Parameter | Description |
---|---|
|
The owner of the table. |
|
The name of the table. |
|
The Optimizer Statistics Advisor recommendation.
|
|
Type of the report: |
Security Model
Note the following:
-
To execute this subprogram, you must have the
ADVISOR
privilege. -
You must have the privileges to gather statistics for the objects for which recommendations are generated.
-
You must be the owner of the task.
-
This subprogram executes using invoker's rights.
Usage Notes
The advisor does not make recommendations for manual statistics gathering. The database only make recommendations for automatic statistics gathering jobs, with the main goal of finishing the job within the maintenance window. As long as the automatic job finishes, the database does not make further recommendations.
Exceptions
-
ORA-20000
: Insufficient privileges -
ORA-20001
: Invalid input values -
ORA-20012
: Optimizer Statistics Advisor errors
Note:
Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor
192.7.59 GET_COLUMN_STATS Procedures
These overloaded procedures get column-related statistics. In the user-defined statistics version, the procedure returns the type of statistics stored.
Syntax
DBMS_STATS.GET_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
distcnt OUT NUMBER,
density OUT NUMBER,
nullcnt OUT NUMBER,
srec OUT StatRec,
avgclen OUT NUMBER,
statown VARCHAR2 DEFAULT NULL,
realtime_stats BOOLEAN iDEFAULT TRUE);
Use the following for user-defined statistics:
DBMS_STATS.GET_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
ext_stats OUT RAW,
stattypown OUT VARCHAR2 DEFAULT NULL,
stattypname OUT VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Parameters
Table 192-64 GET_COLUMN_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Name of the schema |
|
Specifies the name of the table to which this column belongs. |
|
Specifies the name of the column or extension. |
|
Specifies the name of the table partition from which to get the statistics. If the table is partitioned, and if |
|
Specifies the statistics table ID describing where to retrieve the statistics. If |
|
Specifies an optional identifier associated with these statistics within |
|
Specifies the user-defined statistics. |
|
Specifies the schema of the statistics type. |
|
Specifies the name of the statistics type. |
|
Specifies the number of distinct values. |
|
Specifies the column density. |
|
Specifies the number of |
|
Specifies the structure holding the internal representation of the column minimum, maximum, and histogram values. |
|
Specifies the average length of the column (in bytes). |
|
Specifies the schema containing |
|
Specifies whether to include real-time statistics. The default value is |
Security Model
To invoke this procedure you must be owner of the table or have the ANALYZE ANY
privilege. For objects owned by SYS
, you must be the owner of the table, or have either the ANALYZE ANY DICTIONARY
privilege or the SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object
Usage Notes
Before invoking this procedure, ensure that the table exists.
192.7.60 GET_INDEX_STATS Procedures
This overloaded procedure gets all index-related statistics. In the form of this procedure that deals with user-defined statistics, the statistics type returned is the type stored, in addition to the user-defined statistics.
Syntax
DBMS_STATS.GET_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
numrows OUT NUMBER,
numlblks OUT NUMBER,
numdist OUT NUMBER,
avglblk OUT NUMBER,
avgdblk OUT NUMBER,
clstfct OUT NUMBER,
indlevel OUT NUMBER,
statown VARCHAR2 DEFAULT NULL);
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);
Use the following form of the procedure 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);
Parameters
Table 192-65 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) |
Security Model
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.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object
Usage Notes
-
The optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The database calculates the total cost of the operation by combining 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.
-
The database maintains
cachedblk
andcachehit
at all times. However, the database uses the corresponding caching statistics for optimization as part of the table and index statistics only when the user calls theDBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS
procedure for automatic mode orDBMS_STATS.GATHER_SYSTEM_STATS
for manual mode. To prevent the user from utilizing inaccurate and unreliable data, the optimizer computes a “confidence factor” for eachcachehit
and acachedblk
for each object. If the confidence factor for the value meets confidence criteria, then the database uses this value; otherwise, the database uses defaults. -
The automatic maintenance algorithm for object caching statistics assumes that only one major database workload exists. The algorithm adjusts statistics to this workload, ignoring other "minor" workloads. If this assumption is false, then you must use manual mode for maintaining object caching statistics.
-
The object caching statistics maintenance algorithm for automatic 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 created
-
When the system does not have one major workload resulting in averages not corresponding to real values
-
See Also:
Oracle Database SQL Tuning Guide to learn how to manage optimizer statistics
192.7.61 GET_PARAM Function
This function returns the default value of parameters of DBMS_STATS
procedures.
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 DBMS_STATS Deprecated Subprograms.
Syntax
DBMS_STATS.GET_PARAM ( pname IN VARCHAR2) RETURN VARCHAR2;
Parameters
Table 192-66 GET_PARAM Function Parameters
Parameter | Description |
---|---|
|
Parameter name |
Exceptions
ORA-20001
: Invalid input values
192.7.62 GET_PLSQL_PREFS Procedure
Returns the preference used for the named function. If a function has no preference set, the procedure returns the global preference.
Syntax
DBMS_STATS.GET_PLSQL_PREFS(
pname IN VARCHAR2,
user IN VARCHAR2,
package_name IN VARCHAR2,
function_name IN VARCHAR2)
RETURN VARCHAR2;
You can get the global preference by passing in NULL for
user
, package_name
, and function_name
.
Parameters
Table 192-67 GET_PLSQL_PREFS Parameters
Parameter | Description |
---|---|
pname |
The preference that you want to get. Currently only
'dynamic_stats' is accepted.
|
user |
The owner of the function. If NULL, the current user is used. |
package_name |
The name of the PL/SQL package. If NULL, this specifies a standalone, top-level function. |
function_name |
The name of the PL/SQL function. |
Example
Get the preference value that is set for an individual PL/SQL function within
a package. The parameter user
is NULL in order to specify the current user:
SELECT DBMS_STATS.GET_PLSQL_PREFS('dynamic_stats',NULL,'<PL/SQL_PACKAGE NAME>','<PL/SQL FUNCTION
NAME>') pref_value FROM dual;
192.7.63 GET_PREFS Function
This function returns the default value of the specified preference.
Syntax
DBMS_STATS.GET_PREFS (
pname IN VARCHAR2,
ownname IN VARCHAR2 DEFAULT NULL,
tabname IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
Parameters
Table 192-68 GET_PREFS Function Parameters
Parameter | Description |
---|---|
|
Preference name. The possible values are:
|
|
Owner name |
|
Table name |
Table 192-69 Preference Descriptions
Preference Name | Description |
---|---|
|
Specifies the synopsis generation algorithm. A synopsis is special type of statistic that tracks the number of distinct values (NDV) for each column in a partition. Consider a synopsis as an internal management structure that samples distinct values. You can specify the following preferences:
|
|
Controls the automatic creation of extensions when database statistics are gathered. You can set the following values:
|
|
Enables or disables the high-frequency automatic optimizer statistics collection. Values are:
|
|
Configures the maximum run time in
seconds of an execution of high-frequency automatic optimizer statistics collection. The maximum value is |
|
Specifies the interval in seconds between executions of high-frequency automatic optimizer statistics collection. The default value is 3600 but Oracle sets it differently for some platforms and Oracle Database releases. |
|
Determines whether index statistics are collected as part of gathering table statistics. |
|
Determines whether statistics are gathered concurrently on multiple objects, or serially, one object at a time. Valid values are:
|
|
Determines degree of parallelism used for gathering statistics. |
|
Determines the percentage of rows to sample. The valid range is between
0.000001 and 100. Use the constant |
|
Controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics. This preference takes two values:
|
|
Determines the granularity of statistics to collect. This preference is only relevant for partitioned tables. The following values are valid:
|
|
Determines whether the global statistics for a partitioned table are maintained without performing a full table scan. When a table is partitioned, an application typically loads data into a new partition. As new partitions are added and data is loaded, global table statistics must be kept up to date. If the following conditions are met, then the database updates the global table statistics by scanning only the changed partitions instead of the entire table:
If the
|
|
Controls which synopses to collect when
|
|
Specifies when a partition
or subpartition is considered stale. This parameter takes an enumeration of values, such as
The parameter accepts the following values:
Note that the following two executions are different:
The first execution uses
single quotes to set the preference to the value |
|
Controls column statistics collection and histogram creation. When setting preferences at the global, schema, database, or dictionary level, only
The
The default is |
|
Controls the invalidation of
dependent cursors when statistics are gathered. The parameter takes the following values:
If set to
|
|
Determines the
|
|
Determines whether to override the input value of a parameter with the preference value of that parameter for a statistics operation. Possible values are:
Specifying this preference does not change the order of precedence of table, global, and default. |
|
Determines whether the database publishes newly gathered statistics after the gathering job completes. You can gather statistics without publishing them immediately. This technique enables you to test new statistics before publishing them. |
|
Determines the percentage of rows in a table that must change before the statistics on that table are stale and need to be regathered. The valid domain for
|
|
Specifies which statistics to import or export, accepting multiple values separated by a comma. Values supported:
The value |
|
Specifies the average number
of blocks assumed to be cached in the buffer cache when calculating the index clustering
factor. The preference applies only when gathering statistics using
|
|
Specifies the number of
minutes before timing out for locks and pins required for updating statistics. It accepts
values in the range |
Security Model
No special privilege or role is needed to invoke this procedure. To gather statistics concurrently, however, you must either have the DBA role, or have the following privileges in addition to privileges that are required for gathering statistics: CREATE JOB
, MANAGE SCHEDULER
, and MANAGE ANY QUEUE
.
Exceptions
-
ORA-20000
: Unable to gather statistics concurrently: Resource Manager is not enabled. -
ORA-20001
: Invalid input values
Usage Notes
Note the following guidelines:
-
The
CONCURRENT
preference determines whether statistics are gathered concurrently when the user issuesGATHER_*_STATS
procedures.DBMS_STATS
can collect statistics for a single object in parallel based on the value of theDEGREE
parameter. However, parallelism is limited to one object. TheCONCURRENT
preference extends the scope of parallelism to multiple database objects. This approach is primarily intended for multi-CPU systems, and may not be suitable for small databases on single-CPU computers.To gather statistics concurrently, Resource Manager must be enabled, and the setting for the
JOB_QUEUE_PROCESSES
initialization parameter must be at least4
. -
If the
ownname
andtabname
are provided, and if a preference has been entered for the table, then 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 it returns the default value.
See Also:
Oracle Database SQL Tuning Guide to learn how to get optimizer statistics preferences
192.7.64 GET_STATS_HISTORY_AVAILABILITY Function
This function returns oldest timestamp where statistics history is available. Users cannot restore statistics to a timestamp older than this one.
Syntax
DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY RETURN TIMESTAMP WITH TIMEZONE;
Usage Notes
No special privilege or role is needed to invoke this procedure.
192.7.65 GET_STATS_HISTORY_RETENTION Function
This function returns the current statistics history retention value.
Syntax
DBMS_STATS.GET_STATS_HISTORY_RETENTION RETURN NUMBER;
Usage Notes
No special privilege or role is needed to invoke this procedure.
192.7.66 GET_SYSTEM_STATS Procedure
This procedure gets system statistics from stattab
, or from the dictionary if stattab
is NULL
.
Syntax
DBMS_STATS.GET_SYSTEM_STATS ( status OUT VARCHAR2, dstart OUT DATE, dstop OUT DATE, pname IN VARCHAR2, pvalue OUT NUMBER, stattab IN VARCHAR2 DEFAULT NULL, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
Parameters
Table 192-70 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 |
Exceptions
ORA-20000
: Object does not exist or insufficient privileges
ORA-20002
: Bad user statistics table; may need to be upgraded
ORA-20003
: Unable to gather system statistics
ORA-20004
: Parameter does not exist
Usage Notes
To run this procedure, you need the GATHER_SYSTEM_STATISTICS
role.
192.7.67 GET_TABLE_STATS Procedure
This overloaded procedure gets all table-related statistics.
Syntax
DBMS_STATS.GET_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
numrows OUT NUMBER,
numblks OUT NUMBER,
avgrlen OUT NUMBER,
statown VARCHAR2 DEFAULT NULL,
realtime_stats BOOLEAN DEFAULT TRUE);
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,
im_imcu_count OUT NUMBER,
im_block_count OUT NUMBER,
scanrate OUT NUMBER,
realtime_stats BOOLEAN DEFAULT TRUE);
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,
cachedblk OUT NUMBER,
cachehit OUT NUMBER,
realtime_stats BOOLEAN DEFAULT TRUE);
Parameters
Table 192-71 GET_TABLE_STATS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the schema. |
|
Specifies the name of the table to which this column belongs. |
|
Specifies the name of the table partition from which to get the statistics. If the table is partitioned and if |
|
Specifies the user statistics table ID. This ID describes where to retrieve the statistics. If |
|
Specifies the optional ID associates with these statistics within |
|
Specifies the number of rows in the table or partition. |
|
Specifies the number of blocks in the table or partition. |
|
Specifies the average row length for the table or partition. |
|
Specifies the schema containing |
|
Specifies the number of In-Memory Compression Units (IMCUs) in the table or partition. |
|
Specifies the number of In-Memory blocks in the table or partition. An In-Memory block corresponds to a specific data block on disk. If the table is fully populated in the IM column store, then the number of In-Memory blocks equals the number of data blocks. |
|
Specifies the rate, in MB/s, at which the database scans external tables. This parameter is relevant only for external tables. |
|
Specifies whether to include real-time statistics. The default value is |
|
For internal use only. |
|
For internal use only. |
Security Model
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 must be either the owner of the table, or have the ANALYZE ANY DICTIONARY
or SYSDBA
privilege.
Exceptions
ORA-20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object
ORA-20002
: Bad user statistics table; may need to upgrade it
Usage Notes
-
The optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The database calculates the total cost of the operation by combining 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.
-
The database maintains
cachedblk
andcachehit
at all times. However, the database uses the corresponding caching statistics for optimization as part of the table and index statistics only when the user calls theDBMS_STATS.GATHER_*_STATS
procedure for automatic mode orDBMS_STATS.GATHER_SYSTEM_STATS
for manual mode. To prevent the user from utilizing inaccurate and unreliable data, the optimizer computes a “confidence factor” for eachcachehit
and acachedblk
for each object. If the confidence factor for the value meets confidence criteria, then the database uses this value; otherwise, the database uses defaults. -
The automatic maintenance algorithm for object caching statistics assumes that only one major database workload exists. The algorithm adjusts statistics to this workload, ignoring other "minor" workloads. If this assumption is false, then you must use manual mode for maintaining object caching statistics.
-
The object caching statistics maintenance algorithm for automatic 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 created
-
When the system does not have one major workload resulting in averages not corresponding to real values
-
-
The database does not support export or import of statistics across databases of different character sets.
See Also:
Oracle Database SQL Tuning Guide to learn how to manage optimizer statistics preferences
192.7.68 IMPLEMENT_ADVISOR_TASK Function
This function implements the recommendations made by Optimizer Statistics Advisor.
Syntax
DBMS_STATS.IMPLEMENT_ADVISOR_TASK (
task_name IN VARCHAR2,
execution_name IN VARCHAR2 := NULL,
level IN VARCHAR2 := 'TYPICAL')
RETURN CLOB;
Parameters
Table 192-72 IMPLEMENT_ADVISOR_TASK Function Parameters
Parameter | Description |
---|---|
|
The name of the Optimizer Statistics Advisor task. |
|
A name that qualifies and identifies an advisor execution. If not specified, then the advisor automatically generates it. If the specified execution conflicts with the name of an existing execution, then the function returns an error. |
|
The level of the implementation. Possible values are
|