SQL Patch I
March 6, 2012 2 Comments
I know I wasn’t the only one to be intrigued by the recent blog article by the Oracle Optimizer team on injecting hints using a SQL Patch.
If you’ve read the article, you’ll know that creating a SQL Patch requires the use of the undocumented package DBMS_SQLDIAG_INTERNAL which is part of the SQL Repair Advisor.
Now, whilst creating a SQL Patch may be undocumented, altering and dropping a SQL Patch are documented in the DBMS_SQLDIAG package reference.
A follow-up post by the Oracle Optimizer team has since started to address some of the questions and promises to reveal more in the next few weeks.
Some of the questions that sprang to my mind on first reading the article were:
Q. Is this supported?
A. From answers so far, this is still unclear but without a doubt the feature is as good as undocumented.
Q. What is the license situation with a SQL Patch?
A. It’s a standard part of the SQL Repair Advisor which is part of 11g Enterprise Edition, no extra licensing like Diagnostic+Tuning pack required.
Q. Why/When would you use a SQL Patch rather than a SQL Plan Baseline?
For me, the latter has been the big question mark.
Below is my opinion based on what I’ve read and played with so far.
I think part of the confusion is related to the title and premise of the original article – “Using SQL Patch to add hints to a packaged application”. The main illustration and link to previous post concerned the BIND_AWARE hint – that, I believe, was the primary use case – injecting a single hint – being illustrated by the article, not that a SQL Patch is necessarily the best mechanism for changing execution plans for packaged application code.
To my mind, in most circumstances SQL Plan Baselines are the prefered mechanism for changing the executions plans without touching the source code. The API DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE is ideal for this as shown in this article.
But… you can’t use a baseline to inject the BIND_AWARE hint (I’ll touch on that a bit in my next post).
It all comes down to slightly different intentions and use cases.
SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline. If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright.
On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc (and when you see the wealth of wrong results bugs, it’s not surprising that such a feature has been implemented).
If you were in such a crash or wrong results scenario, maybe you can start to see why you might want the lighter touch intervention of a SQL Patch over the more prescriptive SQL Plan Baseline.
In my next post, I hope to touch on some of these internal differences and show how in a very limited set of circumstances you could have a patch and a baseline applied to the same SQL.