Export / Import Oracle Table/Index Stats

Source - Why Export Import optimizer statistics: 

Importing and exporting statistics for the CBO and the systems stats (external system statistics for CPU, I/O. etc) and useful in a variety of areas:

• Export production into test to make test systems "look like" large systems for execution plan generation".
• Export/imports can be used to control execution plans by "freezing execution plans".
• Statistics are used as a backup before re-analyzing a schema.
• System stats can be moved to a smaller server to make it appear as if Oracle is executing on a large fast server.         

- System stats:  When migrating to a new server, you can export the old system statistics to ensure consistent execution plans until you are ready to use the "real" system stats.

- Systems reverse:  Conversely, you can migrate system stats from production to test to make a tiny server appear to be a larger server.  This will not improve SQL execution speed, but developers will see the same execution plans that they would see in production

- Backup stats:  Before making any production change to the CBO stats with dbms_stats, take a full schema backup and an backup of your dbms_stats system stats.  Remember, the primary reason for re-analyzing stats is to change SQL execution plans.

For example, here we export production table stats and backport them to the test database to make it appear to be a larger table

Source - Steps



Source - STATs stale?

EX: 
* when table was last analyzed: select table_name, to_char(last_analyzed,'MM-DD-YYYY HH24:MI'),stale_stats from all_tab_statistics where STALE_STATS='YES'

* what tables got modified recently(MONITORING attribute must be Yes): 
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, to_char(timestamp,'MM-DD-YYYY HH24:MI') from dba_tab_modifications where timestamp > trunc(sysdate) and TABLE_OWNER='SYSADM'  
(delayed flushing; for immediate flush:  
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;)

During the gather_*_stats, FLUSH_DATABASE_MONITORING_INFO would have flushed the information ; it could wipe out the entry from dba_tab_modifications  instead

Source - Rebuild TABLE stats include INDEX stats?

1. create INDEX....
2. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'

3. exec dbms_stats.gather_table_stats(....)

4. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'
union
select table_name,  num_rows, last_analyzed from user_tables where table_name ='A'

Source - Restore Previous Stats
dba_histograms  
dba_tab_stats_history

exec DBMS_STATS.DELETE_TABLE_STATS
exec dbms_stats.restore_table_stats(...)
exec dbms_stats.import_table_stats