Restore old good Statistics for a table

Optimizer Statistics:
-----------------------
we do not simply overwrite old optimizer statistics by gathering new. Instead, the old optimizer statistics are automatically historized and kept for one month by
default, so that they can be restored easily if that should be desired.

SQL> set timing on
SQL> select amount_sold from sales where id=4712;
AMOUNT_SOLD
-----------
 5000
Elapsed: 00:00:01.92

SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd:hh24:mi:ss';
Session altered.

SQL> select table_name,stats_update_time from user_tab_stats_history;
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
SALES                          2010-05-18:09:47:16
SALES                          2010-05-18:09:47:38
SALES                          2010-05-18:09:52:30

The second row represents the accurate statistics. I am going to restore them:

SQL> begin
dbms_stats.restore_table_stats('ADAM','SALES',
to_timestamp('2010-05-18:09:47:38','yyyy-mm-dd:hh24:mi:ss'));
end;
/
PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables;
 NUM_ROWS     BLOCKS
---------- ----------
 29933962     119585

The good old statistics are back, but the statements are still in the library cache and may execute with the same misguided execution plan:

SQL> set timing on
SQL> select amount_sold from sales where id=4712;
AMOUNT_SOLD
-----------
 5000
Elapsed: 00:00:01.92

Still full table scan! The following empties the shared pool and forces the computation of new execution plans, using the accurate statistics:

SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04

SQL> set timing on
SQL> select amount_sold from sales where id=4712;
AMOUNT_SOLD
-----------
 5000
Elapsed: 00:00:00.01