The DBMS_STATS system package is used to view and modify the statistics collected by the optimizer for database objects.
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.
Delete 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 in the specified schema to store 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 in the specified schema. |
| DELETE_SCHEMA_PREFS | Delete the statistics collection preference Prefs for all tables under a specified schema. |
| 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 the schema. |
| 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 schema. |
| 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 statistics for all tables in the schema. |
| LOCK_PARTITION_STATS | Locks the statistics of partitions. |
| LOCK_SCHEMA_STATS | Locks the statistics of all tables in the schema. |
| 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 schema-level historical statistics to a specified point in time. |
| RESET_GLOBAL_PREF_DEFAULTS | Resets 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 | ForDBMS_STATSThe system package sets default values for the parameters of procedures. |
| SET_SCHEMA_PREFS | Sets the statistics preferences Prefs for a specified schema. |
| 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 in the schema. |
| UNLOCK_TABLE_STATS | Unlocks the statistics of a table. |