Export stats from production to testing environment for make similar environment for Performance testing

1. It helps to check the performance issue going on production.
2. It used for testing purpose if any new deployment is gone on production environment.
3. Check the execution plan of query and move to performance server to run advisory reports.

Steps of Activity:

On Production Environment:

1. Create the stats table on the production environment.

exec dbms_stats.create_stat_table('HR','EMPSTATISTICS');

2. Verify the objects created into the production environment.

select object_name,owner,object_type from dba_objects where object_name = 'EMPSTATISTICS';

3. Run the dbms_stats package to export the stats of the schema to table.

 begin
 dbms_stats.export_schema_stats(ownname => 'HR',stattab => 'EMPSTATISTICS');
 end;
 /

4. Export the table with expdp utility.

expdp tables=hr.empstatistics directory=dbbackup dumpfile=empstatistics.dmp logfile=empstatistics.log

5. Drop the table from the production environment.

 exec dbms_stats.drop_stat_table('HR','EMPSTATISTICS');

6. Ftp or Move the dump file to the TEST environment.

On Test Environment:

7. Import the table to the testing environment.(if your schema is different then remap_schema is used in impdp)

impdp directory=dbbackup dumpfile=empstatistics.dmp logfile=empstatistics.log

8. Import the stats into the schema in which you want. (I created new schema HR_NEW similar to HR so i am importing into HR_NEW);

exec dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'EMPSTATISTICS');

9. Error if you got the following error:

ORA-20000: no statistics are imported

We can try for one table also as command given below:

EXEC DBMS_STATS.import_table_stats(ownname => 'HR_NEW',tabname => 'EMP',stattab => 'EMPSTATISTICS');
Error:
ORA-20000: no statistics are imported.
SQL> EXEC DBMS_STATS.import_table_stats(ownname => 'HR_NEW',tabname => 'EMP',stattab => 'EMPSTATISTICS');
BEGIN DBMS_STATS.import_table_stats(ownname => 'HR_NEW',tabname => 'EMP',stattab => 'EMPSTATISTICS'); END;
*
 ERROR at line 1:
 ORA-20005: object statistics are locked (stattype = ALL)
 ORA-06512: at "SYS.DBMS_STATS", line 3784
 ORA-06512: at "SYS.DBMS_STATS", line 4617
 ORA-06512: at "SYS.DBMS_STATS", line 12758
 ORA-06512: at line 1

Solution:
You need to unlock the schema stats first to override the error occurred above.

exec dbms_stats.unlock_schema_stats('HR_NEW');

If still getting error after it you need to check the username if its different in production and test environment you need to follow following steps:
If your user name is different then the production database then you need to update the column value of empstatistics table for resolved it.

select c5 from empstatistics;

empstatistics having production statistics and c5 column contain user name of production you need to modified it to new test environment username HR_NEW

 update empstatistics set c5 = 'HR_NEW';
 commit;

10. IF you version of oracle is different test environment is upper version the following error occur:ORA-20002

 SQL> exec dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'HRSTATISTICS');
 BEGIN dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'HRSTATISTICS'); END;
*
 ERROR at line 1:
 ORA-20002: Version of statistics table HR_NEW.EMPSTATISTICS is too old. Please
 try upgrading it with dbms_stats.upgrade_stat_table
 ORA-06512: at "SYS.DBMS_STATS", line 11648
 ORA-06512: at "SYS.DBMS_STATS", line 11665
 ORA-06512: at "SYS.DBMS_STATS", line 12800
 ORA-06512: at line 1

Solution:

 SQL> exec dbms_stats.upgrade_stat_table(ownname => 'HR_NEW',stattab => 'EMPSTATISTICS');

11. You can simple try again the import schema stats into new user:

 SQL> exec dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'EMPSTATISTICS');

12. Now you can do your testing on new stats similar to production environment
like execution plan, how sql query perform after deployment, check advisory report.