Moving an Oracle SQL execution plan from one environment to another

Posted: September 29, 2015 in Uncategorized
Tags: ,

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

Advertisements
Comments
  1. Van says:

    Thanks for the tip, it worked great

  2. karthik says:

    i have the same issue, test is 12c(good with plan) and qa is 11g (bad with plan) so followed the same process but the QA plan is not showing as good Test again. can you please let me know on this.

    • Hi Karthik!
      I have not tried this going from 12C to a prior release. This is worth opening a support ticket to see if they have a new approach for SQL T in 12c. Please let us know what you find out.
      Thanks!
      Jason

  3. Nataraj Chindam says:

    To perform the migration, do we need license for SQLT

  4. mdm says:

    Is it possible to move a plans from 11g (11.2.0.4.0) to 12g (12.1.0.2.0)?

  5. mdm says:

    No it isn’t possible (tried it from 11 to 12)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s