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.

Automatic cleanup of statistics history

Since OceanBase Database is a distributed database, it cannot directly set up cleanup tasks for newly created tenants on the server. Therefore, after creating a tenant or upgrading a low-version server to a new version, you need to manually create a scheduled task using DBMS_SCHEDULER.CREATE_JOB to enable automatic cleanup.

## Set up a task to automatically clean up statistics history every day starting from now
DECLARE
BEGIN
   DBMS_SCHEDULER.CREATE_JOB(job_name => 'PRUGE_STATS',
                             job_type => 'PL/SQL Block',
                             job_action => 'call dbms_stats.purge_stats(NULL);',
                             repeat_interval => 'FREQ=DAILY;INTERVAL=1',
                             enabled => TRUE);
END;
/

The default cleanup interval for tasks is 31 days. This means that if a table has not been updated within 31 days (that is, the table's statistics have not been recollected or updated during this period), its historical statistics will be cleaned up on the 31st day. You can adjust this interval using the dbms_stats.alter_stats_history_retention() function, with a valid range of [0, 365000]. If set to 0, all historical statistics will be cleared, and no further records will be kept.

Manual cleanup of statistics history

Manual cleanup involves explicitly calling the dbms_stats.purge_stats() command to execute the cleanup. This method is suitable for scenarios where quick cleanup is required at a specific point in time.

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'));