All About Statistics In Oracle Database (11g & 12c)

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’);

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 5.00 out of 5)
Loading...

Add Comment