162 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:

162.1 DBMS_STATS Overview

To improve performance, the database enables you to collect statistics of many different types. This package is concerned with optimizer statistics only. 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 statistics stored in the dictionary have an effect on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel.

Starting in Oracle Database 12c Release 2 (12.2), 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, but you can also run it on demand.

See Also:

Oracle Database SQL Tuning Guide to learn how to manage optimizer statistics

162.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:

The following subprogram is deprecated with Oracle Database 12c and later:

162.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;

162.4 DBMS_STATS Constants

The DBMS_STATS package defines several constants to use specifying parameter values.

Table 162-1 DBMS_STATS Constants

Name Type Description

ADD_GLOBAL_PREFS

NUMBER

Copies global preferences

AUTO_CASCADE

BOOLEAN

Lets Oracle decide whether to collect statistics for indexes or not

AUTO_DEGREE

NUMBER

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

AUTO_INVALIDATE

BOOLEAN

Lets Oracle decide when to invalidate dependent cursors

AUTO_SAMPLE_SIZE

NUMBER

Indicates that auto-sample size algorithms should be used

PURGE_ALL

TIMESTAMP WITH TIME ZONE

A flag that can be passed to the PURGE_STATS Procedure and unconditionally deletes all the history statistics. The deletion uses TRUNCATE statements on the various dictionary statistics tables holding the history of statistics.

RECLAIM_SYNOPSIS

TIMESTAMP WITH TIME ZONE

A constant used for reclaiming synopsis table space.

162.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:

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

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

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

GENERATE_STATS Procedure

Setting or Getting Statistics

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

In the special versions of the SET_*_STATS procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or 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.

Note:

Oracle does not support export or import of statistics across databases of different character sets.

Locking or Unlocking Statistics

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

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

Restoring and Purging Statistics History

Use the following procedures to restore statistics as of a specified timestamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.

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

The other DBMS_STATS procedures related to restoring statistics are:

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 to TRUE 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 in V$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 value OPERATION in V$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 in V$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

162.6 DBMS_STATS Data Structures

The DBMS_STATS package defines a RECORD type.

RECORD Types

162.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 162-2 STAT_REC Attributes

Field Description

epc

Number of buckets in histogram

minval

Minimum value

maxval

Maximum value

bkvals

Array of bucket numbers

novals

Array of normalized end point values

chvals

Array of dumped end point values

eavals

Array of end point actual values

rpcnts

Array of end point value frequencies

eavs

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.

162.7 Summary of DBMS_STATS Subprograms

This table lists the DBMS_STATS subprograms and briefly describes them.

Table 162-3 DBMS_STATS Package Subprograms

Subprogram Description

ALTER_STATS_HISTORY_RETENTION Procedure

Changes the statistics history retention value

CANCEL_ADVISOR_TASK Procedure

Cancels an Optimizer Statistics Advisor execution

CONFIGURE_ADVISOR_FILTER Function

Configures the filter list for an Optimizer Statistics Advisor task

CONFIGURE_ADVISOR_OBJ_FILTER Function

Configures an object filter for an Optimizer Statistics Advisor task

CONFIGURE_ADVISOR_OPR_FILTER Functions

Configures an operation filter for an Optimizer Statistics Advisor task

CONFIGURE_ADVISOR_RULE_FILTER Function

Configures a rule filter for an Optimizer Statistics Advisor task

CREATE_ADVISOR_TASK Function

Creates an advisor task for the Optimizer Statistics Advisor

CONVERT_RAW_VALUE Procedures

Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value

CONVERT_RAW_VALUE_NVARCHAR Procedure

Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value

CONVERT_RAW_VALUE_ROWID Procedure

Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value

COPY_TABLE_STATS Procedure

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

CREATE_EXTENDED_STATS Function

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

CREATE_STAT_TABLE Procedure

Creates a table with name stattab in ownname's schema which is capable of holding statistics

DELETE_COLUMN_STATS Procedure

Deletes column-related statistics

DELETE_DATABASE_PREFS Procedure

Deletes the statistics preferences of all the tables

DELETE_DATABASE_STATS Procedure

Deletes statistics for the entire database

DELETE_DICTIONARY_STATS Procedure

Deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and database component schemas)

DELETE_FIXED_OBJECTS_STATS Procedure

Deletes statistics of all fixed tables

DELETE_INDEX_STATS Procedure

Deletes index-related statistics

DELETE_PENDING_STATS Procedure

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

DELETE_PROCESSING_RATE Procedure

Deletes the processing rate of a given statistics source. If the source is not specified, it deletes the statistics of all the sources

DELETE_SCHEMA_PREFS Procedure

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

DELETE_SCHEMA_STATS Procedure

Deletes schema-related statistics

DELETE_SYSTEM_STATS Procedure

Deletes system statistics

DELETE_TABLE_PREFS Procedure

Deletes statistics preferences of the specified table in the specified schema

DELETE_TABLE_STATS Procedure

Deletes table-related statistics

DIFF_TABLE_STATS_IN_HISTORY Function

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

DIFF_TABLE_STATS_IN_PENDING Function

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

DIFF_TABLE_STATS_IN_STATTAB Function

Compares statistics for a table from two different sources

DROP_ADVISOR_TASK Procedure

Drops the specified Optimizer Statistics Advisor task

DROP_EXTENDED_STATS Procedure

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

DROP_STAT_TABLE Procedure

Drops a user statistics table created by CREATE_STAT_TABLE

EXECUTE_ADVISOR_TASK Function

Executes a previously created Optimizer Statistics Advisor task

EXPORT_COLUMN_STATS Procedure

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

EXPORT_DATABASE_PREFS Procedure

Exports the statistics preferences of all the tables

EXPORT_DATABASE_STATS Procedure

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

EXPORT_DICTIONARY_STATS Procedure

Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) and stores them in the user statistics table identified by stattab

EXPORT_FIXED_OBJECTS_STATS Procedure

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

EXPORT_INDEX_STATS Procedure

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

EXPORT_PENDING_STATS Procedure

Exports the statistics gathered and stored as pending

EXPORT_SCHEMA_PREFS Procedure

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

EXPORT_SCHEMA_STATS Procedure

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

EXPORT_SYSTEM_STATS Procedure

Retrieves system statistics and stores them in the user statistics table

EXPORT_TABLE_PREFS Procedure

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

EXPORT_TABLE_STATS Procedure

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

FLUSH_DATABASE_MONITORING_INFO Procedure

Flushes in-memory monitoring information for all the tables to the dictionary

GATHER_DATABASE_STATS Procedures

Gathers statistics for all objects in the database

GATHER_DICTIONARY_STATS Procedure

Gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components

GATHER_FIXED_OBJECTS_STATS Procedure

Gathers statistics of fixed objects

GATHER_INDEX_STATS Procedure

Gathers index statistics

GATHER_PROCESSING_RATE Procedure

Starts the job of gathering the processing rates which end after interval defined in minutes

GATHER_SCHEMA_STATS Procedures

Gathers statistics for all objects in a schema

GATHER_SYSTEM_STATS Procedure

Gathers system statistics

GATHER_TABLE_STATS Procedure

Gathers table and column (and index) statistics

GENERATE_STATS Procedure

Generates object statistics from previously collected statistics of related objects

GET_ADVISOR_OPR_FILTER Procedure

Creates an operation filter for an Optimizer Statistics Advisor operation

GET_ADVISOR_RECS Function

Generates a recommendation report for the specified item

GET_COLUMN_STATS Procedures

Gets all column-related information

GET_INDEX_STATS Procedures

Gets all index-related information

GET_PARAM Function

Gets the default value of parameters of DBMS_STATS procedures (see DBMS_STATS Deprecated Subprograms)

GET_PREFS Function

Gets the default value of the specified preference

GET_STATS_HISTORY_AVAILABILITY Function

Gets the oldest timestamp where statistics history is available

GET_STATS_HISTORY_RETENTION Function

Returns the current statistics history retention value

GET_SYSTEM_STATS Procedure

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

GET_TABLE_STATS Procedure

Gets all table-related information

IMPLEMENT_ADVISOR_TASK Function

Implements the recommendations made by Optimizer Statistics Advisor

IMPORT_COLUMN_STATS Procedure

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

IMPORT_DATABASE_PREFS Procedure

Imports the statistics preferences of all the tables

IMPORT_DATABASE_STATS Procedure

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

IMPORT_DICTIONARY_STATS Procedure

Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) from the user statistics table and stores them in the dictionary

IMPORT_FIXED_OBJECTS_STATS Procedure

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

IMPORT_INDEX_STATS Procedure

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

IMPORT_SCHEMA_PREFS Procedure

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

IMPORT_SCHEMA_STATS Procedure

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

IMPORT_SYSTEM_STATS Procedure

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

IMPORT_TABLE_PREFS Procedure

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

IMPORT_TABLE_STATS Procedure

Retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary

INTERRUPT_ADVISOR_TASK Procedure

Interrupts a currently executing Optimizer Statistics Advisor task.

LOCK_PARTITION_STATS Procedure

Locks statistics for a partition

LOCK_SCHEMA_STATS Procedure

Locks the statistics of all tables of a schema

LOCK_TABLE_STATS Procedure

Locks the statistics on the table

MERGE_COL_USAGE Procedure

Merges column usage information from a source database, by means of a dblink, into the local database

PREPARE_COLUMN_VALUES Procedures

Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure

PREPARE_COLUMN_VALUES_NVARCHAR 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

PREPARE_COLUMN_VALUES_ROWID Procedure

Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure

PUBLISH_PENDING_STATS Procedure

Publishes the statistics gathered and stored as pending

PURGE_STATS Procedure

Purges old versions of statistics saved in the dictionary

REMAP_STAT_TABLE Procedure

Remaps the names of objects in the user statistics table

REPORT_ADVISOR_TASK Function

Reports the results of an Optimizer Advisor Task.

REPORT_COL_USAGE Function

Reports the recorded column (group) usage information

REPORT_GATHER_AUTO_STATS Function

Runs the auto statistics gathering job in reporting mode

REPORT_GATHER_DATABASE_STATS Functions

Runs the GATHER_DATABASE_STATS Procedures in reporting mode.

REPORT_GATHER_DICTIONARY_STATS Functions

Runs the GATHER_DICTIONARY_STATS Procedure in reporting mode

REPORT_GATHER_FIXED_OBJ_STATS Function

Runs the GATHER_FIXED_OBJECTS_STATS Procedure in reporting mode

REPORT_GATHER_SCHEMA_STATS Functions

Runs the GATHER_SCHEMA_STATS Procedures in reporting mode

REPORT_GATHER_TABLE_STATS Function

Runs the GATHER_TABLE_STATS Procedure in reporting mode

REPORT_STATS_OPERATIONS Function

Generates a report of all statistics operations that take place between two timestamps which may or may not have been provided

RESET_ADVISOR_TASK Procedure

Resets an Optimizer Statistics Advisor task execution to its initial state. Only reset a task that is not currently executing

RESET_COL_USAGE Procedure

Resets the recorded column (group) usage information

RESET_GLOBAL_PREF_DEFAULTS Procedure

Resets the default values of all parameters to Oracle recommended values

RESET_PARAM_DEFAULTS Procedure

Resets global preferences to default values (see DBMS_STATS Deprecated Subprograms)

RESTORE_DICTIONARY_STATS Procedure

Restores statistics of all dictionary tables (tables of 'SYS', 'SYSTEM' and RDBMS component schemas) as of a specified timestamp

RESTORE_FIXED_OBJECTS_STATS Procedure

Restores statistics of all fixed tables as of a specified timestamp

RESTORE_SCHEMA_STATS Procedure

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

RESTORE_SYSTEM_STATS Procedure

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

RESTORE_TABLE_STATS Procedure

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

RESUME_ADVISOR_TASK Procedure

Resumes an interrupted task. It only resumes the execution that was most recently interrupted

SCRIPT_ADVISOR_TASK Function

Retrieves the script that implements the recommended actions for the problems found by Optimizer Statistics Advisor

SEED_COL_USAGE Procedure

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

SET_ADVISOR_TASK_PARAMETER Procedure

Updates the value of an Optimizer Statistics Advisor task parameter

SET_COLUMN_STATS Procedures

Sets column-related information

SET_DATABASE_PREFS Procedure

Sets the statistics preferences of all the tables

SET_GLOBAL_PREFS Procedure

Sets the global statistics preferences

SET_INDEX_STATS Procedures

Sets index-related information

SET_PARAM Procedure

Sets default values for parameters of DBMS_STATS procedures (see DBMS_STATS Deprecated Subprograms)

SET_PROCESSING_RATE Procedure

Sets the value of rate of processing for a given operation

SET_SCHEMA_PREFS Procedure

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

SET_SYSTEM_STATS Procedure

Sets system statistics

SET_TABLE_PREFS Procedure

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

SET_TABLE_STATS Procedure

Sets table-related information

SHOW_EXTENDED_STATS_NAME Function

Returns the name of the virtual column that is created for the user-specified extension

TRANSFER_STATS Procedure

Transfers statistics for specified table(s) from a remote database specified by dblink to the local database

UNLOCK_PARTITION_STATS Procedure

Unlocks the statistics for a partition

UNLOCK_SCHEMA_STATS Procedure

Unlocks the statistics on all the tables in schema

UNLOCK_TABLE_STATS Procedure

Unlocks the statistics on the table

UPGRADE_STAT_TABLE Procedure

Upgrades user statistics on an older table

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

Parameter Description

retention

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

  • -1: Statistics history is never purged by automatic purge

  • 0: Old statistics are never saved. The automatic purge will delete all statistics history

  • NULL: Change statistics history retention to default value

Usage Notes

To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

Exceptions

ORA-20000: Insufficient privileges

162.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 162-5 CANCEL_ADVISOR_TASK Procedure Parameters

Parameter Description

task_name

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 162-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

162.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 162-6 CONFIGURE_ADVISOR_FILTER Function Parameters

Parameter Description

task_name

The name of the Optimizer Statistics Advisor task.

stats_adv_opr_type

The type of operation to configure. Possible values are EXECUTE, REPORT, SCRIPT, and IMPLEMENT. The function permits you to specify a combination of operation types by using the plus (+) operator, for example, EXECUTE +REPORT. If this parameter is null, then the filter applies to all types of advisor operations.

configuration_type

The type of configuration. Possible values are as follows:

  • SET: Sets the specified filter list values. The submitted filter overrides existing filter values.

  • CLEAR: Clears the existing values for the specified filter.

  • SHOW: Shows the current values of the specified filter.

filter

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, use DBMS_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 162-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 162-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 162-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

162.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 162-7 CONFIGURE_ADVISOR_OBJ_FILTER Function Parameters

Parameter Description

task_name

The name of the Optimizer Statistics Advisor task.

stats_adv_opr_type

The type of operation to configure. Possible values are EXECUTE, REPORT, SCRIPT, and IMPLEMENT. See"CONFIGURE_ADVISOR_RULE_FILTER Function".

rule_name

The name of the rule to configure. If null, the function applies the filter to all operation-level rules.

ownname

The owner name of the operation target. If null, the function applies the filter to all owner names.

tabname

The table name of the operation target.

action

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

162.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 162-8 CONFIGURE_ADVISOR_OPR_FILTER Function Parameters

Parameter Description

task_name

The name of the Optimizer Statistics Advisor task.

stats_adv_opr_type

The type of operation to configure. Possible values are EXECUTE, REPORT, SCRIPT, and IMPLEMENT. See"CONFIGURE_ADVISOR_RULE_FILTER Function".

rule_name

The name of the rule to configure. If null, the function applies the filter to all operation-level rules.

operation_name

The name of the operation. For example, an operation name could be gather_table_stats. This value cannot be null.

operation_id

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 DBA_OPSTAT_OPERATIONS.ID. This value cannot be null.

ownname

The owner name of the operation target. This value cannot be null.

tabname

The table name of the operation target.

action

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 162-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:

  1. Log in to the database as stats.

  2. 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;
    /
    
  3. Create a procedure named opr_filter that configures a task to advise on all operations except those that gather statistics for tables in the hr 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
    
  4. Create a task named opt_adv_task1, and then execute the opr_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;
    /
  5. 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;
    /
  6. 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:

162.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 162-9 SCRIPT_ADVISOR_TASK Function Parameters

Parameter Description

task_name

The name of the Optimizer Statistics Advisor task.

stats_adv_opr_type

The type of operation to configure. Possible values are EXECUTE, REPORT, SCRIPT, and IMPLEMENT. You can specify a combination of operation types, for example, EXECUTE +REPORT. If this parameter is null, then the filter applies to all types of Optimizer Statistics Advisor operations.

rule_name

The name of the rule to configure. If null, the function applies the filter to all rules.

action

The configuration action to take for the specified rule. Possible values are:

  • ENABLE: Enables the filter

  • DISABLE: Disables the filter

  • DELETE: Deletes the filter

  • SHOW: Shows the current filter value

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

162.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 162-10 CONVERT_RAW_VALUE Procedure Parameters

Parameter Description

rawval

Raw representation of a column minimum, maximum, histogram end point actual value

resval

Converted, type-specific value

Usage Notes

No special privilege or role is needed to invoke this procedure.

162.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 162-11 CONVERT_RAW_VALUE_NVARCHAR Procedure Parameters

Parameter Description

rawval

The raw representation of a column minimum or maximum datatype-specific output parameters

resval

The converted, type-specific value

Usage Notes

No special privilege or role is needed to invoke this procedure.

162.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 162-12 CONVERT_RAW_VALUE_ROWID Procedure Parameters

Parameter Description

rawval

The raw representation of a column minimum or maximum datatype-specific output parameters

resval

The converted, type-specific value

Usage Notes

No special privilege or role is needed to invoke this procedure.

162.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 NULL,
   force            BOOLEAN DEFAULT FALSE);

Parameters

Table 162-13 COPY_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Schema of the table of source and destination [sub] partitions

tabname

Table name of source and destination [sub] partitions

srcpartname

Source [sub] partition

dtspartname

Destination [sub] partition

scale_factor

Scale factor to scale nblks, nrows etc. in dstpartname

flags

For internal Oracle use (should be left as NULL)

force

When value of this argument is TRUE copy statistics even if the destination [sub]partition is locked

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 is D-1 and the key column to be adjusted is Cn, the maximum value for Cn is set to the upper bound of D (ignoring the maximum value of the source partition column) provided that the upper bounds of the previous key column Cn-1 are the same in partitions D and D-1.

  • If the minimum and maximum values are different for a column after modifications, and if the number of distinct values is less than 1, 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.

162.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 162-14 CREATE_ADVISOR_TASK Function Parameters

Parameter Description

task_name

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 162-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 Advisor

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

The database gathers statistics for this extension when a user-initiated or automatic statistics gathering job gathers statistics for the table. Statistics for such an extension are called extended statistics. This function returns the name of this newly created entry for the extension.

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 162-15 CREATE_EXTENDED_STATS Function Parameters

Parameter Description

ownname

Owner name of a table

tabname

Name of the table

extension

Can be either a column group or an expression. Suppose the specified table has two column c1, c2. An example column group is "(c1, c2)". An example expression is "(c1 + c2)".

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 you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.

There are nine restrictions on the extension:

  • The extension cannot contain a virtual column.

  • Extensions cannot be created on tables owned by SYS.

  • Extensions cannot be created on cluster tables, index organized tables, temporary tables or external tables.

  • The total number of extensions in a table cannot be greater than a maximum of (20, 10% of number of non-virtual columns in the table).

  • The number of columns in a column group must be in the range [2, 32].

  • A column can not appear more than once in a column group.

  • A column group can not contain expressions.

  • An expression must contain at least one column.

  • An expression can not contain a subquery.

  • The COMPATIBLE parameter must be 11.0.0.0.0 or greater.

162.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 162-16 CREATE_STAT_TABLE Procedure Parameters

Parameter Description

ownname

Name of the schema

stattab

Name of the table to create. This value should be passed as the stattab parameter to other procedures when the user does not want to modify the dictionary statistics directly.

tblspace

Tablespace in which to create the statistics tables. If none is specified, then they are created in the user's default tablespace.

global_temporary

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

162.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 162-17 DELETE_COLUMN_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table to which this column belongs

colname

Name of the column or extension

partname

Name of the table partition for which to delete the statistics. If the table is partitioned and if partname is NULL, then global column statistics are deleted.

stattab

User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly from the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

cascade_parts

If the table is partitioned and if partname is NULL, then setting this to true causes the deletion of statistics for this column for all underlying partitions as well.

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

When value of this argument is TRUE, deletes column statistics even if locked

col_stat_type

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

  • HISTOGRAM - delete column histogram only

  • ALL - delete base column statistics and histogram

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.

162.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 162-18 DELETE_DATABASE_PREFS Procedure Parameters

Parameter Description

pname

Preference name. The existing value for following preferences can be deleted and default preference values will be used:

  • APPROXIMATE_NDV_ALGORITHM

  • AUTO_STAT_EXTENSIONS

  • CASCADE

  • DEGREE

  • ESTIMATE_PERCENT

  • GLOBAL_TEMP_TABLE_STATS

  • GRANULARITY

  • INCREMENTAL

  • INCREMENTAL_LEVEL

  • INCREMENTAL_STALENESS

  • METHOD_OPT

  • NO_INVALIDATE

  • OPTIONS

  • PREFERENCE_OVERRIDES_PARAMETER

  • PUBLISH

  • STALE_PERCENT

  • STAT_CATEGORY

  • TABLE_CACHED_BLOCKS

add_sys

Determines whether SYS tables will be included.

Table 162-19 Statistics Preferences

Preference Description

APPROXIMATE_NDV_ALGORITHM

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 delete the following preferences:

  • REPEAT OR HYPERLOGLOG

    This is the default. If INCREMENTAL is enabled on the table, then the database preserves the format of any existing synopses that use the adaptive sampling algorithm. However, the database creates any new synopses in HyperLogLog format. This approach is attractive when existing performance is acceptable, and you do not want to incur the performance cost of reformatting legacy content.

  • ADAPTIVE SAMPLING

    The database uses the adaptive sampling algorithm for all synopses. This is the most conservative option.

  • HYPERLOGLOG

    The database uses the HyperLogLog algorithm for all new and stale synopses. In contrast to dynamic sampling, the HyperLogLog algorithm uses a randomization technique. The advantages of HyperLogLog over adaptive sampling are:

    • The accuracy of the new algorithm is similar to the original algorithm.

    • The memory required is significantly lower, which typically leads to huge reductions in synopsis size.

AUTO_STAT_EXTENSIONS

Controls the automatic creation of extensions when database statistics are gathered.

You can set the following values:

  • ON — When applicable, a SQL plan directive can trigger the creation of column group statistics based on usage of columns in the predicates in the workload.

  • OFF— The database does not create column group statistics automatically. The database creates them only when the CREATE_EXTENDED_STATS function is executed, or when extended statistics are specified explicitly in the METHOD_OPT clause of DBMS_STATS. This is the default.

CASCADE

Determines whether index statistics are collected as part of gathering table statistics.

DEGREE

Determines degree of parallelism used for gathering statistics.

ESTIMATE_PERCENT

Determines the percentage of rows to estimate.

GLOBAL_TEMP_TABLE_STATS

Controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics.

GRANULARITY

Determines the granularity of statistics to collect. This value is only relevant for partitioned tables.

INCREMENTAL

Determines whether the global statistics of a partitioned table will be maintained without doing a full table scan.

INCREMENTAL_LEVEL

Controls which synopses to collect when INCREMENTAL preference is set to TRUE.

INCREMENTAL_STALENESS

Specifies when a partition or subpartition is considered stale. This parameter takes an enumeration of values, such as 'USE_STALE_PERCENT' and 'USE_LOCKED_STATS'. You can also specify multiple values, such as 'USE_STALE_PERCENT, USE_LOCKED_STATS, ALLOW_MIXED_FORMAT'.

The parameter accepts the following values:

  • USE_STALE_PERCENT—A partition or subpartition is not considered stale when DML changes are below the threshold set by the STALE_PERCENT preference.

    For example, assume that STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. The partition has 5% DML changes. The database does not regather statistics.

    Assume a different case in which STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. However, in this case the partition is locked and has 20% of DML changes. Because the partition is locked, the database does not regather statistics.

  • USE_LOCKED_STATS—Locked partitions or subpartitions statistics are never considered stale, regardless of DML changes.

    For example, assume that STALE_PERCENT is 10. You specify 'USE_LOCKED_STATS, USE_STALE_PERCENT'. The partition, which is locked, has 20% DML changes. The partition is not considered stale. The database uses existing statistics to derive global statistics.

  • ALLOW_MIXED_FORMAT—Adaptive sampling synopses and HyperLogLog synopses are permitted to coexist.

  • NULL—A partition or subpartition is considered stale when it has any DML changes.

    For example, assume that STALE_PERCENT is 10. You specify the value 'NULL' for INCREMENTAL_STALENESS. The partition has 5% of DML changes. The database regathers statistics.

Note that the following two executions are different:

EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', 'NULL');
EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', null);

The first execution uses single quotes to set the preference to the value NULL, whereas the second sets the preference to the default, which is ALLOW_MIXED_FORMAT.

METHOD_OPT

Controls column statistics collection and histogram creation. When setting preference on global, schema, database or dictionary level, only'FOR ALL' syntax is allowed.

NO_INVALIDATE

Controls the invalidation of dependent cursors of the tables for which statistics are being gathered.

OPTIONS

Determines the options parameter used in the GATHER_TABLE_STATS Procedure.

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: 

  • TRUE — Ignores input parameter values, and uses the value of the corresponding preference.

  • FALSE — Obeys input parameter values.

Specifying this preference does not change the order of precedence of table, global, and default.

PUBLISH

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.

STALE_PERCENT

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.

STAT_CATEGORY

Specifies which statistics to import or export, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (default)

  • SYNOPSES - information to support incremental statistics

The value 'OBJECT_STATS, SYNOPSES' specifies table statistics, column statistics, index statistics, and synopses.

TABLE_CACHED_BLOCKS

Specifies the average number of blocks assumed to be cached in the buffer cache when calculating the index clustering factor.

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 162-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

162.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 162-20 DELETE_DATABASE_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)

statown

Schema containing stattab (if different from current schema)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

When the value of this argument is TRUE, deletes statistics of tables in a database even if they are locked

stat_category

Statistics to delete. It accepts multiple values separated by comma:

  • OBJECT_STATS - table statistics, column statistics and index statistics

  • SYNOPSES - information to support incremental statistics

The default is 'OBJECT_STATS, SYNOPSES'

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.

162.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 162-21 DELETE_DICTIONARY_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)

statown

Schema containing stattab (if different from current schema)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure

stattype

Statistics type

force

When the value of this argument is TRUE, deletes statistics of tables in a database even if they are locked

stat_category

Statistics to delete. It accepts multiple values separated by comma:

  • OBJECT_STATS - table statistics, column statistics and index statistics

  • SYNOPSES - information to support incremental statistics

The default is 'OBJECT_STATS, SYNOPSES'

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

162.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 162-22 DELETE_FIXED_OBJECTS_STATS Procedure Parameters

Parameter Description

stattab

The user statistics table identifier describing from where to delete the current statistics. If stattab is NULL, the statistics will be deleted directly in the dictionary.

statid

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

statown

Schema containing stattab (if different from current schema)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure

force

Ignores the statistics lock on objects and deletes the statistics if set to TRUE

Usage Notes

You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.

Exceptions

ORA-20000: Insufficient privileges

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

162.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 162-23 DELETE_INDEX_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

indname

Name of the index

partname

Name of the index partition for which to delete the statistics. If the index is partitioned and if partname is NULL, then index statistics are deleted at the global level.

stattab

User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly from the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)

cascade_parts

If the index is partitioned and if partname is NULL, then setting this to TRUE causes the deletion of statistics for this index for all underlying partitions as well

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

stattype

Statistics type

force

When value of this argument is TRUE, deletes index statistics even if locked

stat_category

Statistics to delete. It accepts multiple values separated by comma:

  • OBJECT_STATS - table statistics, column statistics and index statistics

  • SYNOPSES - information to support incremental statistics

The default is 'OBJECT_STATS, SYNOPSES'

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.

162.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 162-24 DELETE_PENDING_STATS Procedure Parameters

Parameter Description

ownname

Owner name

tabname

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

162.7.21 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 162-25 DELETE_PROCESSING_RATE Procedure Parameters

Parameter Description

stat_source

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

162.7.22 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 162-26 DELETE_SCHEMA_PREFS Procedure Parameters

Parameter Description

ownname

Owner name

pname

Preference name. The existing value for following preferences can be deleted and default preference values will be used:

  • APPROXIMATE_NDV_ALGORITHM

  • AUTO_STAT_EXTENSIONS

  • CASCADE

  • DEGREE

  • ESTIMATE_PERCENT

  • GLOBAL_TEMP_TABLE_STATS

  • GRANULARITY

  • INCREMENTAL

  • INCREMENTAL_LEVEL

  • INCREMENTAL_STALENESS

  • METHOD_OPT

  • NO_INVALIDATE

  • OPTIONS

  • PREFERENCE_OVERRIDES_PARAMETER

  • PUBLISH

  • STALE_PERCENT

  • TABLE_CACHED_BLOCKS

Table 162-27 Statistics Preferences

Preference Description

APPROXIMATE_NDV_ALGORITHM

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:

  • REPEAT OR HYPERLOGLOG

    This is the default. If INCREMENTAL is enabled on the table, then the database preserves the format of any existing synopses that use the adaptive sampling algorithm. However, the database creates any new synopses in HyperLogLog format.

  • ADAPTIVE SAMPLING

    The database uses the adaptive sampling algorithm for all synopses.

  • HYPERLOGLOG

    The database uses the HyperLogLog algorithm for all new and stale synopses.

AUTO_STAT_EXTENSIONS

Controls the automatic creation of extensions when database statistics are gathered.

You can set the following values:

  • ON — When applicable, a SQL plan directive can trigger the creation of column group statistics based on usage of columns in the predicates in the workload.

  • OFF— The database does not create column group statistics automatically. The database creates them only when the CREATE_EXTENDED_STATS function is executed, or when extended statistics are specified explicitly in the METHOD_OPT clause of DBMS_STATS. This is the default.

CASCADE

Determines whether index statistics are collected as part of gathering table statistics.

COORDINATOR_TRIGGER_SHARD

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:

  • TRUE—Allows the shard coordinator trigger the statistics gathering on sharded table in local shard if the statistics on local shard are stale.

  • FALSE—Ignores the statistics gathering command triggered from the shard coordinator.

The default value is FALSE.

DEGREE

Determines the degree of parallelism used for gathering statistics.

ESTIMATE_PERCENT

The value determines the percentage of rows to estimate.

METHOD_OPT

Controls column statistics collection and histogram creation. When setting preferences at the global, schema, database, or dictionary level, onlyFOR ALL syntax is allowed.

NO_INVALIDATE

The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered.

GRANULARITY

The value determines granularity of statistics to collect (only pertinent if the table is partitioned)

PUBLISH

This value determines whether or not newly gathered statistics will be published once the gather job has completed.

INCREMENTAL

This value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan.

INCREMENTAL_LEVEL

This value controls what synopses to collect when INCREMENTAL preference is set to TRUE.

INCREMENTAL_STALENESS

Specifies when a partition or subpartition is considered stale. This parameter takes an enumeration of values, such as 'USE_STALE_PERCENT' and 'USE_LOCKED_STATS'. You can also specify multiple values, such as 'USE_STALE_PERCENT, USE_LOCKED_STATS, ALLOW_MIXED_FORMAT'.

The parameter accepts the following values:

  • USE_STALE_PERCENT—A partition or subpartition is not considered stale when DML changes are below the threshold set by the STALE_PERCENT preference.

    For example, assume that STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. The partition has 5% DML changes. The database does not regather statistics.

    Assume a different case in which STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. However, in this case the partition is locked and has 20% of DML changes. Because the partition is locked, the database does not regather statistics.

  • USE_LOCKED_STATS—Locked partitions or subpartitions statistics are never considered stale, regardless of DML changes.

    For example, assume that STALE_PERCENT is 10. You specify 'USE_LOCKED_STATS, USE_STALE_PERCENT'. The partition, which is locked, has 20% DML changes. The partition is not considered stale. The database uses existing statistics to derive global statistics.

  • ALLOW_MIXED_FORMAT—Adaptive sampling synopses and HyperLogLog synopses are permitted to coexist.

  • NULL—A partition or subpartition is considered stale when it has any DML changes.

    For example, assume that STALE_PERCENT is 10. You specify the value 'NULL' for INCREMENTAL_STALENESS. The partition has 5% of DML changes. The database regathers statistics.

Note that the following two executions are different:

EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', 'NULL');
EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', null);

The first execution uses single quotes to set the preference to the value NULL, whereas the second sets the preference to the default, which is ALLOW_MIXED_FORMAT.

ROOT_TRIGGER_PDB

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:
  • TRUE—Allows the application root trigger the statistics gathering on metadata linked table in application PDB if the statistics on PDB are stale.

  • FALSE—Ignores the statistics gathering command triggered from application root.

The default value is FALSE.

Note:

CDB root, different from application root, never triggers statistics gathering on the PDBs and it is not controlled by this preference.

STALE_PERCENT

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.

GLOBAL_TEMP_TABLE_STATS

This controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics.

TABLE_CACHED_BLOCKS

The average number of blocks cached in the buffer cache for any table we can assume when gathering the index clustering factor.

OPTIONS

Determines the options parameter used in the GATHER_TABLE_STATS Procedure.

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

162.7.23 DELETE_SCHEMA_STATS Procedure

This procedure deletes statistics for an entire schema.

Syntax

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

Parameters

Table 162-28 DELETE_SCHEMA_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

stattab

User statistics table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

When value of this argument is TRUE, deletes statistics of tables in a schema even if locked

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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.

162.7.24 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 162-29 DELETE_SYSTEM_STATS Procedure Parameters

Parameter Description

stattab

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

statid

Optional identifier associated with the statistics saved in the stattab

statown

Schema containing stattab (if different from current schema)

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.

162.7.25 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 162-30 DELETE_TABLE_PREFS Procedure Parameters

Parameter Description

ownname

Owner name

tabname

Table name

pname

Preference name. The existing value for following preferences can be deleted and default preference values will be used:

  • APPROXIMATE_NDV_ALGORITHM

  • AUTO_STAT_EXTENSIONS

  • CASCADE

  • DEGREE

  • ESTIMATE_PERCENT

  • GRANULARITY

  • GLOBAL_TEMP_STATS

  • INCREMENTAL

  • INCREMENTAL_LEVEL

  • INCREMENTAL_STALENESS

  • METHOD_OPT

  • NO_INVALIDATE

  • OPTIONS

  • PREFERENCE_OVERRIDES_PARAMETER

  • PUBLISH

  • STALE_PERCENT

  • STAT_CATEGORY

  • TABLE_CACHED_BLOCKS

Table 162-31 Statistics Preferences

Preference Description

APPROXIMATE_NDV_ALGORITHM

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 delete the following preferences:

  • REPEAT OR HYPERLOGLOG

    This is the default. If INCREMENTAL is enabled on the table, then the database preserves the format of any existing synopses that use the adaptive sampling algorithm. However, the database creates any new synopses in HyperLogLog format. This approach is attractive when existing performance is acceptable, and you do not want to incur the performance cost of reformatting legacy content.

  • ADAPTIVE SAMPLING

    The database uses the adaptive sampling algorithm for all synopses. This is the most conservative option.

  • HYPERLOGLOG

    The database uses the HyperLogLog algorithm for all new and stale synopses. In contrast to dynamic sampling, the HyperLogLog algorithm uses a randomization technique. The advantages of HyperLogLog over adaptive sampling are:

    • The accuracy of the new algorithm is similar to the original algorithm.

    • The memory required is significantly lower, which typically leads to huge reductions in synopsis size.

AUTO_STAT_EXTENSIONS

Controls the automatic creation of extensions when database statistics are gathered.

You can set the following values:

  • ON — When applicable, a SQL plan directive can trigger the creation of column group statistics based on usage of columns in the predicates in the workload.

  • OFF— The database does not create column group statistics automatically. The database creates them only when the CREATE_EXTENDED_STATS function is executed, or when extended statistics are specified explicitly in the METHOD_OPT clause of DBMS_STATS. This is the default.

CASCADE

Determines whether index statistics are collected as part of gathering table statistics.

DEGREE

Determines the degree of parallelism used for gathering statistics.

ESTIMATE_PERCENT

Determines the percentage of rows to estimate.

GRANULARITY

Determines granularity of statistics to collect. This value is only relevant for partitioned tables.

GLOBAL_TEMP_TABLE_STATS

Controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics.

INCREMENTAL

This value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan.

INCREMENTAL_LEVEL

This value controls what synopses to collect when INCREMENTAL preference is set to TRUE.

INCREMENTAL_STALENESS

Specifies when a partition or subpartition is considered stale. This parameter takes an enumeration of values, such as 'USE_STALE_PERCENT' and 'USE_LOCKED_STATS'. You can also specify multiple values, such as 'USE_STALE_PERCENT, USE_LOCKED_STATS, ALLOW_MIXED_FORMAT'.

The parameter accepts the following values:

  • USE_STALE_PERCENT—A partition or subpartition is not considered stale when DML changes are below the threshold set by the STALE_PERCENT preference.

    For example, assume that STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. The partition has 5% DML changes. The database does not regather statistics.

    Assume a different case in which STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. However, in this case the partition is locked and has 20% of DML changes. Because the partition is locked, the database does not regather statistics.

  • USE_LOCKED_STATS—Locked partitions or subpartitions statistics are never considered stale, regardless of DML changes.

    For example, assume that STALE_PERCENT is 10. You specify 'USE_LOCKED_STATS, USE_STALE_PERCENT'. The partition, which is locked, has 20% DML changes. The partition is not considered stale. The database uses existing statistics to derive global statistics.

  • ALLOW_MIXED_FORMAT—Adaptive sampling synopses and HyperLogLog synopses are permitted to coexist.

  • NULL—A partition or subpartition is considered stale when it has any DML changes.

    For example, assume that STALE_PERCENT is 10. You specify the value 'NULL' for INCREMENTAL_STALENESS. The partition has 5% of DML changes. The database regathers statistics.

Note that the following two executions are different:

EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', 'NULL');
EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', null);

The first execution uses single quotes to set the preference to the value NULL, whereas the second sets the preference to the default, which is ALLOW_MIXED_FORMAT.

METHOD_OPT

Controls column statistics collection and histogram creation. When setting preference at the global, schema, database, or dictionary level, only'FOR ALL' syntax is allowed.

NO_INVALIDATE

The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered.

OPTIONS

Determines the options parameter used in the GATHER_TABLE_STATS Procedure.

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: 

  • TRUE — Ignores input parameter values, and uses the value of the corresponding preference.

  • FALSE — Obeys input parameter values.

Specifying this preference does not change the order of precedence of table, global, and default.

PUBLISH

Determines whether newly gathered statistics will be published after the statistics gathering job has completed.

STALE_PERCENT

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.

STAT_CATEGORY

Specifies which statistics to import or export, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (default)

  • SYNOPSES - information to support incremental statistics

The value 'OBJECT_STATS, SYNOPSES' specifies table statistics, column statistics, index statistics, and synopses.

TABLE_CACHED_BLOCKS

Specifies the average number of blocks assumed to be in the buffer cache when calculating the index clustering factor.

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, or ANALYZE ANY system privilege.

  • All arguments are of type VARCHAR2 and values are enclosed in quotes, even when they represent numbers.

Examples

DBMS_STATS.DELETE_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

162.7.26 DELETE_TABLE_STATS Procedure

This procedure deletes table-related statistics.

Syntax

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

Parameters

Table 162-32 DELETE_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table to which this column belongs

partname

Name of the table [sub]partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.

stattab

User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)

cascade_parts

If the table is partitioned and if partname is NULL, then setting this to TRUE causes the deletion of statistics for this table for all underlying partitions as well

cascade_columns

Indicates that DELETE_COLUMN_STATS should be called for all underlying columns (passing the cascade_parts parameter)

cascade_indexes

Indicates that DELETE_INDEX_STATS should be called for all underlying indexes (passing the cascade_parts parameter)

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

When value of this argument is TRUE, deletes table statistics even if locked

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

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.

162.7.27 DIFF_TABLE_STATS_IN_HISTORY Function

This function can be used to compare statistics for a table from two timestamps in past and compare the statistics as of that 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 162-33 DIFF_TABLE_STATS_IN_HISTORY Function Parameters

Parameter Description

ownname

Owner of the table. Specify NULL for current schema.

tabname

Table for which statistics are to be compared

time1

First timestamp 1

time2

Second timestamp 2

pctthreshold

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

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.

Usage Notes

If the second timestamp is NULL, then the function compares the current statistics in dictionary with the statistics as of the other timestamp.

162.7.28 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 162-34 DIFF_TABLE_STATS_IN_PENDING Function Parameters

Parameter Description

ownname

Owner of the table. Specify NULL for the current schema.

tabname

Table for which statistics are to be compared.

timestamp

Timestamp in the statistics history that corresponds to the desired statistics. If the timestamp is NULL, then this function compares the current statistics in the dictionary with the pending statistics (default).

pctthreshold

Limit for reporting. The function reports difference in statistics only if it exceeds the specified limit. The default value is 10.

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.

162.7.29 DIFF_TABLE_STATS_IN_STATTAB Function

This function can be used to compare statistics for a table from two different sources.

The statistics can be drawn from

  • two different user statistics tables

  • a single user statistics table containing 2 sets of statistics that can be identified using statids

  • a user statistics table and dictionary

The function also compares the statistics of the dependent objects (indexes, columns, partitions) as well. It displays statistics of the object(s) from both sources if the difference between those statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the function. The statistics corresponding to the first source (stattab1 or time1) will be used as basis for computing the difference percentage.

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 162-35 DIFF_TABLE_STATS_IN_STATTAB Function Parameters

Parameter Description

ownname

Owner of the table. Specify NULL for current schema.

tabname

Table for which statistics are to be compared

stattab1

User statistics table 1

stattab2

User statistics table 2. If NULL, statistics in stattab1 is compared with current statistics in dictionary. This is the default. Specify same table as stattab1 to compare two sets within the statistics table (see statid below).

pctthreshold

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

stadid1

(optional) Identifies statistics set within stattab1.

stadid2

(optional) Identifies statistics set within stattab2

stattab1own

Schema containing stattab1 (if other than ownname)

stattab2own

Schema containing stattab2 (if other than ownname)

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.

162.7.30 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 162-36 DROP_ADVISOR_TASK Procedure Parameters

Parameter Description

task_name

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 162-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

162.7.31 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 162-37 DROP_EXTENDED_STATS Procedure Parameters

Parameter Description

ownname

Owner name of a table

tabname

Name of the table

extension

Can be either a column group or an expression. Suppose the specified table has two column c1, c2. An example column group can be "(c1, c2)" and an example expression can be "(c1 + c2)".

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 by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.

  • If no extended statistics set is created for the extension, this function throws an error.

162.7.32 DROP_STAT_TABLE Procedure

This procedure drops a user statistics table.

Syntax

DBMS_STATS.DROP_STAT_TABLE (
   ownname VARCHAR2, 
   stattab VARCHAR2);

Parameters

Table 162-38 DROP_STAT_TABLE Procedure Parameters

Parameter Description

ownname

Name of the schema

stattab

User statistics table identifier

Exceptions

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

Usage Notes

To invoke this procedure you need the privileges for dropping the specified table.

162.7.33 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 162-39 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 level

    Only users with both the ANALYZE ANY and ANALYZE 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 and ANALYZE ANY DICTIONARY privileges can perform this task for all statistics operations.

    • Users with the ANALYZE ANY privilege but not the ANALYZE ANY DICTIONARY privilege can perform this task for statistics operations related to any schema except SYS.

    • Users with the ANALYZE ANY DICTIONARY privilege but not the ANALYZE ANY privilege can perform this task for statistics operations related to their own schema and the SYS schema.

    • Users with neither the ANALYZE ANY nor the ANALYZE 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 and ANALYZE ANY DICTIONARY privileges.

  • Operation

    If you have the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges, then you can execute this function for all operations. If you have only the ANALYZE ANY privilege, then you can execute this function for operations related to any schemas except SYS. If you have only the ANALYZE ANY DICTIONARY privilege, then you can execute this function for operations related to any schemas, including SYS. If you have neither the ANALYZE ANY nor the ANALYZE 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 162-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

162.7.34 EXPORT_COLUMN_STATS Procedure

This procedure retrieves statistics for a particular column and stores them in the user statistics table identified by stattab.

Syntax

DBMS_STATS.EXPORT_COLUMN_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   colname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 162-40 EXPORT_COLUMN_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table to which this column belongs

colname

Name of the column or extension

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition column statistics are exported.

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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.

  • Oracle does not support export or import of statistics across databases of different character sets.

162.7.35 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 162-41 EXPORT_DATABASE_PREFS Procedure Parameters

Parameter Description

stattab

Statistics table name to where statistics should be exported

statid

(Optional) Identifier to associate with these statistics within stattab

statown

Schema containing stattab (if other than ownname)

add_sys

Value TRUE will include the Oracle-owned tables

Exceptions

ORA-20000: 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.

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

162.7.36 EXPORT_DATABASE_STATS Procedure

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

Syntax

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

Parameters

Table 162-42 EXPORT_DATABASE_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different from current schema)

stat_category

Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (Default)

  • SYNOPSES - information to support incremental statistics

If 'OBJECT_STATS, SYNOPSES' is specified, table statistics, column statistics, index statistics and synopses are deleted.

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 by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.

  • Oracle does not support export or import of statistics across databases of different character sets.

162.7.37 EXPORT_DICTIONARY_STATS Procedure

This procedure retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) and stores them in the user statistics table identified by stattab.

Syntax

DBMS_STATS.EXPORT_DICTIONARY_STATS (
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   statown         VARCHAR2 DEFAULT NULL,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Parameters

Table 162-43 EXPORT_DICTIONARY_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different from current schema)

stat_category

Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (Default)

  • SYNOPSES - information to support incremental statistics

If 'OBJECT_STATS, SYNOPSES' is specified, table statistics, column statistics, index statistics and synopses are deleted.

Usage Notes

  • You must have the SYSDBA or ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.

  • Oracle does not support export or import of statistics across databases of different character sets.

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

162.7.38 EXPORT_FIXED_OBJECTS_STATS Procedure

This procedure retrieves statistics for fixed tables and stores them in the user statistics table identified by stattab.

Syntax

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

Parameters

Table 162-44 EXPORT_FIXED_OBJECTS_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different from current schema)

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

Usage Notes

  • To invoke this subprogram you need to be connected as SYS or have the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege

  • Oracle does not support export or import of statistics across databases of different character sets.

162.7.39 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 162-45 EXPORT_INDEX_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

indname

Name of the index

partname

Name of the index partition. If the index is partitioned and if partname is NULL, then global and partition index statistics are exported.

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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.

  • Oracle does not support export or import of statistics across databases of different character sets.

162.7.40 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 162-46 EXPORT_PENDING_STATS Procedure Parameters

Parameter Description

ownname

Owner name

tabname

Table name

stattab

Statistics table name to where to export the statistics

statid

(Optional) Identifier to associate with these statistics within stattab

statown

Schema containing stattab (if other than ownname)

Exceptions

ORA-20000: Object does not exist or insufficient privileges

Usage Notes

  • If the parameter tabname is NULL then export applies to all tables of the specified schema.

  • The default owner/schema is the user who runs the procedure.

  • To run this procedure, you need to have the same privilege for gathering statistics on the tables that will be touched by this procedure.

  • All arguments are of type VARCHAR2 and values are enclosed in quotes.

  • Oracle does not support export or import of statistics across databases of different character sets.

Examples

DBMS_STATS.EXPORT_PENDING_STATS(NULL, NULL, 'MY_STAT_TABLE');

162.7.41 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 162-47 EXPORT_SCHEMA_PREFS Procedure Parameters

Parameter Description

ownname

Owner name

stattab

Statistics table name to where to export the statistics

statid

(Optional) Identifier to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

Exceptions

ORA-20000: Object does not exist or insufficient privileges

Usage Notes

  • To run this procedure, you need to connect as owner, or have the SYSDBA privilege, or have the ANALYZE ANY system privilege.

  • All arguments are of type VARCHAR2 and values are enclosed in quotes.

  • Oracle does not support export or import of statistics across databases of different character sets.

Examples

DBMS_STATS.EXPORT_SCHEMA_PREFS('SH', 'STAT');

162.7.42 EXPORT_SCHEMA_STATS Procedure

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

Syntax

DBMS_STATS.EXPORT_SCHEMA_STATS (
   ownname         VARCHAR2,
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   statown         VARCHAR2 DEFAULT NULL,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Parameters

Table 162-48 EXPORT_SCHEMA_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

stat_category

Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (Default)

  • SYNOPSES - information to support incremental statistics

If 'OBJECT_STATS, SYNOPSES' is specified, table statistics, column statistics, index statistics and synopses are deleted.

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 by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.

  • Oracle does not support export or import of statistics across databases of different character sets.

162.7.43 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 162-49 EXPORT_SYSTEM_STATS Procedure Parameters

Parameter Description

stattab

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

statid

Optional identifier associated with the statistics stored from the stattab

statown

Schema containing stattab (if different from current schema)

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.

162.7.44 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 162-50 EXPORT_TABLE_PREFS Procedure Parameters

Parameter Description

ownname

Owner name

tabname

Table name

stattab

Statistics table name where to export the statistics

statid

Optional identifier to associate with these statistics within stattab

statown

Schema containing stattab (if other than ownname)

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

162.7.45 EXPORT_TABLE_STATS Procedure

This procedure retrieves statistics for a particular table and stores them in the user statistics table. Cascade results in all index statistics associated with the specified table being exported as well.

Syntax

DBMS_STATS.EXPORT_TABLE_STATS (
   ownname         VARCHAR2, 
   tabname         VARCHAR2, 
   partname        VARCHAR2 DEFAULT NULL,
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   cascade         BOOLEAN  DEFAULT TRUE,
   statown         VARCHAR2 DEFAULT NULL,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Parameters

Table 162-51 EXPORT_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported.

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

cascade

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

statown

Schema containing stattab (if different than ownname)

stat_category

Specifies which statistics to import or export, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (default)

  • SYNOPSES - information to support incremental statistics

The value 'OBJECT_STATS, SYNOPSES' specifies table statistics, column statistics, index statistics, and synopses.

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 by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.

  • Oracle does not support export or import of statistics across databases of different character sets.

162.7.46 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_INFO to view the latest information in *_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

162.7.47 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 162-52 GATHER_DATABASE_STATS Procedure Parameters

Parameter Description

estimate_percent

Determines the percentage of rows to sample.

The valid range is between 0.000001 and 100. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to enable the database to determine the appropriate sample size for optimal statistics. This is the default.

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.

block_sample

Determines whether the database uses random block sampling (TRUE) or random row sampling (FALSE). The default is FALSE.

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.

method_opt

When setting preference on global, schema, database or dictionary level, only'FOR ALL' syntax is allowed.:

  • FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

  • - integer : Number of histogram buckets. Must be in the range [1,2048].
  • - REPEAT : Collects histograms only on the columns that already have histograms.
  • - AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
  • - SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

degree

Determines the degree of parallelism used for gathering statistics.

The default for degree is NULL. NULL means to use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Change the default using the SET_DATABASE_PREFS, SET_GLOBAL_PREFS, SET_SCHEMA_PREFS, and SET_TABLE_PREFS procedures. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement.

Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. The degree is between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters), according to the size of the object. When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution when the size of the object does not warrant parallel execution.

granularity

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

'ALL' - Gathers all (subpartition, partition, and global) statistics

'AUTO'- Determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - Gathers global statistics

'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- Gathers partition-level statistics

'SUBPARTITION' - Gathers subpartition-level statistics

cascade

Determines whether to collect index statistics as part of gathering table statistics.

Specifying this option is equivalent to running the GATHER_INDEX_STATS procedure on each index of the table. Use the constant DBMS_STATS.AUTO_CASCADE to enable the database to determine whether index statistics need to be collected. This is the default. You can change the default using the SET_DATABASE_PREFS, SET_GLOBAL_PREFS, SET_SCHEMA_PREFS, and SET_TABLE_PREFS procedures.

stattab

User statistics table identifier describing where to save the current statistics.

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

statid

Identifier (optional) to associate with these statistics within stattab.

options

Specifies which objects require statistics to be gathered. Valid values are as follows:

  • GATHER — Gathers statistics on all objects in the database. This is the default.

  • GATHER AUTO — Gathers all necessary statistics automatically.

    The database implicitly determines which objects need new statistics and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are comp_id, no_invalidate, stattab, statid, and statown; all other parameter settings will be ignored. Also, the database returns a list of objects processed.

  • GATHER STALE — Gathers statistics on stale objects by querying the *_TAB_MODIFICATIONS views. Also, the database returns a list of objects found to be stale.

  • GATHER EMPTY — Gathers statistics on objects that currently have no statistics. Also, the database returns a list of objects found to have no statistics.

  • LIST AUTO — Returns a list of objects to be processed with GATHER AUTO.

  • LIST STALE — Returns list of stale objects as determined by looking at the *_TAB_MODIFICATIONS views.

  • LIST EMPTY — Returns a list of objects that currently have no statistics.

objlist

List of objects found to be stale or empty

statown

Schema containing stattab (if different from current schema)

gather_sys

Gathers statistics on the objects owned by the SYS user.

no_invalidate

Controls the invalidation of dependent cursors of the tables for which statistics are being gathered. The default is DBMS_STATS.AUTO_INVALIDATE, which means the database decides when to invalidate dependent cursors.

If set to TRUE, then the database not invalidate dependent cursors. If set to FALSE, then the procedure invalidates dependent cursors immediately.

obj_filter_list

A list of object filters. When provided, GATHER_DATABASE_STATS 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-NULL values s1, s2, ... are specified for attributes a1, a2, ... in one object filter. An object o is said to satisfy this object filter if (o.a1 like s1) or (o.a2 like s2) or ... is true.

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 on gather_table_stats.

  • The FREQUENCY histograms on NEWTAB will be created using a sample rather than a full table scan.

162.7.48 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 162-53 GATHER_DICTIONARY_STATS Procedure Parameters

Parameter Description

comp_id

Component id of the schema to analyze. NULL results in the analysis of schemas for all RDBMS components. Refer to the COMP_ID column of DBA_REGISTRY view. The procedure always gather statistics on SYS and SYSTEM schemas regardless of this argument.

estimate_percent

Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

block_sample

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

method_opt

The method options. This parameter accepts the following values:

  • FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

  • - integer : Number of histogram buckets. Must be in the range [1,2048].
  • - REPEAT : Collects histograms only on the columns that already have histograms.
  • - AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
  • - SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to the size of the object. When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.

granularity

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

'ALL' - Gathers all (subpartition, partition, and global) statistics

'AUTO'- Determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - Gathers global statistics

'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- Gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics

cascade

Gathers statistics on indexes also. Index statistics gathering will not be parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

stattab

User statistics table identifier describing where to save the current statistics

statid

The (optional) identifier to associate with these statistics within stattab

options

Specifies which objects require statistics to be gathered. Valid values are as follows:

  • GATHER AUTO — Gathers all necessary statistics automatically. This is the default.

    The database implicitly determines which objects need new statistics and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are comp_id, no_invalidate, stattab, statid, and statown; all other parameter settings will be ignored. Also, the database returns a list of objects processed.

  • GATHER — Gathers statistics on all objects in the relevant schema.

  • GATHER STALE — Gathers statistics on stale objects by querying the *_TAB_MODIFICATIONS views. Also, the database returns a list of objects found to be stale.

  • GATHER EMPTY — Gathers statistics on objects that currently have no statistics. Also, the database returns a list of objects found to have no statistics.

  • LIST AUTO — Returns a list of objects to be processed with GATHER AUTO.

  • LIST STALE — Returns list of stale objects as determined by looking at the *_TAB_MODIFICATIONS views.

  • LIST EMPTY — Returns a list of objects that currently have no statistics.

objlist

The list of objects found to be stale or empty.

statown

Schema containing stattab, if different from the current schema.

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

obj_filter_list

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-NULL values s1, s2, ... are specified for attributes a1, a2, ... in one object filter. An object o is said to satisfy this object filter if (o.a1 like s1) and (o.a2 like s2) and ... is true. See Applying an Object Filter List.

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 on gather_table_stats.

  • The FREQUENCY histograms on NEWTAB 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

162.7.49 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 162-54 GATHER_FIXED_OBJECTS_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing where to save the current statistics

statid

Identifier to associate with these statistics within stattab (optional)

statown

Schema containing stattab (if different from current schema)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Usage Notes

You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.

Exceptions

ORA-20000: Insufficient privileges

ORA-20001: Bad input value

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

162.7.50 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 162-55 GATHER_INDEX_STATS Procedure Parameters

Parameter Description

ownname

Schema of index to analyze

indname

Name of index

partname

Name of partition

estimate_percent

Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

stattab

User statistics table identifier describing where to save the current statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to the size of the object. When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.

granularity

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

'ALL' - Gathers all (subpartition, partition, and global) statistics

'AUTO'- Determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - Gathers global statistics

'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- Gathers partition-level statistics

'SUBPARTITION' - Gathers subpartition-level statistics.

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

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.

162.7.51 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 162-56 GATHER_PROCESSING_RATE Procedure Parameters

Parameter Description

gathering_mode

Mode: 'START' or 'END'. The mode is based on the Active Session History (ASH) data when invoked with 'START' option. It stops gathering when invoked with 'END' option. When invoked with 'START', 'interval' option can be specified optionally. If interval is not specified, its default value is set to 60 minutes.

interval

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

162.7.52 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 162-57 GATHER_SCHEMA_STATS Procedure Parameters

Parameter Description

ownname

Schema to analyze (NULL means current schema)

estimate_percent

Determines the percentage of rows to sample.

The valid range is between 0.000001 and 100. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to enable the database to determine the appropriate sample size for optimal statistics. This is the default.

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.

block_sample

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

method_opt

Accepts:

  • FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

  • - integer : Number of histogram buckets. Must be in the range [1,2048].
  • - REPEAT : Collects histograms only on the columns that already have histograms
  • - AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
  • - SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to the size of the object. When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.

granularity

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

'ALL' - Gathers all (subpartition, partition, and global) statistics

'AUTO'- Determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - Gathers global statistics

'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- Gathers partition-level statistics

'SUBPARTITION' - Gathers subpartition-level statistics.

cascade

Gather statistics on the indexes as well. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

stattab

User statistics table identifier describing where to save the current statistics

statid

Identifier (optional) to associate with these statistics within stattab

options

Specifies which objects require statistics to be gathered. Valid values are as follows:

  • GATHER — Gathers statistics on all objects in the schema. This is the default.

  • GATHER AUTO — Gathers all necessary statistics automatically.

    The database implicitly determines which objects need new statistics and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are comp_id, no_invalidate, stattab, statid, and statown; all other parameter settings will be ignored. Also, the database returns a list of objects processed.

  • GATHER STALE — Gathers statistics on stale objects by querying the *_TAB_MODIFICATIONS views. Also, the database returns a list of objects found to be stale.

  • GATHER EMPTY — Gathers statistics on objects that currently have no statistics. Also, the database returns a list of objects found to have no statistics.

  • LIST AUTO — Returns a list of objects to be processed with GATHER AUTO.

  • LIST STALE — Returns list of stale objects as determined by looking at the *_TAB_MODIFICATIONS views.

  • LIST EMPTY — Returns a list of objects that currently have no statistics.

objlist

List of objects found to be stale or empty

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

Gather statistics on objects even if they are locked

obj_filter_list

A list of object filters. When provided, GATHER_SCHEMA_STATS 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-NULL values s1, s2, ... are specified for attributes a1, a2, ... in one object filter. An object o is said to satisfy this object filter if (o.a1 like s1) and (o.a2 like s2) and ... is true.

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 on gather_table_stats.

  • The FREQUENCY histograms on NEWTAB 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;

162.7.53 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 162-58 GATHER_SYSTEM_STATS Procedure Parameters

Parameter Description

gathering_mode

Specifies the mode in which the database gathers system statistics. Possible values are:

  • NOWORKLOAD

    The database captures performance characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period, the database estimates the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads.

    To fine tune system statistics for the workload, use START and STOP or the INTERVAL option. If you gather both NOWORKLOAD and workload-specific statistics (statistics collected using INTERVAL or START and STOP), the optimizer uses the workload statistics. Collected components include cpuspeednw, ioseektim, and iotfrspeed.

  • INTERVAL

    The database captures system activity during a specified interval in minutes. This parameter works in combination with the interval parameter. The database creates or updates system statistics in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components include maxthr, slavethr, cpuspeed, sreadtim, mreadtim, and mbrc.

  • START | STOP

    The database captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. The database ignores the INTERVAL value. Collected components include maxthr, slavethr, cpuspeed, sreadtim, mreadtim, and mbrc.

  • EXADATA

    In this mode, gathered system statistics take into account the unique capabilities of Oracle Exadata, such as large I/O size and high I/O throughput. The database sets multiblock read count and I/O throughput statistics along with CPU speed.

interval

Specifies the number of minutes in which to gather system statistics. This parameter applies only when gathering_mode='INTERVAL'.

stattab

Specifies the table in which the database stores the statistics.

statid

Specifies an optional identifier associated with the statistics saved in stattab.

statown

Specifies the schema containing stattab, if different from the current schema.

Exceptions

ORA-20000: Object does not exist or insufficient privileges

ORA-20001: Invalid input value

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

ORA-20003: Unable to gather system statistics

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

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;

162.7.54 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 162-59 GATHER_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Schema containing the table.

tabname

Name of the table.

partname

Name of the partition.

estimate_percent

Determines the percentage of rows to sample.

The valid range is between 0.000001 and 100. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to enable the database to determine the appropriate sample size for optimal statistics. This is the default.

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.

block_sample

Determines whether the database uses random block sampling (TRUE) or random row sampling (FALSE). The default is FALSE.

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.

method_opt

METHOD_OPT - When setting preference on global, schema, database or dictionary level, only'FOR ALL' syntax is allowed. Other than that, method_opt accepts either of the following options, or both in combination:

  • FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

  • FOR COLUMNS [column_clause] [size_clause]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column_clause is defined as column_clause := column_name | extension name | extension

  • - integer : Number of histogram buckets. Must be in the range [1,2048].
  • - REPEAT : Collects histograms only on the columns that already have histograms
  • - AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
  • - SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
  • - column_name : Name of a column
  • - extension : can be either a column group in the format of (column_name, Colume_name [, ...]) or an expression

The default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

degree

Determines the degree of parallelism used for gathering statistics.

The default for degree is NULL. NULL means to use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Change the default using the SET_DATABASE_PREFS, SET_GLOBAL_PREFS, SET_SCHEMA_PREFS, and SET_TABLE_PREFS procedures. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement.

Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. The degree is between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters), according to the size of the object. When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution when the size of the object does not warrant parallel execution.

granularity

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

'ALL' - Gathers all (subpartition, partition, and global) statistics

'APPROX_GLOBAL AND PARTITION' - similar to 'GLOBAL AND PARTITION' but in this case the global statistics are aggregated from partition level statistics. This option will aggregate all statistics except the number of distinct values for columns and number of distinct keys of indexes. The existing histograms of the columns at the table level are also aggregated.The aggregation will use only partitions with statistics, so to get accurate global statistics, users should make sure to have statistics for all partitions. Global statistics are gathered if partname is NULL or if the aggregation cannot be performed (for example, if statistics for one of the partitions is missing).

'AUTO'- Determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - Gathers global statistics

'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- Gathers partition-level statistics

'SUBPARTITION' - Gathers subpartition-level statistics.

cascade

Determines whether to collect index statistics as part of gathering table statistics.

Specifying this option is equivalent to running the GATHER_INDEX_STATS procedure on each index of the table. Use the constant DBMS_STATS.AUTO_CASCADE to enable the database to determine whether index statistics need to be collected. This is the default. You can change the default using the SET_DATABASE_PREFS, SET_GLOBAL_PREFS, SET_SCHEMA_PREFS, and SET_TABLE_PREFS procedures.

stattab

User statistics table identifier describing where to save the current statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

no_invalidate

Controls the invalidation of dependent cursors of the tables for which statistics are being gathered. The default is DBMS_STATS.AUTO_INVALIDATE, which means the database decides when to invalidate dependent cursors.

If set to TRUE, then the database not invalidate dependent cursors. If set to FALSE, then the procedure invalidates dependent cursors immediately.

stattype

Statistics type. The only value allowed is DATA.

force

Gather statistics of table even if it is locked

context

Not used.

options

Determines the options parameter used in the GATHER_TABLE_STATS procedure. The preference takes the following values:

  • GATHER — Gathers statistics for all objects in the table. This is the default.

  • GATHER AUTO — Gathers all necessary statistics automatically. Oracle recommends setting GATHER AUTO on tables that undergo bulk load operations that gather statistics. This option is only applicable to tables that do not have INCREMENTAL enabled. Running GATHER_TABLE_STATS procedure on these tables with the GATHER AUTO option skips regathering the already fresh statistics.

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 on gather_table_stats.

  • The FREQUENCY histograms on NEWTAB 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)');

162.7.55 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 162-60 GENERATE_STATS Procedure Parameters

Parameter Description

ownname

Schema of object

objname

Name of object

organized

Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have consecutive keys referencing different table blocks on disk.

This parameter is only used for b-tree indexes. The number can be in the range of 0-10, with 0 representing a completely organized index and 10 a completely disorganized one.

force

If TRUE, generates statistics for the target object even if it is 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.

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

162.7.56 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 162-61 GET_ADVISOR_OPR_FILTER Procedure Parameters

Parameter Description

opr_id

The ID of the statistics operation stored in the DBA_OPTSTAT_OPERATIONS view.

opr_filter

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

162.7.57 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 162-62 GET_ADVISOR_RECS Function Parameters

Parameter Description

ownname

The owner of the table.

tabname

The name of the table.

rec

The Optimizer Statistics Advisor recommendation.

  • INCREMENTAL

    When only a small number of range partitions are modified, this option improves the performance of statistics gathering dramatically. However, it requires additional space to store synopses for maintaining incremental statistics. The report analyzes this trade-off.

  • CONCURRENT

    The report recommends either setting the CONCURRENT preference, or specifying AUTO_DEGREE for individual tables. If the system resources and usage satisfies the conditions, the advisor always recommends setting CONCURRENT first. The advisor only recommends AUTO_DEGREE when statistics gathering on an individual table take a long time and the CONCURRENT preference is already set.

type

Type of the report: TEXT, HTML, or XML.

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

162.7.58 GET_COLUMN_STATS Procedures

These procedures gets all column-related information. In the form of this procedure that deals with user-defined statistics, the statistics type returned is the type stored, in addition to the user-defined statistics.

Syntax

DBMS_STATS.GET_COLUMN_STATS (
   ownname     VARCHAR2, 
   tabname     VARCHAR2, 
   colname     VARCHAR2, 
   partname    VARCHAR2 DEFAULT NULL,
   stattab     VARCHAR2 DEFAULT NULL, 
   statid      VARCHAR2 DEFAULT NULL,
   distcnt OUT NUMBER, 
   density OUT NUMBER,
   nullcnt OUT NUMBER, 
   srec    OUT StatRec,
   avgclen OUT NUMBER,
   statown     VARCHAR2 DEFAULT NULL);

Use the following for user-defined statistics:

DBMS_STATS.GET_COLUMN_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   colname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   ext_stats   OUT  RAW,
   stattypown  OUT  VARCHAR2 DEFAULT NULL, 
   stattypname OUT  VARCHAR2 DEFAULT NULL, 
   statown          VARCHAR2 DEFAULT NULL);

Parameters

Table 162-63 GET_COLUMN_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table to which this column belongs

colname

Name of the column or extension

partname

Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, statistics are retrieved from the global table level.

stattab

User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, statistics are retrieved directly from the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)

ext_stats

The user-defined statistics

stattypown

Schema of the statistics type

stattypname

Name of the statistics type

distcnt

Number of distinct values

density

Column density

nullcnt

Number of NULLs

srec

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

avgclen

Average length of the column (in bytes)

statown

Schema containing stattab (if different than ownname)

Exceptions

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

Usage Notes

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.

162.7.59 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 162-64 GET_INDEX_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

indname

Name of the index

partname

Name of the index partition for which to get the statistics. If the index is partitioned and if partname is NULL, then the statistics are retrieved for the global index level.

stattab

User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL)

ext_stats

User-defined statistics

stattypown

Schema of the statistics type

stattypname

Name of the statistics type

numrows

Number of rows in the index (partition)

numlblks

Number of leaf blocks in the index (partition)

numdist

Number of distinct keys in the index (partition)

avglblk

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

avgdblk

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

clstfct

Clustering factor for the index (partition)

indlevel

Height of the index (partition)

statown

Schema containing stattab (if different than ownname)

guessq

Guess quality for the index (partition)

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 and cachehit 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 the DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for automatic mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. To prevent the user from utilizing inaccurate and unreliable data, the optimizer computes a “confidence factor” for each cachehit and a cachedblk 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

162.7.60 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 162-65 GET_PARAM Function Parameters

Parameter Description

pname

Parameter name

Exceptions

ORA-20001: Invalid input values

162.7.61 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 162-66 GET_PREFS Function Parameters

Parameter Description

pname

Preference name. The possible values are:

  • APPROXIMATE_NDV_ALGORITHM

  • AUTO_STAT_EXTENSIONS

  • CASCADE

  • CONCURRENT

  • DEGREE

  • ESTIMATE_PERCENT

  • GLOBAL_TEMP_TABLE_STATS

  • GRANULARITY

  • INCREMENTAL

  • INCREMENTAL_STALENESS

  • INCREMENTAL_LEVEL

  • METHOD_OPT

  • NO_INVALIDATE

  • OPTIONS

  • PREFERENCE_OVERRIDES_PARAMETER

  • PUBLISH

  • STALE_PERCENT

  • STAT_CATEGORY

  • TABLE_CACHED_BLOCKS

  • WAIT_TIME_TO_UPDATE_STATUS

ownname

Owner name

tabname

Table name

Table 162-67 Preference Descriptions

Preference Name Description

APPROXIMATE_NDV_ALGORITHM

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 delete the following preferences:

  • REPEAT OR HYPERLOGLOG

    This is the default. If INCREMENTAL is enabled on the table, then the database preserves the format of any existing synopses that use the adaptive sampling algorithm. However, the database creates any new synopses in HyperLogLog format. This approach is attractive when existing performance is acceptable, and you do not want to incur the performance cost of reformatting legacy content.

  • ADAPTIVE SAMPLING

    The database uses the adaptive sampling algorithm for all synopses. This is the most conservative option.

  • HYPERLOGLOG

    The database uses the HyperLogLog algorithm for all new and stale synopses. In contrast to dynamic sampling, the HyperLogLog algorithm uses a randomization technique. The advantages of HyperLogLog over adaptive sampling are:

    • The accuracy of the new algorithm is similar to the original algorithm.

    • The memory required is significantly lower, which typically leads to huge reductions in synopsis size.

AUTO_STAT_EXTENSIONS

Controls the automatic creation of extensions when database statistics are gathered.

You can set the following values:

  • ON — When applicable, a SQL plan directive can trigger the creation of column group statistics based on usage of columns in the predicates in the workload.

  • OFF— The database does not create column group statistics automatically. The database creates them only when the CREATE_EXTENDED_STATS function is executed, or when extended statistics are specified explicitly in the METHOD_OPT clause of DBMS_STATS. This is the default.

CASCADE

Determines whether index statistics are collected as part of gathering table statistics.

CONCURRENT

Determines whether statistics are gathered concurrently on multiple objects, or serially, one object at a time. Valid values are:

  • MANUAL — Concurrency is enabled only for manual statistics gathering.

  • AUTOMATIC — Concurrency is enabled only for the automatic statistics gathering.

  • ALL — Concurrency is enabled for both manual and automatic statistics gathering.

  • OFF — Concurrency is disabled for both manual and automatic statistics.

DEGREE

Determines degree of parallelism used for gathering statistics.

ESTIMATE_PERCENT

Determines the percentage of rows to sample.

The valid range is between 0.000001 and 100. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to enable the database to determine the appropriate sample size for optimal statistics. This is the default.

GLOBAL_TEMP_TABLE_STATS

Controls whether the statistics gathered for a global temporary table should be stored as shared statistics or session statistics. This preference takes two values:

  • SHARED — All sessions see the same set of statistics

  • SESSION — Statistics gathered by the GATHER_TABLE_STATS procedure on a global temporary table are session-specific. Thus, the database only uses them for queries issued in the same session as the statistics gathering process. The database deletes session-specific statistics when a session terminates.

GRANULARITY

Determines the granularity of statistics to collect. This preference is only relevant for partitioned tables.

The following values are valid:

  • ALL — Gathers all statistics: subpartition, partition, and global.

  • AUTO — Determines the granularity based on the partitioning type. This is the default value.

  • DEFAULT — Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. Use GLOBAL AND PARTITION for this functionality.

  • GLOBAL — Gathers global statistics.

  • GLOBAL AND PARTITION — Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

  • PARTITION — Gathers partition-level statistics.

  • SUBPARTITION — Gathers subpartition-level statistics.

INCREMENTAL

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:

  • The INCREMENTAL value for the partitioned table is set to TRUE.

  • The PUBLISH value for the partitioned table is set to TRUE.

  • The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

If the INCREMENTAL value for the partitioned table was set to FALSE (default value), then the database uses a full table scan to maintain the global statistics. This technique is a much more resource-intensive and time-consuming operation for large tables.

INCREMENTAL_LEVEL

Controls which synopses to collect when INCREMENTAL preference is set to TRUE. It takes the following values:

  • PARTITION — Gathers partition-level synopses.

    This is the default value. If PARTITION is set on a nonpartitioned table, then the database does not gather synopses.

  • TABLE — Gathers table-level synopses.

    Specify this value when you want to exchange this table with a partition. Before the exchange, you can run GATHER_TABLE_STATS on this table with INCREMENTAL set to TRUE and INCREMENTAL_LEVEL to TABLE. The result is that the database gathers table-level synopses on this table. After the exchange, the partition has synopses that come from the table-level synopses of the table before the exchange. You can only use preference value in the SET_TABLE_PREFS procedure: this value is not allowed in the other SET_*_PREFS procedures.

INCREMENTAL_STALENESS

Specifies when a partition or subpartition is considered stale. This parameter takes an enumeration of values, such as 'USE_STALE_PERCENT' and 'USE_LOCKED_STATS'. You can also specify multiple values, such as 'USE_STALE_PERCENT, USE_LOCKED_STATS, ALLOW_MIXED_FORMAT'.

The parameter accepts the following values:

  • USE_STALE_PERCENT—A partition or subpartition is not considered stale when DML changes are below the threshold set by the STALE_PERCENT preference.

    For example, assume that STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. The partition has 5% DML changes. The database does not regather statistics.

    Assume a different case in which STALE_PERCENT is 10. You specify USE_STALE_PERCEMENT for INCREMENTAL_STALENESS. However, in this case the partition is locked and has 20% of DML changes. Because the partition is locked, the database does not regather statistics.

  • USE_LOCKED_STATS—Locked partitions or subpartitions statistics are never considered stale, regardless of DML changes.

    For example, assume that STALE_PERCENT is 10. You specify 'USE_LOCKED_STATS, USE_STALE_PERCENT'. The partition, which is locked, has 20% DML changes. The partition is not considered stale. The database uses existing statistics to derive global statistics.

  • ALLOW_MIXED_FORMAT—Adaptive sampling synopses and HyperLogLog synopses are permitted to coexist.

  • NULL—A partition or subpartition is considered stale when it has any DML changes.

    For example, assume that STALE_PERCENT is 10. You specify the value 'NULL' for INCREMENTAL_STALENESS. The partition has 5% of DML changes. The database regathers statistics.

Note that the following two executions are different:

EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', 'NULL');
EXEC DBMS_STATS.SET_TABLE_PREFS
  ('sh', 'sales', 'INCREMENTAL_STALENESS', null);

The first execution uses single quotes to set the preference to the value NULL, whereas the second sets the preference to the default, which is ALLOW_MIXED_FORMAT.

METHOD_OPT

Controls column statistics collection and histogram creation. When setting preferences at the global, schema, database, or dictionary level, only FOR ALL syntax is allowed:

  • FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

The size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

  • integer — Specifies the number of histogram buckets. The number must be between 1 and 2048.
  • REPEAT — Collects histograms only on the columns that already have histograms.
  • AUTO — Determines the columns on which to collect histograms based on data distribution and the workload of the columns.
  • SKEWONLY — Determines the columns on which to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO. You can change the value using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

NO_INVALIDATE

Controls the invalidation of dependent cursors of the tables for which statistics are being gathered. The default is DBMS_STATS.AUTO_INVALIDATE, which means the database decides when to invalidate dependent cursors.

If set to TRUE, then the database not invalidate dependent cursors. If set to FALSE, then the procedure invalidates dependent cursors immediately.

OPTIONS

Determines the options parameter used in the GATHER_TABLE_STATS procedure. The preference takes the following values:

  • GATHER — Gathers statistics for all objects in the table. This is the default.

  • GATHER AUTO — Gathers all necessary statistics automatically. Oracle recommends setting GATHER AUTO on tables that undergo bulk load operations that gather statistics. This option is only applicable to tables that do not have INCREMENTAL enabled. Running GATHER_TABLE_STATS procedure on these tables with the GATHER AUTO option skips regathering the already fresh statistics.

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: 

  • TRUE — Ignores input parameter values, and uses the value of the corresponding preference.

  • FALSE — Obeys input parameter values.

Specifying this preference does not change the order of precedence of table, global, and default.

PUBLISH

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.

STALE_PERCENT

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 stale_percent is non-negative numbers. The default value is 10, which means that a table having more than 10% of changes is considered stale.

STAT_CATEGORY

Specifies which statistics to import or export, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (default)

  • SYNOPSES - information to support incremental statistics

The value 'OBJECT_STATS, SYNOPSES' specifies table statistics, column statistics, index statistics, and synopses.

TABLE_CACHED_BLOCKS

Specifies the average number of blocks assumed to be cached in the buffer cache when calculating the index clustering factor.

WAIT_TIME_TO_UPDATE_STATS

Specifies the number of minutes before timing out for locks and pins required for updating statistics. It accepts values in the range 0 to 65535. The default value is 15 minutes. The value 0 gets the locks and pins in no-wait mode.

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 issues GATHER_*_STATS procedures. DBMS_STATS can collect statistics for a single object in parallel based on the value of the DEGREE parameter. However, parallelism is limited to one object. The CONCURRENT 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 least 4.

  • If the ownname and tabname 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

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

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

162.7.64 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 162-68 GET_SYSTEM_STATS Procedure Parameters

Parameter Description

status

Output is one of the following:

  • COMPLETED:

  • AUTOGATHERING:

  • MANUALGATHERING:

  • BADSTATS:

dstart

Date when statistics gathering started.

If status = MANUALGATHERING, the start date is returned.

dstop

Date when statistics gathering stopped.

  • If status = COMPLETE, the finish date is returned.

  • If status = AUTOGATHERING, the future finish date is returned.

  • If status = BADSTATS, the must-finished-by date is returned.

pname

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

  • iotfrspeed - I/O transfer speed in bytes for each millisecond

  • ioseektim - seek time + latency time + operating system overhead time, in milliseconds

  • sreadtim - average time to read single block (random read), in milliseconds

  • mreadtim - average time to read an mbrc block at once (sequential read), in milliseconds

  • cpuspeed - average number of CPU cycles for each second, in millions, captured for the workload (statistics collected using 'INTERVAL' or 'START' and 'STOP' options)

  • cpuspeednw - average number of CPU cycles for each second, in millions, captured for the no-workload (statistics collected using 'NOWORKLOAD' option.

  • mbrc - average multiblock read count for sequential read, in blocks

  • maxthr - maximum I/O system throughput, in bytes/second

  • slavethr - average slave I/O throughput, in bytes/second

pvalue

Parameter value to get

stattab

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

statid

Optional identifier associated with the statistics saved in the stattab

statown

Schema containing stattab (if different from current schema)

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

ORA-20003: Unable to gather system statistics

ORA-20004: Parameter does not exist

Usage Notes

To run this procedure, you need the GATHER_SYSTEM_STATISTICS role.

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

Parameters

Table 162-69 GET_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema.

tabname

Name of the table to which this column belongs.

partname

Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.

stattab

User statistics table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.

statid

Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

numrows

Number of rows in the table or partition.

numblks

Number of blocks in the table or partition.

avgrlen

Average row length for the table or partition.

statown

Schema containing stattab (if different from ownname).

im_imcu_count

The number of In-Memory Compression Units (IMCUs) in the table or partition.

im_block_count

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.

scanrate

The rate, in MB/s, at which the database scans external tables. This parameter is relevant only for external tables.

cachedblk

For internal use only.

cachehit

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 and cachehit 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 the DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for automatic mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. To prevent the user from utilizing inaccurate and unreliable data, the optimizer computes a “confidence factor” for each cachehit and a cachedblk 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

162.7.66 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 162-70 IMPLEMENT_ADVISOR_TASK Function Parameters

Parameter Description

task_name

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

level

The level of the implementation. Possible values are

  • ALL: Ignores the filters and implements all recommendations.

  • TYPICAL: Implements the recommendations according to the filters in place.

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 level

    Only users with both the ANALYZE ANY and ANALYZE 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 and ANALYZE ANY DICTIONARY privileges can perform this task for all statistics operations.

    • Users with the ANALYZE ANY privilege but not the ANALYZE ANY DICTIONARY privilege can perform this task for statistics operations related to any schema except SYS.

    • Users with the ANALYZE ANY DICTIONARY privilege but not the ANALYZE ANY privilege can perform this task for statistics operations related to their own schema and the SYS schema.

    • Users with neither the ANALYZE ANY nor the ANALYZE 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.

Return Values

This function returns an XML CLOB that indicates which recommendations were successfully implemented.

Exceptions

  • ORA-20000: Insufficient privileges

  • ORA-20001: Invalid input values

  • ORA-20012: Optimizer Statistics Advisor errors

Example 162-10 Implementing Optimizer Statistics Advisor Recommendations

This script illustrates a basic Optimizer Statistics Advisor session. It creates a task, executes it, generates a report, and then implements the recommendations.

DECLARE
  v_tname      VARCHAR2(128) := 'my_task';
  v_ename      VARCHAR2(128) := NULL;
  v_report     CLOB := null;
  v_script     CLOB := null;
  v_imp_result CLOB;
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);

  -- view the task report
  v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
  DBMS_OUTPUT.PUT_LINE(v_report);

  -- implement all recommendations
  v_imp_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;

Note:

Oracle Database SQL Tuning Guide to learn how to manage Optimizer Statistics Advisor

162.7.67 IMPORT_COLUMN_STATS Procedure

This procedure retrieves statistics for a particular column from the user statistics table identified by stattab and stores them in the dictionary.

Syntax

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

Parameters

Table 162-71 IMPORT_COLUMN_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table to which this column belongs

colname

Name of the column or extension

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition column statistics are imported.

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier to associate with these statistics within stattab (optional)

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

If set to TRUE, imports statistics even if 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.

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

ORA-20005: Object statistics are locked

Usage Notes

Oracle does not support export or import of statistics across databases of different character sets.

162.7.68 IMPORT_DATABASE_PREFS Procedure

This procedure is used to import the statistics preferences of all the tables, excluding the tables owned by Oracle. These tables can by included by passing TRUE for the add_sys parameter.

Syntax

DBMS_STATS.IMPORT_DATABASE_PREFS (
    stattab    IN  VARCHAR2,
    statid     IN  VARCHAR2 DEFAULT NULL,
    statown    IN  VARCHAR2 DEFAULT NULL
    add_sys    IN  BOOLEAN DEFAULT FALSE);

Parameters

Table 162-72 IMPORT_DATABASE_PREFS Procedure Parameters

Parameter Description

stattab

Statistics table name where to import the statistics

statid

Optional identifier to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

add_sys

Value TRUE will include the Oracle-owned tables

Exceptions

ORA-20000: 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.

  • Oracle does not support export or import of statistics across databases of different character sets.

Examples

DBMS_STATS.IMPORT_DATABASE_PREFS('STATTAB', statown=>'SH');

162.7.69 IMPORT_DATABASE_STATS Procedure

This procedure retrieves statistics for all objects in the database from the user statistics table(s) and stores them in the dictionary.

Syntax

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

Parameters

Table 162-73 IMPORT_DATABASE_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different from current schema)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

Overrides statistics locked at the object (table) level:

  • TRUE - Ignores the statistics lock and imports the statistics

  • FALSE - The statistics will be imported only if they are not locked

stat_category

Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (Default)

  • SYNOPSES - information to support incremental statistics

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

Usage Notes

  • To run this procedure, you need to have the SYSDBA role or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.

  • Oracle does not support export or import of statistics across databases of different character sets.

162.7.70 IMPORT_DICTIONARY_STATS Procedure

This procedure retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) from the user statistics table and stores them in the dictionary.

Syntax

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

Parameters

Table 162-74 IMPORT_DICTIONARY_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

The (optional) identifier to associate with these statistics within stattab

statown

Schema containing stattab (if different from current schema)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

Overrides statistics lock at the object (table) level:

  • TRUE - Ignores the statistics lock and imports the statistics.

  • FALSE - The statistics will be imported only if there is no lock.

stat_category

Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (Default)

  • SYNOPSES - information to support incremental statistics

Usage Notes

  • You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.

  • Oracle does not support export or import of statistics across databases of different character sets.

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

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

162.7.71 IMPORT_FIXED_OBJECTS_STATS Procedure

This procedure retrieves statistics for fixed tables from the user statistics table(s) and stores them in the dictionary.

Syntax

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

Parameters

Table 162-75 IMPORT_FIXED_OBJECTS_STATS Procedure Parameters

Parameter Description

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different from current schema)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

Overrides statistics lock:

  • TRUE - Ignores the statistics lock and imports the statistics

  • FALSE - The statistics will be imported only if there is no lock

Usage Notes

  • You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.

  • Oracle does not support export or import of statistics across databases of different character sets.

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

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

162.7.72 IMPORT_INDEX_STATS Procedure

This procedure retrieves statistics for a particular index from the user statistics table identified by stattab and stores them in the dictionary.

Syntax

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

Parameters

Table 162-76 IMPORT_INDEX_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

indname

Name of the index

partname

Name of the index partition. If the index is partitioned and if partname is NULL, then global and partition index statistics are imported.

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

Imports statistics even if index statistics are locked

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

ORA-20005: Object statistics are locked

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.

Oracle does not support export or import of statistics across databases of different character sets.

162.7.73 IMPORT_SCHEMA_PREFS Procedure

This procedure is used to import the statistics preferences of all the tables owned by the specified owner name.

Syntax

DBMS_STATS.IMPORT_SCHEMA_PREFS (
    ownname    IN  VARCHAR2,
    stattab    IN  VARCHAR2,
    statid     IN  VARCHAR2 DEFAULT NULL,
    statown    IN  VARCHAR2 DEFAULT NULL);

Parameters

Table 162-77 IMPORT_SCHEMA_PREFS Procedure Parameters

Parameter Description

ownname

Owner name

stattab

Statistics table name from where to import the statistics

statid

(Optional) Identifier to associate with these statistics within stattab

statown

Schema containing stattab (if other than ownname)

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 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.IMPORT_SCHEMA_PREFS('SH', 'STAT');

162.7.74 IMPORT_SCHEMA_STATS Procedure

This procedure retrieves statistics for all objects in the schema identified by ownname from the user statistics table and stores them in the dictionary.

Syntax

DBMS_STATS.IMPORT_SCHEMA_STATS (
   ownname         VARCHAR2,
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   statown         VARCHAR2 DEFAULT NULL,
   no_invalidate   BOOLEAN DEFAULTto_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   force           BOOLEAN DEFAULT FALSE,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Parameters

Table 162-78 IMPORT_SCHEMA_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force

Overrides statistics locked at the object (table) level:

  • TRUE - Ignores the statistics lock and imports the statistics.

  • FALSE - Statistics will be imported only if there is no lock.

stat_category

Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (Default)

  • SYNOPSES - information to support incremental statistics

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

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.

Oracle does not support export or import of statistics across databases of different character sets.

162.7.75 IMPORT_SYSTEM_STATS Procedure

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

Syntax

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

Parameters

Table 162-79 IMPORT_SYSTEM_STATS Procedure Parameters

Parameter Description

stattab

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

statid

Optional identifier associated with the statistics retrieved from the stattab

statown

Schema containing stattab (if different from current schema)

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

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

ORA-20003: Unable to import system statistics

Usage Notes

To run this procedure, you need the GATHER_SYSTEM_STATISTICS role.

Oracle does not support export or import of statistics across databases of different character sets.

162.7.76 IMPORT_TABLE_PREFS Procedure

This procedure is used to set the statistics preferences of the specified table in the specified schema.

Syntax

DBMS_STATS.IMPORT_TABLE_PREFS (
    ownname    IN  VARCHAR2,
    tabname    IN  VARCHAR2,
    stattab    IN  VARCHAR2,
    statid     IN  VARCHAR2 DEFAULT NULL,
    statown    IN  VARCHAR2 DEFAULT NULL);

Parameters

Table 162-80 IMPORT_TABLE_PREFS Procedure Parameters

Parameter Description

ownname

Owner name

tabname

Table name

stattab

Statistics table name from where to import the statistics

statid

(Optional) Identifier to associate with these statistics within stattab

statown

Schema containing stattab (if other than ownname)

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.IMPORT_TABLE_PREFS('SH', 'SALES', 'STAT');

162.7.77 IMPORT_TABLE_STATS Procedure

This procedure retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary.

Cascade results in all index statistics associated with the specified table being imported as well.

Syntax

DBMS_STATS.IMPORT_TABLE_STATS (
   ownname         VARCHAR2, 
   tabname         VARCHAR2,
   partname        VARCHAR2 DEFAULT NULL,
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   cascade         BOOLEAN  DEFAULT TRUE,
   statown         VARCHAR2 DEFAULT NULL,
   no_invalidate   BOOLEAN DEFAULT to_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   force           BOOLEAN DEFAULT FALSE,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Parameters

Table 162-81 IMPORT_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are imported.

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier (optional) to associate with these statistics within stattab

cascade

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

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure

force

Imports statistics even if table statistics are locked

stat_category

Specifies what statistics to import, accepting multiple values separated by a comma. Values supported:

  • OBJECT_STATS - table statistics, column statistics and index statistics (Default)

  • SYNOPSES - information to support incremental statistics

Exceptions

ORA-20000: Object does not exist or insufficient privileges

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

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.

Oracle does not support export or import of statistics across databases of different character sets.

162.7.78 INTERRUPT_ADVISOR_TASK Procedure

This procedure interrupts a currently executing Optimizer Statistics Advisor task.

The task ends its operations as it does when at a normal exit, at which point you can access intermediate results. You can also resume the task using the "RESUME_ADVISOR_TASK Procedure".

Syntax

DBMS_STATS.INTERRUPT_ADVISOR_TASK (
  task_name IN VARCHAR2);

Parameters

Table 162-82 INTERRUPT_ADVISOR_TASK Procedure Parameters

Parameter Description

task_name

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