How to create SQL profiles using the SQLT utility.
Oracle has a utility called SQLT to create profiles.
To create profiles we do not need to install this utility it can be done by downloading and unzipping the utility. Refer to metalink note 215187.1. Once the utility is unzipped, you can call the profiler function by running this SQL. You need to be logged in as SYS.
@sqlt/utl/coe_xfr_sql_profile.sql
SQL> @sqlt/utl/coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
This will ask sqlid as input parameter.
Once the sqlid is provided, it will list all the plan hash values and their elapsed times.
Enter value for 1: crj0huynfjdz5
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
129850372 248.461
3675459451 2151.575
664690662 14074.004
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2:
Once the plan hash value is given, it creates the files necessary to create the profile.
Enter value for 2: 129850372
Execute coe_xfr_sql_profile_crj0huynfjdz5_129850372.sql
on TARGET system in order to create a custom SQL Profile
with plan 129850372 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>
If you execute the file coe_xfr_sql_profile_crj0huynfjdz5_129850372.sql, the profile for that hash value is created.
