1. Backup the current Stats
For Database stats:
SQL> Exec dbms_stats.export_database_stats(statown => ‘SYSTEM’, stattab => ‘prod_stats_database_03282018’);
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats(statown => ‘SYSTEM’, stattab => ‘prod_stat_system_03282018’);
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats(statown => ‘SYSTEM’, stattab => ‘prod_stats_dictionary_03282018’);
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats(statown => ‘SYSTEM’, stattab => ‘prod_stats_fixed_03282018’);
2. Update the stats.
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(‘interval’, interval => 45);
END;
/
EXEC DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’);
exec dbms_stats.gather_fixed_objects_stats;
Restore the backup stats as part of backout plan.
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS (stattab => ‘prod_stats_database_03282018’,statown => ‘SYSTEM’);
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => ‘prod_stat_system_03282018’,statown => ‘SYSTEM’);
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS
(stattab => ”prod_stats_dictionary_03282018”,statown => ‘SYSTEM’);
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS
(stattab => ‘prod_stats_fixed_03282018’,statown => ‘SYSTEM’);
Loading...
