Manage statistics history

The statistics history management feature can save statistics information status to a statistics history table when the statistics status changes, such as being recollected, set, deleted, imported, or locked. In the statistics history table, you can query the historical statistics changes of a table and restore the statistics information of a table to a specified historical version.

Statistics history tables and views

The OceanBase Database optimizer uses the following views to store statistics history:

• ALL_TAB_STATS_HISTORY, DBA_TAB_STATS_HISTORY, and USER_TAB_STATS_HISTORY store table-level historical statistics.

• ALL_TAB_COL_STATISTICS, DBA_TAB_COL_STATISTICS, and USER_TAB_COL_STATISTICS store column-level historical basic statistics.

• ALL_TAB_HISTOGRAMS, DBA_TAB_HISTOGRAMS, and USER_TAB_HISTOGRAMS store column-level historical histogram statistics.

The fields of a table are basically the same as the fields in the internal table that stores statistics, except that the internal table has the savtime column, which records the time when the statistics status changes. You can also query the following views for historical changes in statistics.

Functions for statistics history management

OceanBase Database supports the following functions for statistics history management:

• restore_table_stats: restores the table-level statistics to a specified time point.

• restore_schema_stats: restores the schema-level statistics to a specified time point.

• purge_stats: deletes historical statistics before a specified time point.

• alter_stats_history_retention: changes the retention period of historical statistics. The default retention period is 31 days.

• get_stats_history_retention: obtains the current retention period of historical statistics.

• get_stats_history_availability: obtains the earliest historical statistics available currently. The statistics cannot be restored to any version earlier than this version.

The definitions are as follows:

PROCEDURE restore_table_stats (
  ownname               VARCHAR2,
  tabname               VARCHAR2,
  as_of_timestamp       TIMESTAMP WITH TIME ZONE,
  restore_cluster_index BOOLEAN DEFAULT FALSE,
  force                 BOOLEAN DEFAULT FALSE,
  no_invalidate         BOOLEAN DEFAULT FALSE
);

PROCEDURE restore_schema_stats (
  ownname               VARCHAR2,
  as_of_timestamp       TIMESTAMP WITH TIME ZONE,
  force                 BOOLEAN DEFAULT FALSE,
  no_invalidate         BOOLEAN DEFAULT FALSE
);

PROCEDURE purge_stats(
  before_timestamp      TIMESTAMP WITH TIME ZONE
);

PROCEDURE alter_stats_history_retention(
  retention             NUMBER
);

FUNCTION get_stats_history_retention RETURN NUMBER;

FUNCTION get_stats_history_availability RETURN TIMESTAMP WITH TIME ZONE;

The following table describes the parameters.

Strategies of statistics history cleanup

OceanBase Database supports automatic and manual cleanup of statistics history.

OceanBase Database is a distributed database. Therefore, you cannot create data cleanup tasks when you create a tenant on the server. You need to use the DBMS_JOB package to create a scheduled task on the tenant created or upgraded from an earlier version to automatically clean up the statistics history. Here is an example:

## Set a task to automatically clean up statistics history every other day from now on.
DECLARE
   jid BINARY_INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jid, 'dbms_stats.purge_stats(NULL);', trunc(sysdate), 'trunc(sysdate) + 1');
   COMMIT;
END;

The default cleanup interval is 31 days. You can use dbms_stats.alter_stats_history_retention() to set the interval. The value range is [0, 365000]. If the value is set to 0, all historical statistics are cleared and no subsequent statistics changes will be recorded.

To manually clean up statistics history, call the dbms_stats.purge_stats() command.

Examples

• Obtain the retention period of the historical statistics.

  SELECT dbms_stats.get_stats_history_retention() FROM DUAL;
  

• Obtain the 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 the statistics on table tbl1 under the user test to a specific point in time.

  CALL dbms_stats.restore_schema_stats('test', 'tbl1', to_timestamp('2021-09-26 19:02:12.675729', 'YYYY-MM-DD HH24:MI:SS.FF'));
  

• Manually purge the historical statistics 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'));