DBMS_STATS.export import:How to transfer oracle statistics between databases

advertisements

_____________________________________________________________________________________________________________________

Overview:

Oracle database statistics are very import in Cost Based optimizer (CBO). Sometimes the execution plan differs from production database to test databases even though the table, index structures are same. The execution plan might be good in production database compare to the test database. DBAs might get the complaints from the testers. In this situation DBAs can export the schema or table statistics to the test database.

Steps to transfer the optimizer statistics: TRANS schema stats from Prod to Test

1. Create the statistics table. DBAs can use any of the below mentioned syntax

a.       exec dbms_stats.create_stat_table(ownname => 'TRANS', stattab => 'schema_stats_bkup', - >tblspace => 'USERS');

b.      exec dbms_stats.create_stat_table('TRANS','schema_stats_bkup',’);

2. Export the TRANS schema statistics to the schema_stats_bkup table

·         exec dbms_stats.export_schema_stats(‘TRANS’, 'schema_stats_bkup',’);

3. Take export of schema_stats_bkup table to a dump file

·         exp file=exp_schema_stats_bkup.dmp log=exp_schema_stats_bkup.log tables=TRANS.schema_stats_bkup

4. Transfer the dump file exp_schema_stats_bkup.dmp to target test database using FTP/SCP and import the table into test database

·         Imp file=exp_schema_stats_bkup.dmp log=imp_schema_stats_bkup.log full=y

5. Import the table stats to the TRANS schema in Test database after creating the stats table. Before import it is recommended to take the backup of current statistics from the Test database

·         exec dbms_stats.create_stat_table(ownname => 'TRANS', stattab => 'schema_stats_bkup', - >tblspace => 'USERS');

·         exec dbms_stats.import_schema_stats(‘TRANS’, 'schema_stats_bkup',’);

6. Drop the statistics table if it is not need

·         exec DBMS_STATS.DROP_STAT_TABLE(‘TRANS’, 'schema_stats_bkup',’);

_____________________________________________________________________________________________________________________

Website Stats