Restoring Table Statistics

Restoring Table Statistics (Doc ID 452011.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

PURPOSE

This document outlines the facility by which statistics may be restored following their removal. This facility is available from Orale 10g onwards.

SCOPE

Assist with restoring statistics in the event that they are overwritten.

DETAILS

Beginning with Oracle10G, when statistics are gathered for a table, the old statistics are retained so should
there be any problem with performance of queries dependent on those statistics, the old ones can be restored.

How long does Oracle retain the statistics for ?

The default period for which statistics are retained is 31 days but this can be altered with:-

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)

- where xx is the number of days to retain them

NOTE: There is an overhead of storage in the SYSAUX tablespace with statistics so care should be taken not to cause the tablespace to fill with the statistics

How do I know how many days the statistics are available for ?

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

- will return the number of days stats are currently retained for.

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

- will return the date statistics have been purged up to (so only dates newer then this can possibly be restored to). Any request to restore stats from this date or older will fail with: "ORA-20006: Unable to restore statistics , statistics history not available"

How do I find the statistics history for a given table ?

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history

Will show the times statistics were regathered for a given table.

How do I restore the statistics ?

Having decided what date you know the statistics were good for, you can use:-

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)

ie

execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');

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

How to Transfer Statistics from one Database to Another

SOLUTION

1. Create the STATS table

SQL> connect user/pwd
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=> 'user',stattab=>'STATS');

2. Export the statistics to the STATS table

Retrieving the statistics can be achieved using EXPORT_XXXX_STATS procedure:

• Exporting the statistics of all the objects in the database
  EXEC DBMS_STATS.EXPORT_DATABASE_STATS(stattab => 'STATS');
    
  Make sure you have logged in as SYS or SYSTEM to export database level statistics
  
• Exporting the statistics of all the objects in a schema
  EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'user', stattab => 'STATS');
    
  You need to have DBA privileges to export the statistics of other schemas.
  
• Exporting the statistics of tables
  EXEC DBMS_STATS.EXPORT_TABLE_STATS(<'username' or NULL>,'TAB1',NULL,'STATS');
    

3. Export the STATS table using export(exp) or datapump(expdp)

exp system/manager tables=STATS file=stats.dmp owner=log=stats.log

4. Transfer the dump file to the destination database

If you are using ftp to transfer the files make sure that you are transferring the file with binary option to avoid the dump file being corrupted.

5. Import the STATS table to the destination database

imp system/manager tables=STATS file=stats.dmp full=Y

6. Import the statistics into the data dictionary

The statistics can be imported using IMPORT_XXXX_STATS procedure:

• Importing the statistics for all the objects in the database
    
  EXEC DBMS_STATS.IMPORT_DATABASE_STATS(stattab => 'STATS');
    
• Importing the statistics to all the objects in a schema
  EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => user, stattab => 'STATS');
    
  
• Importing the statistics to tables
   
  EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname => user, tabname => 'TAB1', stattab => 'STATS');
    

7. Changing the schema name

Statistics cannot be directly exported from one schema and imported into a different schema.The schema names much match exactly.
If the target database schema is different from the source database schema, the schema name can be changed by updating column C5 of the STATS table:

update table STATS  set c5 = ''
where c5 = ''
and statid = ;

8. Upgrading statistics table

When importing statistics from an earlier version into a later version of Oracle, the structure of the statistics table may have changed.
Try executing DBMS_STATS.UPGRADE_STAT_TABLE to resolve this

Note:
When exporting and importing database objects, Datapump Export and Import utilities will also export and import optimizer statistics along with the tables even when a column has system-generated names.
Importing statistics will overwrite any previous statistics that existed for the table having statistics imported. For example, if previously there were histogram statistics and you import statistics that do not include histograms, there will no longer be any histogram information.


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