Oracle Performance: How to Create a Sql Profile Based on Historical Execution Plan
Performance tuning specialists have many tools at their disposal. For example, I have used stored outlines and sql profiles many times. Each has some advantages. I normally use stored outlines to change an execution plan without any need to actually re-code anything.
Stored Outlines have one specific disadvantage, however, which I suspect is not widely known. When you invoke an outline, the optimizer will strip off any parallel hints in the sql. Thus, if you need parallelism, an outline will not be a good choice.
Sql profiles, on the other hand do not remove parallelism. I have used profiles many times as well, but I have found the Sql Tuning Advisor, on which they are based, to be "hit or miss." In my experience, it is very common for a Sql Tuning Set to offer no good suggestions, or even fail to complete any useful analysis.
There is an overlooked tool, developed by Carlos Sierra, formerly of Oracle's Center of Excellence, that offers some special benefits. With this tricky, but simple to use script, you can create a sql profile, built on any historical plan that is available.
The script looks in dba_hist_sql_plan to grab the plan that you are want to use. Before you run this script, decide which Plan Hash Value you want for your Sql_id. You can check the performance of the various plans by querying Dba_Hist_SqlStat.
Select Snap_Id, Instance_Number,
Plan_Hash_Value, Px_Servers_Execs_Delta ,
Executions_Delta, Rows_Processed_Delta,
Round(Elapsed_Time_Delta/1000/(Executions_Delta+.01),1) Timepermsec,
Round(Disk_Reads_Delta) Disk
From Dba_Hist_Sqlstat S
Where Sql_Id = [TBD] Order By 1
Once you have the PHV ready, you are ready to go! When you execute the script, it will prompt for a Sql_id. Then, it will display the various Plan Hash Values that are available in the historical plan tables. You select one of these plans; then, the script automatically figures out the hints that are needed, and creates a custom sql profile that uses those hints
I recently had a performance tuning issue where the sql at one time had run well, but not anymore. The sql invoked parallelism, so that nixed the idea of a stored outline. Running the usual Sql Tuning set brought up no practical suggestions.
At this point, I remembered this script and gave it a try. I used this script and it worked perfectly. In about 1 minute, I had a correct, active sql profile that worked perfectly! Amazingly simple.
You run the script like this (you can optionally provide the sql_id and PHV) to avoid being prompted for these values:
@coe_xfr_sql_profile.sql
In just a few seconds, the hints will be ready. Then, you run a script of this form:
@coe_xfr_sql_profile_[sqlid]_[phv].sql
Although the script is packaged with SQLT, you can run the script as a standalone. Here is the actual script. I was delighted to find that it runs fine on my client; I did not need to transfer it to the server:
Even though the script mentions running as Sysdba, I found that running this script with my usual DBA account worked fine. Carlos reminds the user that you need to have the proper license to use Oracle's Tuning Pack.
So all in all, I think this script does a great job of solving some tough performance issues. Hats off to Carlos for doing the hard work to develop this tool.
Biking photos courtesy of Agberto Guimaraes
