How to retrieve previous table statistics using Oracle dbms_stats

Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.

1. Statistics restore

This method uses the procedure dbms_stats.restore_%_stats, where % can be table, schema, database, and fixed_objects.
This is an example for a restoration of a table with definite timestamp. The starting situation is:

Statistics info:

SQL> select table_name, num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS')from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS     LAST_ANALYZED
------------------------------ -------------     -------------------
TAB1                                    5                     22.02.2011 10:20:07

Histograms info:

select table_name, column_name, count(*) 
  from dba_histograms  
 where owner='SCHEMA1'
   and table_name='TAB1'
 group by table_name, column_name;TABLE_NAME               COLUMN_NAME            COUNT(*)
-----------------           --------------------        ----------
TAB1                           ADRESS                        2
TAB1                           NAME                           2
TAB1                           NPA                              2
TAB1                           ID                                 2
TAB1                           LOCALITE                      2

Now, let’s remove the statistics:

exec DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCHMEMA1', TABNAME=>'TAB1');
Procedure PL/SQL terminate with success.

Checking information:

SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS   LAST_ANALYZED
------------------------------ -------------   -------------------
TAB1SQL> select table_name, column_name, count(*)
  2    from dba_histograms
  3   where owner='SCHEMA1'
  4     and table_name='TAB1'
  5   group by table_name, column_name;TABLE_NAME                     COLUMN_NAME            COUNT(*)
----------------                   --------------------       ----------
TAB1                                  ID                                2

Now, let’s have a look which statistics are available:

select count(*), stats_update_time
  from dba_tab_stats_history  
 where owner='SCHEMA1'
   and table_name='TAB1'
 group by stats_update_time;  COUNT(*)     STATS_UPDATE_TIME
-------------    ---------------------------------------------------------------------------
         1           11/02/11 21:00:47,054000 +01:00
         1           22/02/11 10:20:07,587000 +01:00
         1           22/02/11 10:25:12,524000 +01:00
  9 rows selected.

Let’s try to restore the second line:

SQL> exec dbms_stats.restore_table_stats(ownname=>'SCHEMA1',tabname=>'TAB1',AS_OF_TIMESTAMP=>'22/02/11 10:20:07,587000 +01:00');Procedure PL/SQL terminate with success.

Now, I could check if the previous statistics were retrieved correctly:

SQL> select table_name, column_name, count(*)
  2    from dba_histograms
  3   where owner='SCHEMA1'
  4     and table_name='TAB1'
  5   group by table_name, column_name;TABLE_NAME                COLUMN_NAME            COUNT(*)
----------------             --------------------         ----------
TAB1                           ADRESS                         2
TAB1                           NAME                            2
TAB1                           NPA                               2
TAB1                           ID                                  2
TAB1                           LOCALITE                       2
SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS   LAST_ANALYZED
------------------------------ -------------   -------------------
TAB1                                    5                  22.02.2011 10:20:07

The disadvantage with this method is knowing which timestamp should be restored. In the following case, the solution is allowed to put a tag. It is therefore easier to restore the desired statistics.

2. Statistics export

An alternative is to store the statistics in a table and associate it with a tag.
Here are the steps:

1) Create the statistics table

exec dbms_stats.create_stat_table('SCHEMA1','STATS');

2) Export the statistics

exec dbms_stats.export_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);This procedure could be also used to extract statistics from the production to feed that of the test.

3) Check statistics information

Statistics info:

SQL> select table_name, num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS')from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS     LAST_ANALYZED
------------------------------ -------------     -------------------
TAB1                                    5                     22.02.2011 10:20:07

Histograms info:

select table_name, column_name, count(*) 
  from dba_histograms  
 where owner='SCHEMA1'
   and table_name='TAB1'
 group by table_name, column_name;TABLE_NAME               COLUMN_NAME            COUNT(*)
-----------------           --------------------        ----------
TAB1                           ADRESS                        2
TAB1                           NAME                           2
TAB1                           NPA                              2
TAB1                           ID                                 2
TAB1                           LOCALITE                      2

4) Delete statistics

exec DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCHEMA1', TABNAME=>'TAB1');SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS   LAST_ANALYZED
------------------------------ -------------   -------------------
TAB1

5) Import statistics

exec dbms_stats.import_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);

The statid parameter is allowed to store several evolutions of statistics and thus the capacity to go through the time.
Here the name is ‘TAG1_TAB1’, thus I could have ‘TAG2_TAB1’, ‘TAG3_TAB1’,.. too.

6) Check if statistics are corrects

SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
TAB1                                    5 22.02.2011 10:20:07

I will come back on this topic in another posting.