Overview of DBMS_STATS

The DBMS_STATS package is used to view and modify statistics collected by the optimizer for database objects.

DBMS_STATS

The DBMS_STATS package in seekdb supports the following operations:

• Collecting optimizer statistics

• Setting column, table, and index information

• Deleting statistics

• Locking and unlocking statistics

Overview of DBMS_STATS procedures

The following table lists the DBMS_STATS procedures supported by seekdb and provides a brief description for each.

Subprogram	Description
ALTER_STATS_HISTORY_RETENTION	Modifies the retention interval for historical statistics. The default retention interval is 31 days.
CREATE_STAT_TABLE	Creates a table in the specified schema to store statistics.
DELETE_COLUMN_STATS	Deletes column-level statistics.
DELETE_INDEX_STATS	Deletes statistics related to indexes.
DELETE_TABLE_STATS	Deletes table-level statistics.
DELETE_SCHEMA_STATS	Deletes statistics for all tables in the specified schema.
DELETE_SCHEMA_PREFS	Deletes the statistics preferences (Prefs) for all tables in the specified schema.
DELETE_SYSTEM_STATS	Deletes system statistics
DELETE_TABLE_PREFS	Deletes the statistics preferences (Prefs) for tables owned by the specified user.
DROP_STAT_TABLE	Drops the user statistics table.
EXPORT_COLUMN_STATS	Exports column-level statistics.
EXPORT_INDEX_STATS	Retrieves statistics for the specified index and stores them in the statistics table.
EXPORT_TABLE_STATS	Exports table-level statistics.
EXPORT_SCHEMA_STATS	Exports statistics for all tables in the schema.
FLUSH_DATABASE_MONITORING_INFO	Flushes memory monitoring information for all tables to the dictionary.
GATHER_INDEX_STATS	Collects index statistics.
GATHER_TABLE_STATS	Collects statistics for tables and columns.
GATHER_SCHEMA_STATS	Collects statistics for all objects in the schema.
GATHER_SYSTEM_STATS	Collects system statistics
GET_STATS_HISTORY_AVAILABILITY	Retrieves the earliest available time of historical statistics. Statistics for a time earlier than this time cannot be restored.
GET_STATS_HISTORY_RETENTION	Retrieves the retention interval for historical statistics.
GET_PARAM	Retrieves the default values of the parameters of the procedures in the DBMS_STATS package.
GET_PREFS	Retrieves the default value of the specified preferences (Prefs).
IMPORT_INDEX_STATS	Retrieves statistics for the specified index from the user statistics table and stores them in the dictionary.
IMPORT_COLUMN_STATS	Imports column-level statistics.
IMPORT_TABLE_STATS	Imports table-level statistics.
IMPORT_SCHEMA_STATS	Imports statistics for all tables in the specified schema.
LOCK_PARTITION_STATS	Locks statistics for the specified partition.
LOCK_SCHEMA_STATS	Locks statistics for all tables in the specified schema.
LOCK_TABLE_STATS	Locks statistics for the specified table.
RESTORE_TABLE_STATS	Restores table-level historical statistics at the specified time.
RESTORE_SCHEMA_STATS	Restores schema-level historical statistics at the specified time.
RESET_GLOBAL_PREF_DEFAULTS	Resets global preferences to their default values.
RESET_PARAM_DEFAULTS	Resets all parameters to their recommended database values.
PURGE_STATS	Deletes historical statistics before the specified time.
SET_COLUMN_STATS	Sets column-level basic statistics.
SET_INDEX_STATS	Sets index-related information.
SET_TABLE_STATS	Sets table-level basic statistics.
SET_GLOBAL_PREFS	Sets global statistics preferences.
SET_PARAM	Sets default values for parameters of procedures in the DBMS_STATS package.
SET_SCHEMA_PREFS	Sets statistics preferences for the specified schema.
SET_SYSTEM_STATS	Sets system statistics
SET_TABLE_PREFS	Sets statistics preferences for tables owned by the specified user.
UNLOCK_PARTITION_STATS	Unlocks statistics for the specified partition.
UNLOCK_SCHEMA_STATS	Unlocks statistics for all tables in the specified schema.
UNLOCK_TABLE_STATS	Unlocks statistics for the specified table.
CANCEL_GATHER_STATS	Used to cancel a statistics collection command.