Sunday, December 17, 2017

Moving an Oracle SQL execution plan from one environment to another using SQLT

" A query is running very slow in production, faster in development, and never finishing in test.” Have you heard such issues before?

Here we can copy sql execution plan from good environment to problematic environment. There can be multiple ways to accomplish this. Let’s give a shot to SQLT !!

If the query has executed successfully in one environment and table structure is the same across environments, you can create a sql profile and that can be used in your other environments.

Step 1: Find the sql_id and plan hash value from the good environment you want to use to create your profile
#Query to get sql_id and PHV :-
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

Click "How to Determine the SQL_ID of a SQL Statement" for more details.

After getting SQL_ID , you can see the plan from below queries:-
select * from table(dbms_xplan.display_awr('SQL_ID'));  — run this if the sql_id is not in memory
select * from table(dbms_xplan.display_cursor('SQL_ID')); — use this if the sql_id is still in memory

Step 2: Create a SQL profile. SQLT has a great script to help create a sql profile that can be used in your other environments. If you don’t already have sqlt installed, see Oracle Support Doc.

Step 3: Run this script in SQL Plus. It will prompt you for the sql_id and plan hash value.
sql> @/home/oracle/sqlt/utl/coe_xfr_sql_profile.sql
This script will produce a file similar to coe_<sqlid>_planhash.sql.

Step 4: Copy this script "coe_<sqlid>_planhash.sql" to the target environment you want to create the profile in and run it.
sql> @coe_<sqlid>_planhash.sql

Step 5: Run your query again and verify the execution plan is now using the newly created sql profile!
select * from table(dbms_xplan.display_cursor('SQL_ID'));
 – Degree of Parallelism is n because of hint
 – SQL profile coe_<sqlid>_planhash used for this statement

From this example it's very clear that SQLT is very simple approach to move execution plan between databases.

Related Links :-
How to Determine the SQL_ID of a SQL Statement

1 comment:

  1. Thanks for this. I was going to use a baseline but that's a bit more awkward.
    This seems a lot easier.