skip to main content

SAP NetWeaver Newbie

Restoring Previous Table Statistics on Oracle Database

If you suspect that a recent update of table statistics has caused performance problems, you may want to restore the previous statistics.
Check the statistics history to confirm if there was indeed a change in table stats:
select stats_update_time from user_tab_stats_history where table_name = 'MSEG';
Take a backup of current statistics, in case you anticipate a restore:
Create a temporary stat table to hold the current statistics:
exec dbms_stats.create_stat_table ('SAPR3', 'MSEG_NEW_STAT');
Export the current table stats to the temporary stat table:
dbms_stats.export_table_stats('SAPR3', ‘MSEG’, NULL, ‘MSEG_NEW_STAT’, NULL, true, 'SAPR3');
Restore the old stats (by time):
execute dbms_stats.restore_table_stats ('SAPR3','MSEG','19-SEP-10 PM +08:00');

If you have been facing problems every time there is a stat update, you can disable update using the following command:

exec dbms_stats.lock_table_stats('SAPR3', 'MSEG');

If you wish to undo the restore to older statistics:
dbms_stats.import_table_stats('SAPR3', ‘MSEG’, NULL, ‘MSEG_NEW_STAT’, NULL, true, 'SAPR3');

No comments:

Post a Comment

Email Subscription

Get every new post into your inbox by subscribing us.

Want a reason to subscribe?
1. This sitemap might convince you to subscribe.
2. We do not misuse email IDs. We respect privacy.

© 2008 - 2017 sapnwnewbie. All rights reserved.