KNOWLEDGE SHARING: EASY SQLPROFILE HOW TO

For the sql-profile, we usually do it via dbms_sqltune system package or simply do it through OEM. Here we have another more straight forward approach.

There is a scripts under sqlt,e.g

 

This sql can help us to generate sql-profile very easy:

coe_xfr_sql_profile.sql <sql_id> <good_plan_hash_value>

for the sql_id,good_plan_hash_value, I believe you can find it from the ourput of sqltrend.

As a result, another sql file will be generated,e,g

Run coe_xfr_sql_profile.sql g7scs5vbfrta8 317069062  as a result, coe_xfr_sql_profile_g7scs5vbfrta8_317069062.sql will be generated.
You can simply run the sql generated to apply the new sql-profile. And further more, in the new profile, you will be told how to rollback this change:

e.g
REM PARAMETERS
REM   None.
REM
REM EXAMPLE
REM   SQL> START coe_xfr_sql_profile_g7scs5vbfrta8_317069062.sql;
REM
REM NOTES
REM   1. Should be run as SYSTEM or SYSDBA.
REM   2. User must have CREATE ANY SQL PROFILE privilege.
REM   3. SOURCE and TARGET systems can be the same or similar.
REM   4. To drop this custom SQL Profile after it has been created:
REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_g7scs5vbfrta8_317069062');
REM   5. Be aware that using DBMS_SQLTUNE requires a license
REM      for the Oracle Tuning Pack.
REM