Backup, Restore, Create and Delete of stats / Statistics using DBMS_STATS package

Test system to be identical to production in terms of hardware and data size. This is not always possible, most commonly due to the size of the production environments. By copying the Optimizer statistics from a production database to any other system running the same Oracle version.Export and import stats from production to test environment.

Create or Gather stats for Schema, tables and indexes

Gather stats with auto sample size

--For Schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
--For Table
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP',DBMS_STATS.AUTO_SAMPLE_SIZE);
--For Index
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMP_IDX',DBMS_STATS.AUTO_SAMPLE_SIZE);

Gather stats with estimate percentage 20

--For Schema
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 20);
--For Table
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP',estimate_percent => 20);
--For Index
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMP_IDX',estimate_percent => 20);


Gather stats with cascade option(it's include stats for all the indexes)
--For Schema
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);
--For Table
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP',estimate_percent => 20,cascade => TRUE);
--For Index
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMP_IDX',estimate_percent => 20,cascade => TRUE);

Gather stats with degree(parallel) option(to increase the speed of process)
--For Schema
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE, Degree => 4);
--For Table
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP',estimate_percent => 20,cascade => TRUE, Degree => 4);
--For Index
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMP_IDX',estimate_percent => 20,cascade => TRUE, Degree => 4);

Delete the stats of schema, tables, columns in Oracle

Delete Schema Stats
EXEC DBMS_STATS.delete_schema_stats('SCOTT');

Delete Table Stats
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP');

 Delete Table Column Stats
EXEC DBMS_STATS.delete_column_stats('SCOTT', 'EMP', 'EMPNO');

 Delete Index Stats
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMP_IDX');

Backup and Restore Stats in Oracle

Step 1: Create a stat table to hold the schema statistics:

exec dbms_stats.create_stat_table('schemaname', 'table_name');
e.g:
EXEC DBMS_STATS.create_stat_table('HR','STATS');
PL/SQL procedure successfully completed.

Note: Upper command create a table name stats in HR Schema.

Step2: Export the HR schema statistics into the table STATS.

exec dbms_stats.export_schema_stats(ownname => 'HR',stattab => 'STATS');

Step3: Take backup of expdp/exp backup of table

expdp hr/hr directory=dbbackup tables=stats dumpfile=stats.dmp logfile=stats.log

Copying the dumpfile into the destination server

Step 4: Import the STATS table (if need to restore again or any other system)

impdp hr/hr directory=dbbackup full=y dumfile=stats.dmp logfile = stats.log

Step 5: Import stats into the schema HR

exec dbms_stats.import_schema_stats(ownname => 'HR',stattab => 'STATS');