Moving Oracle SQL Profiles

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 11.2.0.3 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;

Here you can see that there are 2 profiles that need to be moved with the database.
listing_sql_profile

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.

Advertisements

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