dbms_stats

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31 => this is the default value

If you like to change it to 45 days, then use the ALTER_STATS_HISTORY_RETENTION procedure

SQL> exec dbms_stats.alter_stats_history_retention(45);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_availability from dual; => this query will return the date statistics have been purged up to. 

GET_STATS_HISTORY_AVAILABILITY

---------------------------------------------------------------------------

18-APR-13 07.20.37.099029000 PM -05:00

SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = ‘GLS’;  è this query shows the time when  given tables’ statistics were re-gathered

  

TABLE_NAME                     STATS_UPDATE_TIME

------------------------------ ---------------------------------------

GLS_CONTACT_NEW                 19-MAY-13 09.56.39.845243 PM -05:00

GLS_CONTACT_NEW                 06-MAY-13 05.22.14.338999 PM -05:00

To restore table statistics:   For example, for GLS_CONTACT_NEW table, the statistics on May 19 is bad but May 6^th is good, so you like to restore it to May 6^th.

SQL> execute dbms_stats.restore_table_stats('GLS','GLS_CONTACT_NEW','06-MAY-13 05.22.14.338999 PM -05:00');

PL/SQL procedure successfully completed.

To compare statistics for a table from two timestamps or date in the past

select * from table(dbms_stats.diff_table_stats_in_history
(ownname => upper('&ownname'),
tabname => upper('&tabname'),
time1 => systimestamp,
time2 => to_timestamp('&time2','mm/dd/yyyy'),
pctthreshold => 0))

Whenever it is necessary, statistics can be restored from the history.  The dbms_stats provides:

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_schemas_stats(‘owner’,date)

Execute dbms_stats.restore_system_stats(date)

To set table statistics, you can use dbms_stats.set_table_stats

SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE', numrows=>140);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE',numblks=>10);
PL/SQL procedure successfully completed.

To set index statistics, you can use dbms_stats.set_index_stats to set number rows in index, number of leaf blocks, distinct keys, clustering factor, heigh of index 

exec dbms_stats.set_index_stats (ownname=>'GLS',tabname=>'PROD_NEW',numdist=>1000);