Exporting and Importing Table Statistics

Oracle lets you export and import table statistics. This allows you to restore statistics from a given point in time. To illustrate this, first set the system date back a few months:

SQL> alter system set fixed_date = '01-MAR-2011'
  2  /

System altered.

SQL> select sysdate from dual
  2  /

SYSDATE
---------
01-MAR-11

SQL> 

Now create a table and analyze it:

SQL> create table andrews_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE');

PL/SQL procedure successfully completed.

SQL>
 

Check the date the table was last analyzed. This will match the sysdate set above:

SQL> select last_analyzed from user_tables
  2  where table_name = 'ANDREWS_TABLE'
  3  /

LAST_ANALYZED
-------------
01-MAR-11

SQL> 

Before exporting the statistics, you need to create a table to hold them:

SQL> exec dbms_stats.create_stat_table -
> (ownname => 'ANDREW', stattab => 'ANDREWS_STATS');

PL/SQL procedure successfully completed.

SQL> 

Now export the table's statistics. The stattab parameter tells Oracle where to store the statistics. The statid parameter gives this set of statistics a name within the table:

SQL> exec dbms_stats.export_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE', -
>  stattab => 'ANDREWS_STATS', statid => 'MAR2011');

PL/SQL procedure successfully completed.

SQL> 

And you can see this identifier by running a select against the table storing the statistics:

SQL> select distinct statid from andrews_stats
  2  /

STATID
------------------------------
MAR2011

SQL>
  
Now reinstate the sysdate:

SQL> alter system set fixed_date = 'NONE'
  2  /

System altered.

SQL> select sysdate from dual
  2  /

SYSDATE
---------
19-SEP-11

SQL> 

And analyze the table again:

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE');

PL/SQL procedure successfully completed.

SQL> /

SYSDATE
---------
19-SEP-11

SQL>

This sets the last_analyzed date to the new sysdate:

SQL> select last_analyzed from user_tables
  2  where table_name = 'ANDREWS_TABLE'
  3  /

LAST_ANALYZED
-------------
19-SEP-11

SQL>

dbms_stats.import_table_stats allows you to reimport the statistics which were exported earlier:

SQL> exec dbms_stats.import_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE', -
>  stattab => 'ANDREWS_STATS', statid => 'MAR2011');

PL/SQL procedure successfully completed.

SQL> 

This has the effect of resetting the last_analyzed  date to the earlier sysdate so that you can be sure that the correct statistics have been reimported:

SQL> select last_analyzed from user_tables
  2  where table_name = 'ANDREWS_TABLE'
  3  /

LAST_ANALYZED
-------------
01-MAR-11

SQL>