When you collect statistics without specifying an option, such as the degree of parallelism (DOP) and granularity in statistics collection, and the number of histogram buckets, the default options are used. The OceanBase Database optimizer allows you to configure preferences for statistics collection by modifying the default options.
The following table describes the collection preferences supported by the OceanBase Database optimizer.
| Preference name | Default value | Description |
|---|---|---|
| APPROXIMATE_NDV | TRUE | Specifies whether estimation is used for the calculation of the number of distinct values (NDV). |
| CASCADE | DBMS_STATS.AUTO_CASCADE | Specifies whether to enable cascaded collection. If you set the value to TRUE, index statistics on a table are collected along with the table statistics. |
| DEGREE | NULL, which indicates that the DOP is 1. | The DOP for statistics collection. |
| ESTIMATE_PERCENT | DBMS_STATS.AUTO_SAMPLE_SIZE | The percentage of data to be used to compute distribution features. |
| GRANULARITY | AUTO | The granularity in statistics collection. |
| INCREMENTAL | FALSE | Specifies whether to use the incremental collection strategy. |
| METHOD_OPT | FOR ALL COLUMNS SIZE AUTO | The histogram collection method at the column level. |
| STALE_PERCENT | 10 | The percentage of the changed data. If the percentage of the changed data exceeds the specified value, statistics are expired and re-collected. |
| STATS_RETENTION | 31 | The retention period for historical statistics. |
You can query and modify the collection preferences by using the following procedures provided by the DBMS_STATS package:
GET_PARAM: gets the default values of parameters for procedures in theDBMS_STATSsystem package.GET_PREFS: gets the default values of the preferences.RESET_GLOBAL_PREF_DEFAULTS: restores the preferences to default values for the current tenant.RESET_PARAM_DEFAULTS: resets the default values of all parameters to the values recommended by OceanBase Database.SET_GLOBAL_PREFS: sets preferences for the current tenant.SET_PARAM: sets default values for parameters of procedures in theDBMS_STATSpackage.SET_SCHEMA_PREFS: sets preferences for all tables under the schema.SET_TABLE_PREFS: sets table preferences.DELETE_SCHEMA_PREFS: deletes preferences for all tables under the schema.DELETE_TABLE_PREFS: deletes table preferences.
# Set the APPROXIMATE_NDV option to FALSE at the tenant level.
call dbms_stats.set_global_prefs('APPROXIMATE_NDV', 'FALSE');
# Query the value of the APPROXIMATE_NDV option of the current tenant.
select dbms_stats.get_prefs('APPROXIMATE_NDV') from dual;
# Restore all preferences for a tenant to default values.
call reset_global_pref_defaults();
# Set the DEGREE option to 128 for the T1 table under the TEST user.
call dbms_stats.set_table_prefs('TEST', 'T1', 'degree', '128');
# Query the default value of the DEGREE option for the T1 table under the TEST user.
select dbms_stats.get_prefs('degree', 'TEST', 'T1') from dual;
# Delete the DEGREE option for the T1 table under the TEST user.
call dbms_stats.delete_table_prefs('TEST', 'T1', 'DEGREE');