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
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
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.
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');
Pingback: Upgrade Oracle database from 10g to 11g version | Smart way of Technology