Oracle Performance Tuning: Use Sql Profile to Transfer Optimizer Execution Plan to Another Database
Changing Execution Plans
There is often a need to change an execution plan. Oftentimes, we want to use an execution plan from another database. For example, the plan might be correct in the Test or QA environment, and we want that identical execution plan in Production.
There are several ways to accomplish this, via sql hints, stored outlines, or sql profiles. For example, you can create a stored oultine on the "good" database, export a stored outline, transfer the dump file, then import the outline to the problem database. Similarly, if you have a sql profile on the good database, you can call a package to place the profile in a staging table, then export the staging table.
All of the above methods work--but they require several steps. You have to spend time getting the exact details of the export/import right, and make sure you don't import objects you don't want. If you goof up any of them up, your execution plan will be incorrect.
Also, there is a special danger when you import stored outlines. Oracle will launch a secret process that will delete any outlines that don't have a correct "hint count." (I have never seen Oracle document this process--I discovered it the hard way.)
A Much Easier Way
And it Works!
I recently discovered a method which is incredibly easy, and avoids all the complications above. This method is based on an Oracle "Center of Excellent," (COE) script, created years ago by Carlos Sierra. This script is part of the SQLT package that is delivered with the database software.
The script name is: coe_xfr_sql_profile.sql.
I find it convenient to have a copy of this script on my desktop computer, The script is self-contained--you don't need to copy over a bunch of other scripts.
I have never found the SQLT package very useful--except for this one script. For our purposes, we will use the script named above-but we will switch up the method a little bit. Here's how it works:
(1) Go to "Good" Database Having Desired Plan
You need to have some database that has correctly runs the problem sql at some time in the past. It doesn 't even have to be the current execution plan. There just has to be a record (in the AWR tables) of at least one good run with a good execution plan.
(2) Prepare a Sql Profile Script (but don't run it!)
Run the Center of Excellence Script. It will prompt you for the sql_id, so be ready with that entry. Then, the script will show you all the historical runtimes based on different Plan Hash Values. Of course, you will normally select the PHV that gives the fastest runtime. (Best to avoid any PHV that shows a blank for the runtime; that could mean the sql never finished using that PHV!)
After you provide the sql_id and PHV, the script will create a build-sql-profile script. This script will contain the various hints that are needed to accomplish the optimizer plan for the desired PHV.
This all just takes a few seconds. At the end, you will see the name of the build-profile script. It will have the sql_id + PHV appended, so it's easy to find in your directory.
Important! Here's the trick--Do not run that script! That's right--we are NOT going to create a sql profile on the good database.
(3) Go to the Database Having the Wrong Plan
Now that you have the profile-create script ready, disconnect from the good database, and go to the problem database needing the corrected plan.
(4) Create Sql Profile using Script
Here is the payoff: In Sql*Plus, as a DBA, run the script that was created from the other database. This takes just a few seconds. You will see a confirmation message about, "Sql Profile TBD Created."
That's it! Try it and see how simple it is. No export, no imports, no copying files. Nothing.
Finally, I always like to check separately to make sure the sql profile does indeed exist. I usually sort all the sql profiles by create date, so I can easily spot the new one. Naturally, you will want to confirm that the execution plan has changed to the desired plan.
I have had great success with this method. Best of all, it is simple!
Yes, the Execution Plan is Correct!
