How To Save Database Statistics Before They Are Updated

Step-by-step guide

Exporting statistics is a three step process. The result is a single table containing schema statistics (table, index and column) and system statistics (workload and non-workload).

The first step creates a physical version of a StatTable. The StatTable is a consolidated table to hold all types of statistics, so the format is very generic. First, create an instance of the table using DBMS_STATS.CREATE_STAT_TABLE

begin
dbms_stats.CREATE_STAT_TABLE( ownname=>user
, stattab=>'MY_STATS_TABLE');
end;
/

The next two steps are to export data from the current schema. The column STATID identifies a particular set of statistics within this table. It is possible to do multiple exports into a single StatTable by using a different STATID. In this case I am using “CURRENT_STATS” as the STATID.

Export the Table, Index, and Column Statistics

begin
dbms_stats.export_schema_stats( ownname=>user
, stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
end;
/

Then Export system statistics (sys.aux_stats$)

begin
dbms_stats.export_system_stats( stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
end;
/

If we look at the contents of MY_STATS_TABLE, we will see rows for each different statistic type (T=Table, I=Index, C=Column, S=System)

select statid, type, count(*)
from my_stats_table
group by statid, type
/

This table can be exported (Export or Datapump) and imported into another database. If the schema is the same, then the statistics can be imported. (Remember to clear the shared pool anytime statistics are updated)

begin
dbms_stats.import_schema_stats( ownname=>user
, stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
end;
/
begin
dbms_stats.import_system_stats( stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
end;
/

In this way we can guarantee that queries are using the same statistics for optimization. This is the case even if the underlying data is different. Oracle only uses the stored statistics to perform query optimization (except in the case where there are no statistics; then dynamic sampling is used). This technique is an invaluable way to share optimizer statistics and diagnose query plan problems.

Lets see how long historic statistics are kept for.

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
31

If we want to restore the statistics, we can perform the following:

To find statistics history of a particular table:

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;

execute DBMS_STATS.RESTORE_TABLE_STATS (‘owner’,’table’,date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS(‘owner’,date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)

Example:
exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'<SCHEMA_NAME>’,tabname=>'<TABLE_NAME>’,as_of_timestamp=>TO_DATE(’15-JAN-2019 19:01:30′, ‘DD-MON-YYYY HH24:MI:SS’));