Chapter 39. DBMS

Chapter 39. DBMS_STATS

Table of Contents

39.2.1. ALTER_STATS_HISTORY_RETENTION
39.2.2. COPY_TABLE_STATS
39.2.3. CREATE_STAT_TABLE
39.2.4. DELETE_COLUMN_STATS
39.2.5. DELETE_DATABASE_STATS
39.2.6. DELETE_DICTIONARY_STATS
39.2.7. DELETE_INDEX_STATS
39.2.8. DELETE_SCHEMA_STATS
39.2.9. DELETE_SYSTEM_STATS
39.2.10. DELETE_TABLE_STATS
39.2.11. DROP_STAT_TABLE
39.2.12. EXPORT_COLUMN_STATS
39.2.13. EXPORT_DATABASE_STATS
39.2.14. EXPORT_INDEX_STATS
39.2.15. EXPORT_SCHEMA_STATS
39.2.16. EXPORT_SYSTEM_STATS
39.2.17. EXPORT_TABLE_STATS
39.2.18. GATHER_DATABASE_STATS
39.2.19. GATHER_DICTIONARY_STATS
39.2.20. GATHER_INDEX_STATS
39.2.21. GATHER_SCHEMA_STATS
39.2.22. GATHER_SYSTEM_STATS
39.2.23. GATHER_TABLE_STATS
39.2.24. GET_COLUMN_STATS
39.2.25. GET_INDEX_STATS
39.2.26. GET_TABLE_STATS
39.2.27. IMPORT_COLUMN_STATS
39.2.28. IMPORT_DATABASE_STATS
39.2.29. IMPORT_INDEX_STATS
39.2.30. IMPORT_SCHEMA_STATS
39.2.31. IMPORT_SYSTEM_STATS
39.2.32. IMPORT_TABLE_STATS
39.2.33. LOCK_TABLE_STATS
39.2.34. LOCK_SCHEMA_STATS
39.2.35. SET_COLUMN_STATS
39.2.36. SET_INDEX_STATS
39.2.37. SET_TABLE_STATS
39.2.38. PURGE_STATS
39.2.39. RESTORE_SCHEMA_STATS
39.2.40. RESTORE_TABLE_STATS
39.2.41. SET_PARAM
39.2.42. SET_SYSTEM_STATS
39.2.43. UNLOCK_TABLE_STATS
39.2.44. UNLOCK_SCHEMA_STATS

39.3.1. GET_PARAM
39.3.2. GET_STATS_HISTORY_AVAILABILITY
39.3.3. GET_STATS_HISTORY_RETENTION
39.3.4. TO_BOOLEAN

This chapter briefly introduces the DBMS_STATS package, and describes how to use the procedures and functions of the package.

39.1. Overview

DBMS_STATS collects and manages (such as deletion and initialization) statistics about database objects such as columns, tables, data dictionaries(DD), indexes, schemas, and the system.

When collecting table statistics, the data distribution can be stored in each column selectively by the user. This is called a histogram, and the collected data types are NUMBER, CHAR, VARCHAR, DATE, TIME and TIMESTAMP.

A user can only collect statistics on objects the user owns. The user with the ANALYZE ANY privilege can collect statistics on all objects.

39.2. Procedures

This section describes the procedures provided by the DBMS_STATS package, in alphabetical order.

39.2.1. ALTER_STATS_HISTORY_RETENTION

Changes the statistics history retention period that determines when statistics history is deleted.

For more information about deleting history, refer to “39.2.38. PURGE_STATS”.

Details about the ALTER_STATS_HISTORY_RETENTION procedure are as follows:

Prototype

DBMS_STATS.ALTER_STATS_HISTORY_RETENTION
(
   retention    NUMBER
);

Parameter

Parameter	Description
retention	Maximum time period for keeping statistics history. (Unit: day) The value must be greater than or equal to 1. Values less than 1 are reserved for special purposes. -1: History is not deleted automatically. (Retention period is infinite) 0: History is not saved. Existing history is deleted using the automatic deletion function. NULL: Sets to the default value: 31.

Description

retention

Maximum time period for keeping statistics history. (Unit: day)

The value must be greater than or equal to 1. Values less than 1 are reserved for special purposes.

-1: History is not deleted automatically. (Retention period is infinite)
0: History is not saved. Existing history is deleted using the automatic deletion function.
NULL: Sets to the default value: 31.

39.2.2. COPY_TABLE_STATS

Copies statistics from a partition of a table or local index to a target partition.

If the histogram of the partition key's first column is collected up to the previous partition for a RANGE partition, the histogram is also updated.

Details about the COPY_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.COPY_TABLE_STATS
(
    ownname             IN          VARCHAR2,                                 
    tabname             IN          VARCHAR2,                                 
    srcpartname         IN          VARCHAR2,                                 
    dstpartname         IN          VARCHAR2,                                 
    scale_factor        IN          NUMBER      DEFAULT 1,                    
    flags               IN          NUMBER      DEFAULT NULL,
    force               IN          BOOLEAN     DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Name of the schema to which the partition belongs.
tabname	Name of the table to which the partition belongs.
srcpartname	Name of the source partition to copy statistics from.
dstpartname	Name of the target partition to copy statistics to.
scale_factor	Values, such as the number of rows or blocks in the source partition, are multiplied by this parameter value and then saved to the target partition.
flags	Unused.
force	If set to TRUE, statistics are copied even if locked.

Exception

Exception Description
20003 Occurs when the specified table or partition name does not exist.

Exception	Description
20003	Occurs when the specified table or partition name does not exist.

39.2.3. CREATE_STAT_TABLE

Creates a table to store statistics.

Details about the CREATE_STAT_TABLE procedure are as follows:

Prototype

DBMS_STATS.CREATE_STAT_TABLE
(
    ownname      VARCHAR2,
    stattab      VARCHAR2,
    tblspace     VARCHAR2 DEFAULT NULL
);

Parameters

Parameter	Description
ownname	Schema name of the statistics table.
stattab	Table to store statistics in.
tblspace	Tablespace name of the statistics table. (Default value: NULL, use SYSTEM tablespace.)

Exception

Exception Description
7068 Occurs when the entered schema is invalid.

Exception	Description
7068	Occurs when the entered schema is invalid.

39.2.4. DELETE_COLUMN_STATS

Deletes statistics of a column.

Details about the DELETE_COLUMN_STATS procedure are as follows:

Prototype

DBMS_STATS.DELETE_COLUMN_STATS 
(
    ownname           VARCHAR2, 
    tabname           VARCHAR2, 
    colname           VARCHAR2, 
    partname          VARCHAR2 DEFAULT NULL,
    cascade_parts     BOOLEAN  DEFAULT TRUE,
    no_invalidate     BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force             BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name of the column.
colname	Column name.
partname	Partition name to delete statistics from. If the table is partitioned and partname is NULL, statistics for the specified column are deleted from the table.
cascade_parts	If the table is partitioned and partname is NULL, column statistics can be deleted from all partitions by setting this parameter to TRUE.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics are deleted even if locked.

Exception

Exception Description
OBJECT_NOT_EXISTS Occurs when the object does not exist.

Exception	Description
OBJECT_NOT_EXISTS	Occurs when the object does not exist.

39.2.5. DELETE_DATABASE_STATS

Deletes statistics of all tables in the database.

Details about the DELETE_DATABASE_STATS procedure are as follows:

Prototype

DBMS_STATS.DELETE_DATABASE_STATS 
(
    no_invalidate     BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force             BOOLEAN DEFAULT FALSE  
);

Parameters

Parameter	Description
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are deleted even if locked.

39.2.6. DELETE_DICTIONARY_STATS

Deletes statistics of all data dictionary schemas (SYS and SYSCAT).

Details about the DELETE_DICTIONARY_STATS procedure are as follows:

Prototype

DBMS_STATS.DELETE_DICTIONARY_STATS 
(
    no_invalidate     BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force             BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are deleted even if locked.

39.2.7. DELETE_INDEX_STATS

Deletes statistics of an index.

Details about the DELETE_INDEX_STATS procedure are as follows:

Prototype

DBMS_STATS.DELETE_INDEX_STATS 
(
    ownname           VARCHAR2, 
    idxname           VARCHAR2, 
    partname          VARCHAR2 DEFAULT NULL,
    cascade_parts     BOOLEAN  DEFAULT TRUE,
    no_invalidate     BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force             BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
idxname	The index name.
partname	Partition name to delete statistics from. If the index is partitioned and partname is NULL, statistics for the index are deleted.
cascade_parts	If the index is partitioned and partname is NULL, statistics can be deleted from all partitions by setting this parameter to TRUE.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, index statistics are deleted even if locked.

Exception

Exception Description
OBJECT_NOT_EXISTS Occurs when the object does not exist.

Exception	Description
OBJECT_NOT_EXISTS	Occurs when the object does not exist.

39.2.8. DELETE_SCHEMA_STATS

Deletes statistics of all schemas.

Details about the DELETE_SCHEMA_STATS procedure are as follows:

Prototype

DBMS_STATS.DELETE_SCHEMA_STATS 
(
    ownname           VARCHAR2, 
    no_invalidate     BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force             BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are deleted even if locked.

Exception

Exception Description
OBJECT_NOT_EXISTS Occurs when the object does not exist.

Exception	Description
OBJECT_NOT_EXISTS	Occurs when the object does not exist.

39.2.9. DELETE_SYSTEM_STATS

Deletes statistics of the workload system, and initializes statistics of the noworkload system. The statistics of the workload system are collected with the INTERVAL, START, and STOP options, and those of the noworkload system are collected with the NOWORKLOAD option.

Details about the DELETE_SYSTEM_STATS procedure are as follows:

Prototype
```
DBMS_STATS.DELETE_SYSTEM_STATS ();
```
Exception

Exception Description
20000 Occurs when the user does not have DBA privileges.

39.2.10. DELETE_TABLE_STATS

Deletes statistics of a table.

Details about the DELETE_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.DELETE_TABLE_STATS 
(
  ownname           VARCHAR2, 
  tabname           VARCHAR2, 
  partname          VARCHAR2 DEFAULT NULL,
  cascade_parts     BOOLEAN  DEFAULT TRUE,
  cascade_columns   BOOLEAN  DEFAULT TRUE,
  cascade_indexes   BOOLEAN  DEFAULT TRUE,
  no_invalidate     BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
partname	Partition name to delete statistics from. If the table is partitioned and partname is NULL, statistics for the table and all columns in the table are deleted.
cascade_parts	If the table is partitioned and partname is NULL, statistics can be deleted from all partitions by setting this parameter to TRUE.
cascade_columns	If set to TRUE, column statistics are also deleted.
cascade_indexes	If set to TRUE, index statistics in the table are also deleted.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, table statistics are deleted even if locked.

Exception

Exception Description
OBJECT_NOT_EXISTS Occurs when the object does not exist.

Exception	Description
OBJECT_NOT_EXISTS	Occurs when the object does not exist.

39.2.11. DROP_STAT_TABLE

Deletes a statistics table.

Details about the DROP_STAT_TABLE procedure are as follows:

Prototype

DBMS_STATS.DROP_STAT_TABLE
(
   ownname     VARCHAR2,
   stattab     VARCHAR2
);

Parameters

Parameter Description
ownname Schema name of the statistics table.
stattab Table to store statistics in.
Exception

Exception Description
7068 Occurs when an invalid schema has been entered.

Exception	Description
7068	Occurs when an invalid schema has been entered.

39.2.12. EXPORT_COLUMN_STATS

Saves statistics of a specified column in the statistics table.

Details about the EXPORT_COLUMN_STATS procedure are as follows:

Prototype

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

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
colname	Column name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.

Exception

Exception Description
20003 Occurs when an invalid schema has been entered.

Exception	Description
20003	Occurs when an invalid schema has been entered.

39.2.13. EXPORT_DATABASE_STATS

Saves statistics of tables in the database to the statistics table.

Details about the EXPORT_DATABASE_STATS procedure are as follows:

Prototype

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

Parameters

Parameter Description
stattab Statistics table name.
statid Stat ID used in the statistics table.
statown Schema name of the statistics table.
Exception

Exception Description
-20000 Occurs when the user does not have DBA privileges.

Exception	Description
-20000	Occurs when the user does not have DBA privileges.

39.2.14. EXPORT_INDEX_STATS

Saves statistics of a specified index in the statistics table.

Details about the EXPORT_INDEX_STATS procedure are as follows:

Prototype

DBMS_STATS.EXPORT_INDEX_STATS
(
   ownname     VARCHAR2,
   indname     VARCHAR2,
   partname    VARCHAR2 DEFAULT NULL,
   stattab     VARCHAR2,
   statid      VARCHAR2 DEFAULT NULL,
   statown     VARCHAR2 DEFAULT NULL
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
indname	Index name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.

Exception

Exception Description
20003 Occurs when an invalid index has been entered.

Exception	Description
20003	Occurs when an invalid index has been entered.

39.2.15. EXPORT_SCHEMA_STATS

Saves statistics of the entire schema in the statistics table.

Details about the EXPORT_SCHEMA_STATS procedure are as follows:

Prototype

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

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.

Exception

Exception Description
20003 Occurs when an invalid schema has been entered.

Exception	Description
20003	Occurs when an invalid schema has been entered.

39.2.16. EXPORT_SYSTEM_STATS

Saves statistics of the system in the statistics table.

Details about the EXPORT_SYSTEM_STATS procedure are as follows:

Prototype

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

Parameters

Parameter Description
stattab Statistics table name.
statid Stat ID used in the statistics table.
statown Schema name of the statistics table.
Exception

Exception Description
-20000 Occurs when the user does not have DBA privileges.

Exception	Description
-20000	Occurs when the user does not have DBA privileges.

39.2.17. EXPORT_TABLE_STATS

Saves statistics of a table in the statistics table.

Details about the EXPORT_TABLE_STATS procedure are as follows:

Prototype

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

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
cascade	Saves statistics of columns, indexes, and partitions of a table.
statown	Schema name of the statistics table.

Exception

Exception Description
20003 Occurs when an invalid table has been entered.

Exception	Description
20003	Occurs when an invalid table has been entered.

39.2.18. GATHER_DATABASE_STATS

Collects statistics on all objects in the database.

Details about the GATHER_DATABASE_STATS procedure are as follows:

Prototype

DBMS_STATS.GATHER_DATABASE_STATS 
(
    estimate_percent NUMBER   DEFAULT 
                   to_estimate_percent_type(get_param('STAT_ESTIMATE_PERCENT'),
    block_sample     BOOLEAN  DEFAULT FALSE,
    method_opt       VARCHAR2 DEFAULT get_param('STAT_METHOD_OPT'),
    degree           NUMBER   DEFAULT to_degree_type (get_param('STAT_DEGREE')),
    cascade          BOOLEAN  DEFAULT to_boolean(get_param('STAT_CASCADE')),
    gather_sys       BOOLEAN  DEFAULT FALSE,
    no_invalidate    BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    options          VARCHAR2 DEFAULT 'GATHER',
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
estimate_percent	Percentage of rows to estimate. The valid range is from 0.000001 to 100. If set to 0, the optimizer will determine an appropriate sampling rate for the table size.
block_sample	Option to perform block sampling instead of row sampling when estimate_percent is less than 100. Since block sampling does not select samples evenly compared to row sampling, it is recommended to set this parameter to FALSE if estimate_percent is not great enough.
method_opt	Options for generating a histogram. Refer to [method_opt Parameter Options] for configuration information.
degree	Number of queries to be processed in parallel.
cascade	Option to collect statistics on the indexes.
gather_sys	Option to gather statistics on the tables owned by the 'SYS' user.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
options	Criteria for collecting tables: GATHER: Collect all tables. GATHER AUTO: Collect only the tables that meet the criteria. GATHER EMPTY: Collect the tables that do not contain any statistics. GATHER STALE: Collect tables in which 10% or more rows have been modified since the last statistics collection.
force	If set to TRUE, statistics about the tables and dependent objects are collected even if locked.

[method_opt Parameter Options]

The method_opt parameter can be configured as follows:

FOR ALL
```
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
```
The following options are used:

Option Description
INDEXED Collect statistics on the indexed column.
HIDDEN
Collect statistics on columns created by the functional index.
size_clause Length of the histogram to create.
FOR COLUMNS
```
FOR COLUMNS [size_clause] column [size_clause] [,column [size_clause]...]
```
The following option is used:

Option Description
size_clause Length of the histogram to create.

Option	Description
INDEXED	Collect statistics on the indexed column.
HIDDEN	Collect statistics on columns created by the functional index.
size_clause	Length of the histogram to create.

Option	Description
size_clause	Length of the histogram to create.

size_clause

size_clause := SIZE {integer | REPEAT | AUTO}

The following options are used:

Option	Description
integer	Number of histogram buckets. Valid range is from 1 to 254.
REPEAT	Collect information on existing histograms.
AUTO	System automatically determines the value.

Option

Description

integer

Number of histogram buckets.

Valid range is from 1 to 254.

REPEAT

Collect information on existing histograms.

AUTO

System automatically determines the value.

Exception

Exception Description
BAD_INPUT_VALUE Occurs when an invalid parameter value has been entered.

Exception	Description
BAD_INPUT_VALUE	Occurs when an invalid parameter value has been entered.

39.2.19. GATHER_DICTIONARY_STATS

Collects statistics on all data dictionary schema objects (SYS and SYSCAT).

Details about the GATHER_DICTIONARY_STATS procedure are as follows:

Prototype

DBMS_STATS.GATHER_DICTIONARY_STATS 
(
    estimate_percent NUMBER   DEFAULT 
                   to_estimate_percent_type(get_param('STAT_ESTIMATE_PERCENT'),
    block_sample     BOOLEAN  DEFAULT FALSE,
    method_opt       VARCHAR2 DEFAULT get_param('STAT_METHOD_OPT'),
    degree           NUMBER   DEFAULT to_degree_type (get_param('STAT_DEGREE')),
    cascade          BOOLEAN  DEFAULT to_boolean(get_param('STAT_CASCADE')),
    no_invalidate    BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
estimate_percent	Percentage of rows to estimate. The valid range is from 0.000001 to 100. If set to 0, the optimizer will determine an appropriate sampling rate for the table size.
block_sample	Option to perform block sampling instead of row sampling when estimate_percent is less than 100. Since block sampling does not select samples evenly compared to row sampling, it is recommended to set this parameter to FALSE if estimate_percent is not great enough.
method_opt	Option to generate a histogram. Refer to [method_opt Parameter Options] for configuration information.
degree	Number of queries to be processed in parallel.
cascade	Option to collect statistics on the indexes.
gather_sys	Option to gather statistics on the tables owned by the 'SYS' user.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are collected even if locked.

Exception

Exception Description
BAD_INPUT_VALUE Occurs when an invalid parameter value has been entered.

Exception	Description
BAD_INPUT_VALUE	Occurs when an invalid parameter value has been entered.

39.2.20. GATHER_INDEX_STATS

Collects statistics on an index.

Details about the GATHER_INDEX_STATS procedure are as follows:

Prototype

DBMS_STATS.GATHER_INDEX_STATS 
(
    ownname          VARCHAR2,
    idxname          VARCHAR2,
    partname         VARCHAR2 DEFAULT NULL,
    estimate_percent NUMBER DEFAULT to_estimate_percent_type (
                     get_param ('STAT_ESTIMATE_PERCENT')),
    degree           NUMBER DEFAULT to_degree_type(get_param('STAT_DEGREE')),
    no_invalidate    BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
idxname	Index name.
partname	Partition name.
estimate_percent	Percentage of rows to estimate. The valid range is from 0.000001 to 100. If set to 0, the optimizer will determine an appropriate sampling rate for the table size.
degree	Number of queries to be processed in parallel.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, index statistics are collected even if locked.

Exception

Exception Description
OBJECT_NOT_EXISTS Occurs when the object does not exist.

Exception	Description
OBJECT_NOT_EXISTS	Occurs when the object does not exist.

39.2.21. GATHER_SCHEMA_STATS

Collects statistics on all schema objects.

Details about the GATHER_SCHEMA_STATS procedure are as follows:

Prototype

DBMS_STATS.GATHER_SCHEMA_STATS 
(
    ownname          VARCHAR2,
    estimate_percent NUMBER   DEFAULT 
                   to_estimate_percent_type(get_param('STAT_ESTIMATE_PERCENT'),
    block_sample     BOOLEAN  DEFAULT FALSE,
    method_opt       VARCHAR2 DEFAULT get_param('STAT_METHOD_OPT'),
    degree           NUMBER   DEFAULT to_degree_type (get_param('STAT_DEGREE')),
    cascade          BOOLEAN  DEFAULT to_boolean(get_param('STAT_CASCADE')),
    no_invalidate    BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
estimate_percent	Percentage of rows to estimate. The valid range is from 0.000001 to 100. If set to 0, the optimizer will determine an appropriate sampling rate for the table size.
block_sample	Option to perform block sampling instead of row sampling when estimate_percent is less than 100. Since block sampling does not select samples evenly compared to row sampling, it is recommended to set this parameter to FALSE if estimate_percent is not great enough.
method_opt	Option to generate a histogram. Refer to [method_opt Parameter Options] for configuration information.
degree	Number of queries to be processed in parallel.
cascade	Option to collect statistics on the indexes.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are collected even if locked.

Exceptions

Exception Description
OBJECT_NOT_EXISTS Occurs when the object does not exist.
BAD_INPUT_VALUE Occurs when an invalid parameter value has been entered.

Exception	Description
OBJECT_NOT_EXISTS	Occurs when the object does not exist.
BAD_INPUT_VALUE	Occurs when an invalid parameter value has been entered.

39.2.22. GATHER_SYSTEM_STATS

Collects statistics on the system. DBA privileges are required to use this procedure.

Details about the GATHER_SYSTEM_STATS procedure are as follows:

Prototype

DBMS_STATS.GATHER_SYSTEM_STATS 
(
    gathering_mode     VARCHAR2 DEFAULT 'NOWORKLOAD',
    interval           NUMBER   DEFAULT 60,
    limit              VARCHAR2 DEFAULT '3G'
);

Parameters

Parameter	Description
gathering_mode	Supports the NOWORKLOAD, INTERVAL, START, and STOP options. Refer to the following [gathering_mode Parameter Options] for a description of each option.
interval	Duration, in minutes, to collect statistics on system performance when gathering_mode is set to INTERVAL. The default value is 60 minutes.
limit	Largest data volume for I/O, in bytes, when gathering_mode is set to NOWORKLOAD. The unit options are K (kilobytes), M (megabytes), G (gigabytes), and T (terabytes). To gather more accurate information, it is recommended to use a value larger than the size of the database or OS cache buffer. The default is three gigabytes.

Parameter

Description

gathering_mode

Supports the NOWORKLOAD, INTERVAL, START, and STOP options.

Refer to the following [gathering_mode Parameter Options] for a description of each option.

interval

Duration, in minutes, to collect statistics on system performance when gathering_mode is set to INTERVAL. The default value is 60 minutes.

limit

Largest data volume for I/O, in bytes, when gathering_mode is set to NOWORKLOAD. The unit options are K (kilobytes), M (megabytes), G (gigabytes), and T (terabytes).

To gather more accurate information, it is recommended to use a value larger than the size of the database or OS cache buffer. The default is three gigabytes.

[gathering_mode Parameter Options]

The following describes the options used in gathering_mode.

Option	Description
NOWORKLOAD	Measures CPU and I/O performance of the system by collecting CPUSPEED, SEEKTM, and TRFSPEED statistics. Since system statistics are collected through data files, it is strongly recommended to collect them after creating the database and all tablespaces. To fine-tune system statistics, use gathering_mode with the INTERVAL option, or the START and STOP options. If both noworkload and workload system statistics are collected, the workload statistics will be used by the optimizer. Depending on the database size, it may take a few minutes to collect system statistics in this mode. The collection duration can be adjusted with the limit parameter.
INTERVAL	Measures system performance for the period specified in the interval parameter by collecting workload system statistics including SBLKRDTM, MBLKRDTM, and MBLKRDCNT. If needed, collection can be completed before the specified interval time passes by setting the STOP option for gathering_mode.
START, STOP	Measures system performance between the period specified by START and STOP options. Collects workload system statistics including SBLKRDTM, MBLKRDTM, and MBLKRDCNT.

Option

Description

NOWORKLOAD

Measures CPU and I/O performance of the system by collecting CPUSPEED, SEEKTM, and TRFSPEED statistics.

Since system statistics are collected through data files, it is strongly recommended to collect them after creating the database and all tablespaces.

To fine-tune system statistics, use gathering_mode with the INTERVAL option, or the START and STOP options.

If both noworkload and workload system statistics are collected, the workload statistics will be used by the optimizer.

Depending on the database size, it may take a few minutes to collect system statistics in this mode. The collection duration can be adjusted with the limit parameter.

INTERVAL

Measures system performance for the period specified in the interval parameter by collecting workload system statistics including SBLKRDTM, MBLKRDTM, and MBLKRDCNT.

If needed, collection can be completed before the specified interval time passes by setting the STOP option for gathering_mode.

START, STOP

Measures system performance between the period specified by START and STOP options. Collects workload system statistics including SBLKRDTM, MBLKRDTM, and MBLKRDCNT.

Exceptions

Exception	Description
20000	Occurs when the user has insufficient privileges.
20023	Occurs when an invalid gathering_mode option has been used. Only NOWORKLOAD, INTERVAL, START, and STOP are valid.
20026	Occurs when an invalid parameter value has been entered.
20027	Occurs when DD_AUX_STATS table is bad.

39.2.23. GATHER_TABLE_STATS

Collects statistics on a table.

Details about the GATHER_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.GATHER_TABLE_STATS 
(
    ownname          VARCHAR2, 
    tabname          VARCHAR2, 
    partname         VARCHAR2 DEFAULT NULL,
    estimate_percent NUMBER   DEFAULT 
                   to_estimate_percent_type(get_param('STAT_ESTIMATE_PERCENT'),
    block_sample     BOOLEAN  DEFAULT FALSE,
    method_opt       VARCHAR2 DEFAULT get_param('STAT_METHOD_OPT'),
    degree           NUMBER   DEFAULT to_degree_type (get_param('STAT_DEGREE')),
    cascade_indexes  BOOLEAN  DEFAULT to_boolean(get_param('STAT_CASCADE')),
    no_invalidate    BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    granularity      VARCHAR2 DEFAULT 'ALL',
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
partname	Partition name.
estimate_percent	Percentage of rows to estimate. The valid range is from 0.000001 to 100. If set to 0, the optimizer will determine an appropriate sampling rate for the table size.
block_sample	Option to perform block sampling instead of row sampling when estimate_percent is less than 100. Since block sampling does not select samples evenly compared to row sampling, it is recommended to set this parameter to FALSE if estimate_percent is not great enough.
method_opt	Option to generate a histogram. Refer to [method_opt Parameter Options] for configuration information.
degree	Number of queries to be processed in parallel.
cascade_indexes	Option to collects statistics on indexes.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
granularity	Granularity of statistics to collect. ALL: Collect statistics on tables and partitions. GLOBAL: Collect statistics on tables only. PARTITION: Collects statistics on partitions only.
force	If set to TRUE, table statistics are collected even if locked.

Exceptions

Exception Description
OBJECT_NOT_EXISTS Occurs when the object does not exist.
BAD_INPUT_VALUE Occurs when an invalid parameter value has been entered.

Exception	Description
OBJECT_NOT_EXISTS	Occurs when the object does not exist.
BAD_INPUT_VALUE	Occurs when an invalid parameter value has been entered.

39.2.24. GET_COLUMN_STATS

Gets statistics about specified column from DD (data dictionary) or stats table.

Details about the GET_COLUMN_STATS procedure are as follows:

Prototype

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

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
colname	Column name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
distcnt	Number of distinct values in the column.
density	Density of column values.
nullcnt	Number of NULL values in the column.
avgclen	Average length in bytes for the column.

Exceptions

Exception Description
20003 Occurs when an invalid column is specified.
20025 Occurs when an invalid statistics table is specified.

Exception	Description
20003	Occurs when an invalid column is specified.
20025	Occurs when an invalid statistics table is specified.

39.2.25. GET_INDEX_STATS

Gets statistics about specified index from DD (data dictionary) or stats table.

Details about the GET_INDEX_STATS procedure are as follows:

Prototype

DBMS_STATS.GET_INDEX_STATS
(
    ownname      VARCHAR2,
    idxname      VARCHAR2,
    partname     VARCHAR2 DEFAULT NULL,
    stattab      VARCHAR2 DEFAULT NULL,
    statid       VARCHAR2 DEFAULT NULL,
    statown      GET_TABLE_STATSVARCHAR2 DEFAULT NULL,
    numrows      OUT   NUMBER,
    numlblks     OUT   NUMBER,
    numdist      OUT   NUMBER,
    clstfct      OUT   NUMBER,
    idxlevel     OUT   NUMBER
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
idxname	Index name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
numrows	Number of rows in the index.
numblks	Number of blocks in the index.
numlblks	Number of leaf blocks in the index.
numdist	Number of distinct values in the index.
clstfct	Index clustering factor. A value between 0 and 1.
idxlevel	Index level.

Exceptions

Exception	Description
20007	Occurs when an invalid index is specified.
20003	Occurs when an invalid partition is specified.
20004	Occurs when a partition name is specified for a non-partitioned index.
20025	Occurs when an invalid statistics table is specified.

39.2.26. GET_TABLE_STATS

Gets statistics about specified table from DD (data dictionary) or stats table.

Details about the GET_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.GET_TABLE_STATS
(
    ownname      VARCHAR2,
    tabname      VARCHAR2,
    partname     VARCHAR2 DEFAULT NULL,
    stattab      VARCHAR2 DEFAULT NULL,
    statid       VARCHAR2 DEFAULT NULL,
    statown      VARCHAR2 DEFAULT NULL,
    numrows      OUT NUMBER,
    numblks      OUT NUMBER,
    avgrlen      OUT NUMBER
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
numrows	Number of rows in the table.
numblks	Number of blocks in the table.
avgrlen	Average row length.

Exceptions

Exception	Description
20003	Occurs when an invalid table or partition is specified.
20004	Occurs when a partition name is specified for a non-partitioned table.
20025	Occurs when an invalid statistics table is specified.

39.2.27. IMPORT_COLUMN_STATS

Saves statistics of a specified column in the statistics table to the data dictionary.

Details about the IMPORT_COLUMN_STATS procedure are as follows:

Prototype

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_boolean(get_param('STAT_NO_INVALIDATE')),
    force             BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
colname	Column name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
no_invalidate	If set to TRUE, the related physical plan is not deleted.
force	If set to TRUE, column statistics are saved even if locked.

Exception

Exception Description
20003 Occurs when an invalid column has been entered.

39.2.28. IMPORT_DATABASE_STATS

Saves all table statistics in the statistics table to the data dictionary.

Details about the IMPORT_DATABASE_STATS procedure are as follows:

Prototype

DBMS_STATS.IMPORT_DATABASE_STATS
(
    stattab     VARCHAR2,
    statid      VARCHAR2 DEFAULT NULL,
    statown     VARCHAR2 DEFAULT NULL,
    no_invalidate    BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are saved even if locked.

Exception

Exception Description
-20000 Occurs when the user does not have DBA privileges.

39.2.29. IMPORT_INDEX_STATS

Saves statistics of a specified index in the statistics table to the data dictionary.

Details about the IMPORT_INDEX_STATS procedure are as follows:

Prototype

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_boolean(get_param('STAT_NO_INVALIDATE')),
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
idxname	Index name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, index statistics are saved even if locked.

Exception

Exception Description
20003 Occurs when an invalid index has been entered.

39.2.30. IMPORT_SCHEMA_STATS

Saves statistics of the entire schema in the statistics table to the data dictionary.

Details about the IMPORT_SCHEMA_STATS procedure are as follows:

Prototype

DBMS_STATS.IMPORT_SCHEMA_STATS
(
    ownname     VARCHAR2,
    stattab     VARCHAR2,
    statid      VARCHAR2 DEFAULT NULL,
    statown     VARCHAR2 DEFAULT NULL,
    no_invalidate    BOOLEAN  DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are saved even if locked.

Exception

Exception Description
20003 Occurs when an invalid schema has been entered.

39.2.31. IMPORT_SYSTEM_STATS

Saves the system statistics in the statistics table to the data dictionary.

Details about the IMPORT_SYSTEM_STATS procedure are as follows:

Prototype

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

Parameters

Parameter Description
stattab Statistics table name.
statid Stat ID used in the statistics table.
statown Schema name of the statistics table.
Exception

Exception Description
-20000 Occurs when the user does not have DBA privileges.

39.2.32. IMPORT_TABLE_STATS

Saves statistics of a table in the statistics table to the data dictionary.

Details about the IMPORT_TABLE_STATS procedure are as follows:

Prototype

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

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
cascade	Option to collect statistics on the columns, indexes, and partitions of the table.
statown	Schema name of the statistics table.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, table statistics are saved even if locked.

Exception

Exception Description
20003 Occurs when an invalid table has been entered.

39.2.33. LOCK_TABLE_STATS

Locks statistics about a table and its indexes, partitions, and columns. Locked statistics cannot be collected, deleted, or copied.

If statistics are locked, an error occurs when collecting, deleting, or copying statistics on a single table (GATHER_TABLE_STATS, DELETE_TABLE_STATS, etc.) and stat processing is skipped when collecting, deleting, or copying statistics on multiple objects (GATHER_SCHEMA_STATS, DELETE_DATABASE_STATS, etc.).

Details about the LOCK_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.LOCK_TABLE_STATS
(
    ownname      VARCHAR2,
    tabname      VARCHAR2
);

Parameters

Parameter Description
ownname
Schema name.
If set to NULL, this indicates the schema that called this procedure.
tabname Table name.
Exception

Exception Description
20003 Occurs when an invalid table has been entered.

39.2.34. LOCK_SCHEMA_STATS

Locks statistics about the tables, indexes, partitions, and columns of a schema. Locked statistics cannot be collected, deleted, or copied.

Details about the LOCK_SCHEMA_STATS procedure are as follows:

Prototype

DBMS_STATS.LOCK_SCHEMA_STATS
(
    ownname      VARCHAR2
);

Parameter

Parameter Description
ownname Schema name.
Exception

Exception Description
20002 Occurs when an invalid schema has been entered.

39.2.35. SET_COLUMN_STATS

Sets statistics about specified column in DD (data dictionary) or stats table.

Details about the SET_COLUMN_STATS procedure are as follows:

Prototype

DBMS_STATS.SET_COLUMN_STATS
(
    ownname      VARCHAR2,
    tabname      VARCHAR2,
    colname      VARCHAR2,
    stattab      VARCHAR2 DEFAULT NULL,
    statid       VARCHAR2 DEFAULT NULL,
    statown      VARCHAR2 DEFAULT NULL,
    distcnt      NUMBER,
    density      NUMBER,
    nullcnt      NUMBER,
    avgclen      NUMBER,
    no_invalidate  BOOLEAN DEFAULT to_boolean(SET_param('STAT_NO_INVALIDATE')),
    force          BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
colname	Column name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
distcnt	Number of distinct values in the column.
density	Density of column values.
nullcnt	Number of NULL values in the column.
avgclen	Average length in bytes for the column.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, table statistics are saved even if locked.

Exceptions

Exception Description
20003 Occurs when an invalid column is specified.
20025 Occurs when an invalid statistics table is specified.

39.2.36. SET_INDEX_STATS

Sets statistics about specified index in DD (data dictionary) or stats table.

Details about the SET_INDEX_STATS procedure are as follows:

Prototype

DBMS_STATS.SET_INDEX_STATS
(
    ownname      VARCHAR2,
    idxname      VARCHAR2,
    partname     VARCHAR2 DEFAULT NULL,
    stattab      VARCHAR2 DEFAULT NULL,
    statid       VARCHAR2 DEFAULT NULL,
    statown      VARCHAR2 DEFAULT NULL,
    numrows      NUMBER,
    numlblks     NUMBER,
    numdist      NUMBER,
    clstfct      NUMBER,
    idxlevel     NUMBER,
    no_invalidate   BOOLEAN  DEFAULT to_boolean(SET_param('STAT_NO_INVALIDATE')),
    force           BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
idxname	Index name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
numrows	Number of rows in the index.
numblks	Number of blocks in the index.
numlblks	Number of leaf blocks in the index.
numdist	Number of distinct values in the index.
clstfct	Index clustering factor. A value between 0 and 1.
idxlevel	Index level.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, table statistics are saved even if locked.

Exceptions

Exception	Description
20003	Occurs when an invalid partition is specified.
20004	Occurs when a partition name is specified for a non-partitioned index.
20007	Occurs when the index is disabled or its statistics cannot be saved.
20025	Occurs when an invalid statistics table is specified.

39.2.37. SET_TABLE_STATS

Sets statistics about specified table in DD (data dictionary) or stats table.

Details about the SET_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.SET_TABLE_STATS
(
    ownname      VARCHAR2,
    tabname      VARCHAR2,
    partname     VARCHAR2 DEFAULT NULL,
    stattab      VARCHAR2 DEFAULT NULL,
    statid       VARCHAR2 DEFAULT NULL,
    statown      VARCHAR2 DEFAULT NULL,
    numrows      NUMBER,
    numblks      NUMBER,
    avgrlen      NUMBER,
    no_invalidate  BOOLEAN  DEFAULT to_boolean(SET_param('STAT_NO_INVALIDATE')),
    force          BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	Table name.
partname	Partition name.
stattab	Statistics table name.
statid	Stat ID used in the statistics table.
statown	Schema name of the statistics table.
numrows	Number of rows in the table.
numblks	Number of blocks in the table.
avgrlen	Average row length.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, table statistics are saved even if locked.

Exceptions

Exception	Description
20003	Occurs when an invalid table or partition is specified.
20004	Occurs when a partition name is specified for a non-partitioned table.
20025	Occurs when an invalid statistics table is specified.

39.2.38. PURGE_STATS

Purges statistics saved before the specified timestamp. The SYSDBA or ANALYZE ANY privilege is required to perform this procedure.

Details about the PURGE_STATS procedure are as follows:

Prototype

DBMS_STATS.PURGE_STATS
(
    before_timestamp    IN          TIMESTAMP WITH TIME ZONE
);

Parameter

Parameter	Description
before_timestamp	If set to a non-NULL value, statistics saved before the specified time are purged. If set to NULL, statistics are purged based on the retention period returned by the GET_STATS_HISTORY_RETENTION function.

39.2.39. RESTORE_SCHEMA_STATS

Restores the statistics of all objects of a schema with a timestamp later than the specified value (as_of_timestamp).

Details about the RESTORE_SCHEMA_STATS procedure are as follows:

Prototype

DBMS_STATS.RESTORE_SCHEMA_STATS
(
    ownname          VARCHAR2, 
    as_of_timestamp  TIMESTAMP WITH TIME ZONE, 
    no_invalidate    BOOLEAN DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force            BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
as_of_timestamp	Statistical data with a timestamp later than this value is restored.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, statistics about tables and dependent objects are restored even if locked.

39.2.40. RESTORE_TABLE_STATS

Restores the statistics of a table with a timestamp later than the specified timestamp (as_of_timestamp). This procedure also restores the statistics of dependent indexes and columns.

Details about the RESTORE_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.RESTORE_TABLE_STATS
(
    ownname         VARCHAR2, 
    tabname         VARCHAR2,
    as_of_timestamp TIMESTAMP WITH TIME ZONE, 
    no_invalidate   BOOLEAN DEFAULT to_boolean(get_param('STAT_NO_INVALIDATE')),
    force           BOOLEAN DEFAULT FALSE
);

Parameters

Parameter	Description
ownname	Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname	The table name.
as_of_timestamp	Statistical data with a timestamp later than this value is restored.
no_invalidate	If set to TRUE, the dependent physical plan is not deleted.
force	If set to TRUE, table statistics are restored even if locked.

39.2.41. SET_PARAM

Modifies default values of parameters in the DBMS_STATS package.

Details about the SET_PARAM procedure are as follows:

Prototype

DBMS_STATS.SET_PARAM 
(
   pname     VARCHAR2,
   pval      VARCHAR2
);

Parameters

Parameter

Description

pname

Parameter name.

pval

Parameter value.

NULL: Default value is set by referring to the VT_PARAMETER value when installing Tibero.
If not set to NULL, select one of:
- STAT_CASCADE: Default value of the cascade parameter used in the DBMS_STATS package. (Default value: Y)
- STAT_METHOD_OPT: Default value of the method_opt parameter used in the DBMS_STATS package. (Default value: FOR ALL COLUMNS SIZE 32)
- STAT_NO_INVALIDATE: Default value of the no_invalidate parameter used in the DBMS_STATS package. (Default value: N)
- STAT_ESTIMATE_PERCENT: Default value of the estimate_percent parameter used in the DBMS_STATS package. (Default value: 0)
- STAT_DEGREE: Default value of the degree parameter used in the DBMS_STATS package. (Default value: 1)

Exception

Exception Description
BAD_INPUT_VALUE Occurs when an invalid parameter value has been entered.

39.2.42. SET_SYSTEM_STATS

Manually sets statistics of the system.

Details about the SET_SYSTEM_STATS procedure are as follows:

Prototype

DBMS_STATS.SET_SYSTEM_STATS
(
     pname      VARCHAR2,
     pvalue     VARCHAR2
);

Parameters

Parameter

Description

pname

Parameter name.

pvalue

Parameter value.

Set one of the following values:

CPUSPEED: Average number of CPU cycles per μs, in megahertz..
SEEKTM: Average I/O seek time, in milliseconds.
TRFSPEED: Average I/O transfer speed, in bytes per millisecond.
SBLKRDTM: Average time to read a single block, in milliseconds.
MBLKRDTM: Average time to read consecutive BLKRDCNT blocks, in milliseconds.
BLKRDCNT: Average number of consecutive blocks read.

Exceptions

Exception	Description
20000	Occurs when the user does not have DBA privileges.
20021	Occurs when an invalid parameter value has been entered.
20022	Occurs when an invalid parameter name has been entered.

39.2.43. UNLOCK_TABLE_STATS

Unlocks statistics about a table and its indexes, partitions, and columns. Unlocked statistics can be collected, deleted, or copied.

Details about the UNLOCK_TABLE_STATS procedure are as follows:

Prototype

DBMS_STATS.UNLOCK_TABLE_STATS
(
    ownname      VARCHAR2,
    tabname      VARCHAR2
);

Parameters

Parameter Description
ownname
Schema name. If set to NULL, this indicates the schema that called this procedure.
tabname Table name.
Exception

Exception Description
20003 Occurs when the specified table name is incorrect.

39.2.44. UNLOCK_SCHEMA_STATS

Unlocks statistics about the tables, indexes, partitions, and columns of a schema. Unlocked statistics can be collected, deleted, or copied.

Details about the UNLOCK_SCHEMA_STATS procedure are as follows:

Prototype

DBMS_STATS.UNLOCK_SCHEMA_STATS
(
    ownname      VARCHAR2
);

Parameter

Parameter Description
ownname Schema name.
Exception

Exception Description
20002 Occurs when an invalid schema has been entered.

39.3. Functions

This section describes the functions provided by the DBMS_STATS package, in alphabetical order.

39.3.1. GET_PARAM

Returns the default parameter value of the DBMS_STATS package.

Details about the GET_PARAM function are as follows:

Prototype

DBMS_STATS.GET_PARAM 
(
    pname     VARCHAR2
)
RETURN VARCHAR2;

Parameter

Parameter Description
pname Parameter name.
Exception

Exception Description
BAD_INPUT_VALUE Occurs when an invalid parameter value has been entered.

39.3.2. GET_STATS_HISTORY_AVAILABILITY

Returns the oldest timestamp of available statistics history. Statistics cannot be restored to a time before this value.

Details about the GET_STATS_HISTORY_AVAILABILITY function are as follows:

Prototype

DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY
RETURN TIMESTAMP WITH TIME ZONE;

39.3.3. GET_STATS_HISTORY_RETENTION

Returns the maximum retention period of the specified statistics history.

Details about the GET_STATS_HISTORY_RETENTION function are as follows:

Prototype

DBMS_STATS.GET_STATS_HISTORY_RETENTION
RETURN NUMBER;

39.3.4. TO_BOOLEAN

Converts a string representing a boolean value to a BOOLEAN value.

Details about the TO_BOOLEAN function are as follows:

Prototype

DBMS_STATS.TO_BOOLEAN 
(
    strval     VARCHAR2
)
RETURN BOOLEAN;

Parameter

Parameter Description
strval String representing a boolean value.
Exception

Exception Description
BAD_INPUT_VALUE Occurs when an invalid parameter value has been entered.

Prev		Next
Chapter 38. DBMS_SQL_TRANSLATOR	Home	Chapter 40. DBMS_SYSTEM