The DBMS_STATS package allows you to view and modify statistics collected by the optimizer for database objects.
Considerations
In the current version of OceanBase Database, the DBMS_STATS package supports the following operations:
Collect statistics from the optimizer.
Set information about columns, tables, and indexes.
Delete statistics.
Lock and unlock statistics.
Subprograms
The following table describes the DBMS_STATS subprograms supported by the current OceanBase Database version.
| Subprogram | Description |
|---|---|
| ALTER_STATS_HISTORY_RETENTION | Changes the retention period of historical statistics. The default retention period 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 index-related statistics. |
| DELETE_TABLE_STATS | Deletes table-level statistics. |
| DELETE_SCHEMA_STATS | Deletes the statistics on all tables in the specified schema. |
| DELETE_SCHEMA_PREFS | Deletes the statistics preferences of all tables in the specified schema. |
| DELETE_TABLE_PREFS | Deletes the statistics preferences of a table owned by the specified user. |
| DROP_STAT_TABLE | Drops a user statistics table. |
| EXPORT_COLUMN_STATS | Exports column-level statistics. |
| EXPORT_INDEX_STATS | Retrieves statistics on a specified index and stores them in a statistics table. |
| EXPORT_TABLE_STATS | Exports table-level statistics. |
| EXPORT_SCHEMA_STATS | Exports the statistics on all tables in a schema. |
| FLUSH_DATABASE_MONITORING_INFO | Flushes the memory monitoring information of all tables to the dictionary. |
| GATHER_INDEX_STATS | Collects index statistics. |
| GATHER_TABLE_STATS | Collects statistics on tables and columns. |
| GATHER_SCHEMA_STATS | Collects statistics on all objects in a schema. |
| GET_STATS_HISTORY_AVAILABILITY | Obtains the time of the earliest available historical statistics. You cannot restore historical statistics that are earlier than this time. |
| GET_STATS_HISTORY_RETENTION | Obtains the retention period of the historical statistics. |
| GET_PARAM | Obtains the default values of parameters of procedures in the DBMS_STATS package. |
| GET_PREFS | Obtains the default value of a specified preference. |
| IMPORT_INDEX_STATS | Retrieves the statistics on the specified index from a 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 the statistics on all tables in a schema. |
| LOCK_PARTITION_STATS | Locks the statistics on a partition. |
| LOCK_SCHEMA_STATS | Locks the statistics on all tables in a schema. |
| LOCK_TABLE_STATS | Locks the statistics on a table. |
| RESTORE_TABLE_STATS | Restores the table-level historical statistics at the specified point in time. |
| RESTORE_SCHEMA_STATS | Restores the schema-level historical statistics of the specified point in time. |
| RESET_GLOBAL_PREF_DEFAULTS | Resets global preferences to their default values. |
| RESET_PARAM_DEFAULTS | Resets the default values of all parameters to the recommended values. |
| PURGE_STATS | Purges historical statistics saved before the specified timestamp. |
| SET_COLUMN_STATS | Sets column-level basic information. |
| SET_INDEX_STATS | Sets related information of an index. |
| SET_TABLE_STATS | Sets table-level basic information. |
| SET_GLOBAL_PREFS | Sets a global statistics preference. |
| SET_PARAM | Sets the default value for a parameter of procedures in the DBMS_STATS package. |
| SET_SCHEMA_PREFS | Sets the statistics preferences in the specified schema. |
| SET_TABLE_PREFS | Sets a statistics preference of a table owned by the specified user. |
| UNLOCK_PARTITION_STATS | Unlocks the statistics on a partition. |
| UNLOCK_SCHEMA_STATS | Unlocks the statistics on all tables in a schema. |
| UNLOCK_TABLE_STATS | Unlocks the statistics on a table. |