Pages

Wednesday, September 3, 2014

Moving SQL Profiles from one database to other in 11G.

Following the SQL truing advisor we implemented some profiles to particular SQLs in our database.
That significantly improved the performance but the challenge is to apply the same to all the environments.
Oracle provides DBMS_SQLTUNE with multiple options to perform related tasks.
Following are the steps we followed.

1.     Logion with SYSTEM and create a staging table for profile.

SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name => 'PROFILE_STGTAB');

2.    Check the profile name you need to move and package that in the staging table.

SQL> select name, status from dba_sql_profiles where name like 'SYS_SQLPROF_01483a%';
NAME                           STATUS
------------------------------ --------
SYS_SQLPROF_01483ab3b1530003   ENABLED
SYS_SQLPROF_01483aad5f6f0002   ENABLED

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_01483a%', staging_table_name => 'PROFILE_STGTAB');

% is the wildcard, giving only % may help packaging all the profiles.

3.    Export the staging table.
4.    Import the staging table to destination.
5.    Unpack the profiles from staging table.
This will overwrite existing profiles if replace will be TRUE in below command, or will throw error.

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_01483a%', replace => FALSE, staging_table_name => 'PROFILE_STGTAB');

6.    Verify status on Destination.

SQL> select name, status from dba_sql_profiles where name like 'SYS_SQLPROF_01483a%';
NAME                           STATUS
------------------------------ --------
SYS_SQLPROF_01483ab3b1530003   ENABLED
SYS_SQLPROF_01483aad5f6f0002   ENABLED

No comments:

Post a Comment