Move SQL Profiles from One Database to Another

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Steps to copy profile from one DB to Another:

1.Creating a staging table to store the SQL Profiles

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'SQLPROFILE_STAGE_TABLE',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.

2.Pack the SQL Profiles into the Staging Table

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_category => '%',staging_table_name =>'SQLPROFILE_STAGE_TABLE',staging_schema_owner=>'SCOTT');

PL/SQL procedure successfully completed.

3. Take backup of staging table and copy dump to target db host

 expdp username/password dumpfile=EXPDP_SqlProfiles.dmp logfile=EXPDP_SqlProfiles.log DIRECTORY=ORACLE_BASE

4.Import staging table on target db 

 impdp username/password dumpfile=EXPDP_SqlProfiles.dmp logfile=IMPDP_SqlProfiles.log DIRECTORY=ORACLE_BASE

5.Unpack Staging Table

If importing to the same schema, schema owner does not need to be specified:

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'SQLPROFILE_STAGE_TABLE');

However, if importing to different schema, the staging schema owner needs to be changed:|

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'SQLPROFILE_STAGE_TABLE',staging_schema_owner => 'SCOTT');

PL/SQL procedure successfully completed.

6.Validate profiles on target side

select * from from DBA_SQL_PROFILES;