Whenever you migrate a database it’s a good idea to move those SQL profiles that have forced the optimizer to a more efficient execution plan. I found that this wasn’t as easy as I had expected, so I thought I would share the results of my research. In my case I’mm migrating from 10.2.0.5 to 22.214.171.124 on AIX.
Once you’ve already established a good SQL profile in a database moving the profile is actually not as difficult as it may seem.
You can view these profiles with the following SQL Query.
--Listing a profile COLUMN category FORMAT a10 COLUMN sql_text FORMAT a20 SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;
In order to move the profiles you need to create a table on the source database to hold the information using the DBMS_SQLTUNE package.
--Create staging table BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( table_name => 'PROFILE_STAGING_TABLE' , schema_name => 'DBAMGR' ); END; / PL/SQL procedure successfully completed.
Then “pack” the profiles into the staging table.
--Export profiles into staging table BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( profile_name => 'SYS_SQLPROF_0149ed3eeff64000' , staging_table_name => 'PROFILE_STAGING_TABLE' , staging_schema_owner => 'DBAMGR' ); END; / BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( profile_name => 'SYS_SQLPROF_0249eecaf0c70000' , staging_table_name => 'PROFILE_STAGING_TABLE' , staging_schema_owner => 'DBAMGR' ); END; / PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
Once you’ve completed the packing routine for each profile then simply move the staging table to the target database however you choose. I do this by export/import processes, but the choice is yours.
Once the staging table is in place then “unpack” the profiles into the target database.
--Unpack profile(s) into target BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF( replace => true , staging_table_name => 'PROFILE_STAGING_TABLE' ); END; / PL/SQL procedure successfully completed.
You can then re-run the list query to validate that the profiles are now in the target database.
If you need to drop a profile the same package can be utilized to drop a profile.
-- Drop a profile BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE ( name => 'SYS_SQLPROF_025514b59c930000' ); END; /
*NOTE: There may be license requirements in order to use SQL profiles to tune a query. You’ll need to check before deploying these techniques.