Browsing Historical Table and Column Statistics

Historical statistics for the past 31 days are stored in SYS tables/SYSAUX tablespaceon 10g and 11g Oracle databases. So whenever the nightly Statistics Gathering job regenerates statistics for an object, the the old value is stored in these history tables. Old historical stats past the time limit are purged as needed. The retention period can be modified using the ALTER_STATS_HISTORY_RETENTION procedure. Some ERP systems have a lot of turnover in statistics and might benefit from a shorter retention period.

Statistics Gathering Job History

Check the history of the GATHER_STATS_JOB

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

Table Stats History

 -- All stats history for user objects
 -- Sort: most recent at top
 SELECT do.owner, 
        do.object_name, 
        oth.* 
   FROM SYS.WRI$_OPTSTAT_TAB_HISTORY oth join dba_objects do on oth.obj#=do.object_id
  WHERE OWNER NOT IN ('SYS','SYSTEM')
  ORDER BY savtime desc;
-- Get historical Statistics info for 1+ Tables
WITH TableList as (select object_id, object_name 
               from dba_objects 
              where object_type='TABLE' 
                and object_name IN ('TABLE_1','TABLE_2',...)) 
select OBJECT_NAME as TABLE_NAME, 
       TO_CHAR(ANALYZETIME,'MM/DD/YYYY HH24:MI') as AnalyzeTime_Formatted,
       OTH.*
  From SYS.WRI$_OPTSTAT_TAB_HISTORY OTH, TableList 
 where OTH.OBJ# = TableList.OBJECT_ID
 ORDER BY table_name, analyzetime;

Histogram Stats History Summary

-- Get histogram summary info for 1 column
WITH TabColInfo AS (select OBJ#, COL# from sys.COL$ where OBJ# in (select object_id 
               from dba_objects 
              where object_type='TABLE' 
                and object_name='&my_table_name.')
              and NAME='&my_column_name.')
select SAVTIME, COUNT(BUCKET) From SYS.WRI$_OPTSTAT_HISTGRM_HISTORY HH, TabColInfo TCI
WHERE HH.OBJ#=TCI.OBJ# and HH.INTCOL#=TCI.COL# GROUP BY SAVTIME;