Query historical versions of statistics and roll back to a specified version

Currently, the OceanBase Database optimizer provides a historical version management feature for statistics. This feature is mainly used for querying historical versions of statistics and for rolling back statistics. When the status of statistics changes (such as collection, setting, deletion, import, and locking), the statistics prior to the change is saved to a historical statistics table. The historical statistics table allows querying the change history of statistics for a specific table and is also capable of restoring statistics to a specified historical version.

The following table describes the views that the OceanBase Database optimizer provided for you to query the historical versions of statistics.

In these views, the STATS_UPDATE_TIME field indicates the time when the statistics version was updated. To roll back the statistics of the specified version, you must first query the relevant view to obtain the version number, and then use the following procedures provided by the DBMS_STATS package:

• restore_table_stats: restores historical statistics on a table to the specified version.
• restore_schema_stats: restores historical statistics on a schema to the specified version.

By default, a version of historical statistics is retained for 31 days. You can query and modify the retention period by using the following procedures provided by the DBMS_STATS package:

• alter_stats_history_retention: modifies the retention period of historical statistics. The value range is [-1,365000]. If you set the value to -1, the statistics are never cleared. If you set the value to 0, the historical statistics retention is disabled.
• get_stats_history_retention: gets the retention period of historical statistics of the current version.

Historical statistics can be automatically cleared every day by scheduled tasks based on the retention period. You can also manually clear historical statistics by running the DBMS_STATS.PURGE_STATS('$time_stamp') command.

# Query the retention period of historical statistics of the current version.
select dbms_stats.get_stats_history_retention() from dual;

# Query the collection time of the earliest available historical statistics.
select dbms_stats.get_stats_history_availability() from dual;

# Change the retention period of historical statistics to 15 days.
call dbms_stats.alter_stats_history_retention(15);

# Restore statistics on the T1 table under the TEST user to a specific point in time.
call dbms_stats.restore_table_stats('TEST', 'T1', to_timestamp('2021-09-26 19:02:12.675729', 'YYYY-MM-DD HH24:MI:SS.FF'));

# Manually clear the historical statistics collected at the specified point in time.
call dbms_stats.purge_stats(to_timestamp('2021-09-26 19:02:12.675729', 'YYYY-MM-DD HH24:MI:SS.FF'));