Rollback Table Statistics

In Oracle, we can bring back statistics from the past, which is very helpful when Oracle makes different decisions and starts to gather suboptimal statistics for specific tables.

When this happens, we can try changing the gather stats preferences and other troubleshooting methods. However, if you don't have much time, restoring old statistics can save your business.

First of all, we need to understand that this is an Enterprise Feature, so if you are running in Standard Edition, this option is not available.

By default, Oracle preserves 31 days of optimizer statistics details, after which they are purged. But no worries, we can modify this retention period.

We can use the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure to change this retention period. This setting determines how long historical statistics are retained in the database, which is crucial for performance tuning and troubleshooting. However, be aware that this will increase the size of your SYSAUX tablespace.

Checking the current history:

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

Checking the current setting:

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

Changing this setting:

SQL> begin DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(<number_of_days>); end;
/

This is how we can rollback the statistic.

SQL> SET PAGES 200 LIN 200
SQL> SET COLSEP '|'
SQL> COL TABLE_NAME FORMAT A30
SQL> COL STATS_MOD_TIME FOR A20
SQL> SELECT TABLE_NAME, TO_CHAR(STATS_UPDATE_TIME,'YYYY-MM-DD:HH24:MI:SS') AS STATS_MOD_TIME FROM DBA_TAB_STATS_HISTORY WHERE OWNER='&owner' AND TABLE_NAME='&table_name' ORDER BY STATS_UPDATE_TIME DESC;
SQL> begin DBMS_STATS.RESTORE_TABLE_STATS('&username', '&table_name',TO_TIMESTAMP('2024-07-06:21:30:49','YYYY-MM-DD HH24:MI:SS'), NO_INVALIDATE=>FALSE); end;
SQL> /