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
