Historical Optimizer Statistics

Sometime due to change in data volume or High volatility of data in table sub-optimal plan may be picked and to correct that OLD plan may be used for that query,

1=> Restoring Optimizer Statistics

You can use DBMS_STATS to restore old versions of statistics that are stored in the data dictionary.
Sometime we can restore OLD stats jus to compare and check the execution differences.

Restrictions for Restoring Optimizer Statistics

When restoring previous versions of statistics, various limitations apply.

Restrictions include the following:
DBMS_STATS.RESTORE_*_STATS procedures cannot restore user-defined statistics.
Old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics.
Dropping a table removes the workload data used by the automatic histogram feature and the statistics history used by DBMS_STATS.RESTORE_*_STATS.
Without this data, these features do not work properly. Therefore, to remove all rows from a table and repopulate it, 
Oracle recommends using TRUNCATE instead of dropping and re-creating the table.
If a table resides in the recycle bin, then flashing back the table also retrieves the statistics.

Procedures to restore Statistics
RESTORE_DICTIONARY_STATS
RESTORE_FIXED_OBJECTS_STATS
RESTORE_SCHEMA_STATS
RESTORE_SYSTEM_STATS
RESTORE_TABLE_STATS

Views Helpful in Restoring Statistics

Dictionary views display the time of statistics modifications. 
You can use the following views to determine the time stamp to be use for the restore operation:

The DBA_OPTSTAT_OPERATIONS view contains history of statistics operations performed at schema and database level using DBMS_STATS
The DBA_TAB_STATS_HISTORY views contain a history of table statistics modifications.

Demo
Let we restore OLD statistics for a table which is not performing up to the mark.
Current status of table statistics as below.

SQL> SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='EMP_DATA';

TABLE_NAME LAST_ANALYZED	  NUM_ROWS
---------- -------------------- ----------
EMP_DATA   28-MAY-2022 23:31:07        282

SQL> SELECT COUNT(*) FROM EMP_DATA;
  COUNT(*)
----------
       282

SQL> SELECT INDEX_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='IDX_EMPD';

INDEX_NAME	 LAST_ANALYZED		NUM_ROWS
-------------------------------------------------------------------------------------------------------
IDX_EMPD  28-MAY-2022 23:31:07	     282

Now let we check available stats for that table as below.

COL TABLE_NAME FORMAT a10
SELECT TABLE_NAME,
       TO_CHAR(STATS_UPDATE_TIME,'YYYY-MM-DD:HH24:MI:SS') AS STATS_MOD_TIME
FROM   DBA_TAB_STATS_HISTORY 
WHERE  TABLE_NAME='EMP_DATA'
AND    OWNER='ABHI_TEST'
ORDER BY STATS_UPDATE_TIME DESC;SQL> SQL>   2    3    4    5    6  

TABLE_NAME STATS_MOD_TIME
---------- -------------------
EMP_DATA   2022-05-28:23:31:07
EMP_DATA   2022-05-28:23:27:20
EMP_DATA   2022-05-28:23:26:59
EMP_DATA   2022-05-28:23:25:44
EMP_DATA   2022-05-28:23:24:08
EMP_DATA   2022-05-28:23:23:07
EMP_DATA   2022-05-28:17:03:04
EMP_DATA   2022-05-28:17:01:43
EMP_DATA   2022-05-28:16:59:42
EMP_DATA   2022-05-28:16:58:44
EMP_DATA   2022-05-28:16:57:02
EMP_DATA   2022-05-28:16:22:49
EMP_DATA   2022-05-28:16:21:57
EMP_DATA   2022-05-28:16:09:23
EMP_DATA   2022-05-28:16:08:37

15 rows selected.

As we can see we are also having stats on 2022-05-28:16:08:37.Let we change current stats to OLD one and check count and Histogram details as below.

SQL> BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS( 'ABHI_TEST','EMP_DATA', 
               TO_TIMESTAMP('2022-05-28:16:08:37','YYYY-MM-DD:HH24:MI:SS') );
END;
/
PL/SQL procedure successfully completed.

Now let we check NUM_ROWS and HISTOGRAM status for that table.

SQL> SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='EMP_DATA';

TABLE_NAME		       LAST_ANALYZED	      NUM_ROWS
------------------------------ -------------------- ----------
EMP_DATA		       11-SEP-2021 01:41:51	  1300

SQL> 
SQL> 
SQL>  SELECT INDEX_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='IDX_EMPD';

INDEX_NAME	LAST_ANALYZED		  NUM_ROWS
-------------------------------------------------
IDX_EMPD	04-JAN-2022 12:13:09  1300

SQL> SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='EMP_DATA';

TABLE_NAME		       COLUMN_NAME		      HISTOGRAM
------------------------------ -----------------------------
EMP_DATA		       ID			          NONE
EMP_DATA		       GENDER			      FREQUENCY

SQL> SELECT COUNT(*) FROM EMP_DATA;

  COUNT(*)
----------
       282
SQL> 

SO as we can see Table statistics got changed.

2=> Optimizer Statistics Retention Period

You can configure the retention period using the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure.
The default is 31 days. After which time the statistics are scheduled for purging.

Obtaining Optimizer Statistics History 
You can use the following procedure to obtain information about the optimizer statistics history:

GET_STATS_HISTORY_RETENTION -This function can retrieve the current statistics history retention value.

GET_STATS_HISTORY_AVAILABILITY - This function retrieves the oldest time stamp when statistics history is available.
                                                           Users cannot restore statistics to a time stamp older than the oldest time stamp.

Changing the Optimizer Statistics Retention Period

SQL> SET SERVEROUTPUT ON
SQL> 
DECLARE
  v_stats_retn  NUMBER;
  v_stats_date  DATE;
BEGIN
  v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
  DBMS_OUTPUT.PUT_LINE('The retention setting is ' || 
    v_stats_retn || '.');
  v_stats_date := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
  DBMS_OUTPUT.PUT_LINE('Earliest restore date is ' ||
    v_stats_date || '.');
END;
/SQL>   2    3    4    5    6    7    8    9   10   11   12  
The retention setting is 31.
Earliest restore date is 27-APRIL-2022 23:25:52.

PL/SQL procedure successfully completed.

SQL>

You can configure the retention period using the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure. The default is 31 days.

Here we are changing Retentions from 31 days to 80 days .We can do as below.

SQL> EXECUTE set_opt_stats_retention(80);

PL/SQL procedure successfully completed.

SQL> 

3=> Reporting on Past Statistics Gathering Operations
We can use below Functions to report OLD stats details.

REPORT_STATS_OPERATIONS
REPORT_SINGLE_STATS_OPERATION

Using REPORT_STATS_OPERATIONS to view all operations done for statistics related,

======================================================================

col myRptFile new_value myRptFile noprint
select 'dbms_stats_rpt_' || to_char(sysdate,'yyyymmdd_hh24miss') || '.html' myRptFile from dual;
var myRpt clob
begin
                :myRpt := dbms_stats.report_stats_operations (
                                since => systimestamp -1,
                                until => systimestamp,
                                detail_level => 'TYPICAL',
                                format => 'HTML'
                );
end;
/
set long 2000000
spool &myRptFile
print myRpt
spool off




Generates a report of the specified operation. REPORT_SINGLE_STATS_OPERATION

col myRptFile new_value myRptFile noprint
select 'dbms_stats_rpt_' || to_char(sysdate,'yyyymmdd_hh24miss') || '.html' myRptFile from dual;
var myRpt clob
begin
                :myRpt:=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION (
     OPID    => 889
,    FORMAT  => 'HTML'
);
end;
/
set long 2000000
spool &myRptFile
print myRpt
spool off
======