10.2.0.4 came with some 11g features, an extended dbms_stats package. I had made some basic demonstraions below.



begin dbms_stats.delete_table_stats(ownname => user, tabname => 'T2'); commit; end;
/

select 'Stats history available from date '||
       to_char(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'dd/mm/yy')||
       ' with '||DBMS_STATS.GET_STATS_HISTORY_RETENTION||' days retention'
from dual;

-- test table
drop table t2 purge;
create table t2
nologging
as
select *
from all_objects
where rownum  USER, tabname => 'T2' );
commit; end;
/
select * from ALL_TAB_STATS_HISTORY atsh where atsh.owner = USER and atsh.table_name = 'T2';
select * from all_tab_statistics ats where ats.owner = USER and ats.table_name = 'T2';

-- change data
update t2 set t2.data_object_id = data_object_id*100, owner = USER, t2.timestamp = null;
commit;

begin dbms_stats.gather_table_stats(ownname => USER, tabname => 'T2' );
commit; end;
/

-- report all change of stats available in history
declare
      l_report clob;
begin

      for x in (select * from(select atsh.*, rownum rn
                       from ALL_TAB_STATS_HISTORY atsh
                       where atsh.owner = USER
                       and atsh.table_name = 'T2'
                       order by atsh.stats_update_time
                       ) where rn > 1
                       )
      loop
          select report
          into  l_report
          from table(dbms_stats.diff_table_stats_in_history(USER,'T2',x.stats_update_time));

          dbms_output.put_line(l_report);
      end loop;
end;
/

This was historical comparision. An objects stats is stored on DB, with a certain retention limit. Here is basic usage of stat table based comparision.

-- example table
drop table t1 purge;
create table t1
nologging
as
select *
from all_objects
where rownum  USER, stattab => 'STAT_TAB1');
      exception when others then dbms_output.put_line('stat_tab1 not exits'); end;
      begin
             dbms_stats.drop_stat_table(ownname => USER, stattab => 'STAT_TAB2');
      exception when others then dbms_output.put_line('stat_tab2 not exits'); end;
      dbms_stats.create_stat_table(ownname => USER, stattab => 'STAT_TAB1');
      dbms_stats.create_stat_table(ownname => USER, stattab => 'STAT_TAB2');
      commit;
end;
/

-- gather stats
begin
      dbms_stats.gather_table_stats(ownname => USER,tabname => 'T1',stattab => 'STAT_TAB1',estimate_percent => 100);
      dbms_stats.gather_table_stats(ownname => USER,tabname => 'T1',stattab => 'STAT_TAB1',estimate_percent => 100);
end;
/

-- what does the stat table contain?
declare
      l_numrows number;
      l_numblks number;
      l_avgrlen number;
begin
      dbms_stats.get_table_stats(ownname => USER, tabname => 'T1', stattab => 'STAT_TAB1', numrows => l_numrows,numblks => l_numblks,avgrlen => l_avgrlen);
      dbms_output.put_line('l_numrows:'||l_numrows||' l_numblks:'||l_numblks||' l_avgrlen:'||l_avgrlen);
end;
/

-- some change
update t1 set t1.data_object_id = data_object_id*100, owner = USER, t1.timestamp = null;
commit;

-- gather stats, STAT_TAB2
begin
      dbms_stats.gather_table_stats(ownname => USER,tabname => 'T1',stattab => 'STAT_TAB2',estimate_percent => 100);
      dbms_stats.gather_table_stats(ownname => USER,tabname => 'T1',stattab => 'STAT_TAB2',estimate_percent => 100);
      commit;
end;
/

declare
      l_numrows number;
      l_numblks number;
      l_avgrlen number;
begin
      dbms_stats.get_table_stats(ownname => USER, tabname => 'T1', stattab => 'STAT_TAB2', numrows => l_numrows,numblks => l_numblks,avgrlen => l_avgrlen);
      dbms_output.put_line('l_numrows'||l_numrows||' l_numblks'||l_numblks||' l_avgrlen'||l_avgrlen);
end;
/

-- compare two stats
select * from table(
       dbms_stats.diff_table_stats_in_stattab(USER,'T1','STAT_TAB1', 'STAT_TAB2',90)
       );
       

I was about to write a handy package that compares historical stats metadata of tables. I was late again... These packages "should" be considered after migrations and upgrades, with the help of exportable stats.