The DBMS_STATS package allows you to view and modify statistics collected by the optimizer for database objects.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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 under the specified user 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 of the specified user. |
| DELETE_SCHEMA_PREFS | Deletes a statistics preference from the statistics on all tables of the specified user. |
| DELETE_SYSTEM_STATS | Deletes system statistics. |
| 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 of the specified user. |
| 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 the statistics on all objects of the specified user. |
| GATHER_SYSTEM_STATS | Collect system statistics. |
| 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 of the specified user. |
| LOCK_PARTITION_STATS | Locks the statistics on a partition. |
| LOCK_SCHEMA_STATS | Locks the statistics on all tables of the specified user. |
| LOCK_TABLE_STATS | Locks the statistics on a table. |
| RESTORE_TABLE_STATS | Restores the table-level historical statistics as of the specified point in time. |
| RESTORE_SCHEMA_STATS | Restores the user-level historical statistics as of the specified point in time. |
| RESET_GLOBAL_PREF_DEFAULTS | Resets global preferences to 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 a statistics preference for the specified user. |
| SET_SYSTEM_STATS | Sets system statistics. |
| 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 of the specified user. |
| UNLOCK_TABLE_STATS | Unlocks the statistics on a table. |
| COPY_TABLE_STATS | Copies the statistics of the source partition (or subpartition) to the destination partition (or subpartition). |
