The DBMS_STATS system package is used to view and modify the statistics collected by the optimizer for database objects.
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL-compatible mode.
Usage instructions for DBMS_STATS
The current version of OceanBase Database supports the following regular operations for the DBMS_STATS system package:
Collect optimizer statistics.
Set column, table, and index information
Drop statistics
Lock and unlock statistics
Overview of DBMS_STATS Subprograms
The following table lists the DBMS_STATS subprograms supported by the current version of OceanBase Database and provides brief descriptions.
Subprogram |
Description |
|---|---|
| ALTER_STATS_HISTORY_RETENTION | The retention interval for modification history statistics. The default value is 31 days. |
| CREATE_STAT_TABLE | Creates a table among the specified users for storing statistics. |
| DELETE_COLUMN_STATS | Drops column-level statistics. |
| DELETE_INDEX_STATS | Delete index-related statistics. |
| DELETE_TABLE_STATS | Deletes table-level statistics. |
| DELETE_SCHEMA_STATS | Drops the statistics of all tables for a specified user. |
| DELETE_SCHEMA_PREFS | Delete the statistics collection preference Prefs for all tables under a specified user. |
| DELETE_TABLE_PREFS | Delete the statistics collection preference Prefs for tables owned by a specified user. |
| DROP_STAT_TABLE | Delete the created user statistics table. |
| EXPORT_COLUMN_STATS | Export column-level statistics. |
| EXPORT_INDEX_STATS | Retrieves statistics for a specified index and stores them in the statistics table. |
| EXPORT_TABLE_STATS | Export table-level statistics. |
| EXPORT_SCHEMA_STATS | Export the statistics of all tables under a user. |
| FLUSH_DATABASE_MONITORING_INFO | Refreshes the memory monitoring information of all tables into the dictionary. |
| GATHER_INDEX_STATS | Gathers index statistics. |
| GATHER_TABLE_STATS | Gathers statistics for tables and columns. |
| GATHER_SCHEMA_STATS | Gathers statistics for all objects in the user. |
| GET_STATS_HISTORY_AVAILABILITY | Gets the earliest point in time from which historical statistics are currently available. Historical statistics from a time earlier than this cannot be restored. |
| GET_STATS_HISTORY_RETENTION | Retrieves the retention interval for current historical statistics. |
| GET_PARAM | Used to obtainDBMS_STATSThe default value of a parameter in a system package procedure. |
| GET_PREFS | Gets the default value for the specified preference Prefs. |
| IMPORT_INDEX_STATS | Retrieves statistics for a specified index from the user information statistics table and stores them in a dictionary. |
| IMPORT_COLUMN_STATS | Import column-level statistics. |
| IMPORT_TABLE_STATS | Import table-level statistics. |
| IMPORT_SCHEMA_STATS | Import the statistics of all tables under the user. |
| LOCK_PARTITION_STATS | Locks the statistics of partitions. |
| LOCK_SCHEMA_STATS | Locks the statistics of all tables for the user. |
| LOCK_TABLE_STATS | Locks the statistics of a table. |
| RESTORE_TABLE_STATS | Restores table-level historical statistics to a specified point in time. |
| RESTORE_SCHEMA_STATS | Restores user-level historical statistics to a specified point in time. |
| RESET_GLOBAL_PREF_DEFAULTS | Resets the global preferences Prefs to their default values. |
| RESET_PARAM_DEFAULTS | Reset all parameter defaults to the database-recommended values. |
| PURGE_STATS | Delete historical statistics from a specified point in time. |
| SET_COLUMN_STATS | Sets the basic statistics at the column level. |
| SET_INDEX_STATS | Sets the index information. |
| SET_TABLE_STATS | Sets the basic statistics at the table level. |
| SET_GLOBAL_PREFS | Sets global statistics preferences Prefs. |
| SET_PARAM | To beDBMS_STATSThe system package sets default values for the parameters of procedures. |
| SET_SCHEMA_PREFS | Sets the statistics preferences Prefs for a specified user. |
| SET_TABLE_PREFS | Sets the statistics preferences for tables owned by the specified user. |
| UNLOCK_PARTITION_STATS | Unlocks partition statistics. |
| UNLOCK_SCHEMA_STATS | Unlocks the statistics for all tables of the user. |
| UNLOCK_TABLE_STATS | Unlocks the statistics of a table. |
| COPY_TABLE_STATS | This statement is used to copy statistics from a source partition (or subpartition) to a target partition (or subpartition). |