Document Type | Technical Information
Category | Tuning
Applicable Product Versions | 6FS06, 6FS07, 6FS07PS, 7FS02, 7FS02PS
Document Number | TTUTI003
Overview
This explains how to recover previously applied table statistics information.
Method
Statistics History Table Information (default retention 31 days): SYS._STAT_TBL_HISTORY
Statistics history query (per table, ANALYZETIME column shows the time when statistics were collected)
SELECT DU.USERNAME , DO.OBJECT_NAME , SH.* FROM DBA_OBJECTS DO , DBA_USERS DU , SYS._STAT_TBL_HISTORY SH WHERE DO.OBJECT_ID = SH.OBJ_ID AND DO.OWNER = DU.USERNAME AND DO.OBJECT_TYPE = 'TABLE' AND DU.USERNAME = '<owner>' AND DO.OBJECT_NAME = '<table_name>' ORDER BY TIMESTAMP ;
How to restore table statistics to a specified time (as_of_timestamp)
Verify statistics restoration: DBA_TABLES, DBA_INDEXES check the LAST_ANALYZED date of the table and confirm it matches the ANALYZETIME timestamp in _STAT_TBL_HISTORY.
EXEC DBMS_STATS.RESTORE_TABLE_STATS('<owner>', '<table_name>', TO_TIMESTAMP('2023/12/11 09:49:04', 'YYYY/MM/DD HH24:MI:SS'), force=>TRUE);
CautionWhen executing, you must select a time 1 second later than the statistics collection time for it to be applied.