Backup, Restore, Create and Delete the stats using DBMS STATS package in Oracle

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.
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');

 

Advertisements

One thought on “Backup, Restore, Create and Delete the stats using DBMS STATS package in Oracle

  1. Pingback: Upgrade Oracle database from 10g to 11g version | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s