Posts Tagged ‘Create SQL Profile’

Moving an Oracle SQL execution plan from one environment to another

Posted: September 29, 2015 in Uncategorized
Tags: ,
10

I know there is more than one way to move a sql plan hash value from one environment to another but this way was simple and worked very efficiently for me. Give it a shot!

  • A few days ago, Developers started complaining, “This query is running very slow in production, faster in development, and never finishing in test.” Have you heard this complaint before?
  • If the query has run successfully in one environment (including the one you are executing it in), and table structure is the same across environments, you can create a sql profile than 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. There are a couple of ways to do this. In my case the sql_id was not in memory so I had to use “display_awr”…

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. SQL T has a great script to help create a sql profile that can be used in your other environments. If you don’t already have sql t installed, see Oracle Support Doc ID 1614107.1 Be advised, this link will only work if you have an Oracle Support account and a CSI#.
  • Step 3: Run this script in SQL Plus. It will prompt you for the sql_id and plan hash value.

@/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 to the 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’));

…

Note
—–
– Degree of Parallelism is 8 because of hint
– SQL profile coe_sqlid_planhash used for this statement

Success!!

Please let me know if you have any difficulties with this. I was surprised by the simplicity and ease of use for this product. SQL T has many more useful products for tuning but a friend of mine showed me this little nugget and made my day!

Thanks for reading!

Jason