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.
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 in the specified schema to store statistics. |
| DELETE_CATALOG_GLOBAL_PREFS | Deletes a global preference from the catalog. |
| DELETE_CATALOG_TABLE_PREFS | This API is used to delete table-level preferences in a catalog. |
| 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 preferences Prefs for all tables under a specified schema. |
| DELETE_SYSTEM_STATS | Delete system statistics |
| DELETE_TABLE_PREFS | Delete the statistics collection preference Prefs for tables owned by a specified user. |
| DROP_STAT_TABLE | Delete the created user information statistics table. |
| EXPORT_COLUMN_STATS | Exports 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_CATALOG_TABLE_STATS | This API is used to collect statistics on external tables in the catalog. |
| GATHER_INDEX_STATS | Collects index statistics. |
| GATHER_TABLE_STATS | Gathers statistics for tables and columns. |
| GATHER_SCHEMA_STATS | Gathers statistics for all objects in the schema. |
| GATHER_SYSTEM_STATS | Collect system statistics |
| GET_CATALOG_GLOBAL_PREFS | Retrieves the value of a global preference from the catalog. |
| GET_CATALOG_PREFS | Retrieves the value of a table-level preference from the catalog. |
| GET_STATS_HISTORY_AVAILABILITY | Gets the earliest point in time from which historical statistics are currently available. Historical statistics earlier than this point 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 in the schema. |
| LOCK_CATALOG_PARTITION_STAT | Used to lock partition statistics in the catalog. |
| LOCK_CATALOG_TABLE_STAT | Used to lock external table statistics in the catalog. |
| 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 the 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 | Reset the global preferences Prefs to their default values. |
| RESET_PARAM_DEFAULTS | Reset the default values of all parameters to the database-recommended values. |
| PURGE_STATS | Delete historical statistics from a specified point in time. |
| SET_CATALOG_GLOBAL_PREFS | Sets the global preference value in the catalog. |
| SET_CATALOG_TABLE_PREFS | Sets the value of a table-level preference in the catalog. |
| 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_SYSTEM_STATS | Set system statistics |
| SET_TABLE_PREFS | Sets the statistics preferences Prefs for tables owned by a specified user. |
| UNLOCK_CATALOG_PARTITION_STAT | Used to unlock partition statistics in the catalog. |
| UNLOCK_CATALOG_TABLE_STAT | Used to unlock external table statistics in the catalog. |
| 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. |
| COPY_TABLE_STATS | Copies statistics from a source partition (or subpartition) to a target partition (or subpartition). |
| CANCEL_GATHER_STATS | Cancels the statistics collection command. |