How to replace sql plan by another sql id sql plan

We have a condition where a query is performing poorly and sql tuning advisor is not helping, we dont have a good plan also, so a custom sql profile can also not be created. The only solution is adding manual hints in the query but it is not possible to change the code. In this post, we will see how we can achieve the solution to this problem by replacing sql plan with another sql id sql plan

How to replace sql plan with another sql id sql plan

Step 1
Make sure that both the original and modified statement are in memory. This can be achieved by executing the queries. Find the sql_id and plan_hash_value for both the original sql (without hints) and the modified sql (with hints)

Step 2

• Download the sqltxplain tool if not already
• Change directory to sqlt/utl

Step 3

Oracle has provided two scripts to achieve this

coe_load_sql_baseline.sql / coe_load_sql_profile.sql

coe_load_sql_baseline.sql: It works using sql plan management. Loads a plan from the modified sql with hints into a custom sql plan baseline of the original SQL (without hints)
coe_load_sql_profile.sql: This is a custom sql Profile. Loads the plan from the modified sql with hints sql into a custom sql profile of the original SQL (without hints)

We will check on sql profile in this post

Step 4

Suppose the two sql are

4f75765ab7rd 1872589290
select /*+ FULL (EMP) */ emp_name from emp_data where emp_name=’name’

3119d8bxvrcr 2045807146
select emp_name from emp_data where emp_name=’name’

We want to impose plan hash value 1872589290 on sql id 3119d8bxvrcr

Step 4

connect to a system user and execute the script

@coe_load_sql_profile.sql

It will ask first for the original sql_id, then for the modified sql_id, and then display the plan hash value of the modified sql_id, you need to choose that and then it will create the sql profile

Example

SQL> @coe_load_sql_profile.sql

Parameter 1:

ORIGINAL_SQL_ID (required)
Enter value for 1: 3119d8bxvrcr

Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 4f75765ab7rd

PLAN_HASH_VALUE AVG_ET_SECS
-----------     -----------
1872589290               .1 

Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 2872589290


Values passed to coe_load_sql_profile
ORIGINAL_SQL_ID: "3119d8bxvrcr"
MODIFIED_SQL_ID: "4f75765ab7rd"
PLAN_HASH_VALUE: "1872589290"
.
.



coe_load_sql_profile completed.

I hope this will be useful for performance tuning.