Copy sql profile/baseline from one sql id to another for Oracle database out of a modified SQL using coe_load scripts

 

We came across user requirement where sql profile and sql baseline from 1 sql id needs to  be copied to another sqlid . 

SQL_ID1 and SQL_HANDLE1 with a poorly performing Execution Plan with Hash Value PHV1. With a small modification to this query, like adding a CBO Hint or removing one, we obtain query Q2, which performs well, and has SQL_ID2, SQL_HANDLE2 and PHV2. So what we want it to associate PHV2 to SQL_ID

This can be done using coe_load_sql_baseline.sql / coe_load_sql_profile.sql   provided under Metalink (Doc ID 1400903.1 ) 

coe_load_sql_baseline.sql / coe_load_sql_profile.sql are scripts provided with the SQLT tool in the "utl" folder.

Have stored  both scripts  below  

coe_load_sql_profile.sql :   ( we can get  from util directory under sqlt) 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/02/coeloadsqlprofilesql-as-per-doc-id.html

coe_load_sql_baseline.sql :

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/02/coeloadsqlbaselinesql-as-per-doc-id.html

References:

Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT) (Doc ID 1400903.1)

All About the SQLT Diagnostic Tool (Doc ID 215187.1)