X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Using SQL Patch to add hints to a packaged application

From Oracle Database 12c Release 2 onwards there's a public API call to create SQL patches using DBMS_SQLDIAG.CREATE_SQL_PATCH. If you're using this release you should check out this post too.

In the last post, I showed how you can use the BIND_AWARE hint to skip the monitoring phase of adaptive cursor sharing.  If you have a packaged application, you might be wondering how you can use this hint if you can't edit a query directly.  In this post I'll who you how to do just that, for any hint (or set of hints).  In order to do this, we will create a SQL patch for a query.  We haven't said much, if anything, about SQL patches here on the blog before.  A SQL patch is a SQL manageability object that can be generated by the SQL Repair Advisor, in order to circumvent a plan which causes a failure.  In essence, a SQL patch tells the optimizer to change the plan in some way or avoid an optimization so that the failure does not occur.  For instance, if a failure happens during index access, then generating a plan without that index (or without any index) may work around the failure.  You can read more about this in the documentation here.

What we don't tell you in the documentation is that you can create your own SQL patch, which will tell the optimizer to use a set of hints when it optimizes a particular query.  You can use this trick to essentially insert hints into a query whose text you cannot edit.  You can do this with the function dbms_sqldiag_internal.i_create_patch.  This function lets you specify a query text and a set of hints to apply to the query.  I'll start with an example using the same query from the BIND_AWARE hint post.

Recall from the last post, we saw that if we ran the query without the BIND_AWARE hint, the first child cursor to be generated would not be bind aware.  But with the BIND_AWARE hint, we get a bind-aware child cursor from the start:


Now let's create the patch for this query.  The hint that we want to apply is simply "BIND_AWARE".

And then see what happens when we run the original query, without the hint.

I cleared the cursor cache before that last step, to make it easier to read.  If I hadn't done that, you should still see a new cursor, due to STB_OBJECT_MISMATCH.

The BIND_AWARE hint is pretty simple, but you can use all kinds of hint text with this function.  The hint text just needs to work at the top level of the query.  So, for instance, if you want to hint an access path inside of a view, you can use global query block names to do this. Consider this query, which picks a full table scan by default:

Let's say for some crazy reason you want to use an index instead.  If you wanted to hint the query with global query block names, it would look like this:

So if you want to create a patch for this query instead, you would use that same hint text:

And now if we run the query or use explain plan to generate the plan, we pick the index:

And we even include a note in the plan that tells you that the SQL patch was used.

So now you have enough rope to hang yourself.  As you may have noticed, we generally aren't proponents of hinting your queries; we've talked about some of the perils of hints here before.  But we know there are legitimate cases where you need to add hints, and now you can do this even with a packaged application in which you cannot edit the queries directly.

You can get a copy of the script I used to generate this post here.
 

Join the discussion

Comments ( 14 )
  • Jagjeet Tuesday, February 28, 2012

    Thanks for sharing, awesome tip, I did not find any option to pass as sqlid, might be difficult for long sqls with literals ..


  • Dominic Brooks Tuesday, February 28, 2012

    Allison,

    This is really interesting.

    Now, as you mentioned, the SQL Patch exists primarily for the SQL Repair Advisor. But here you have used it to inject some hints into an application much like you might use a SQL Plan Baseline.

    Are there any reasons why you would pick a SQL Patch over a baseline?

    Thanks.

    Cheers,

    Dominic


  • Heitor Tuesday, February 28, 2012

    Hello,

    Does this package dbms_sqldiag_internal need any additional license?

    Thanks,

    Heitor


  • guest Tuesday, February 28, 2012

    Thank you for sharing the usage of the procedure SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(). We tested the procedure and it works as expected. This is very useful.


  • guest Tuesday, February 28, 2012

    Thanks very much for very useful info sharing, Allison

    How have "SQL Patch" to interact with other STB object - SQL Plan Baseline?

    I mean, if I try to add some hints (using method described above) to a particular query with already allowed SQL Plan Baseline, have this to modify or to disallow the existent Baseline?

    And if we add some access path hints to query, have this to prevent the execution plan evolution by Cardinality Feedback / Adaptive Cursor Sharing technologies?


  • Wolfgang Breitling Tuesday, February 28, 2012

    Is that a free feature or does the use of the dbms_sqldiag_internal function require the diagnostic pack license?


  • guest Thursday, March 1, 2012

    That's cool. But there is no way to use sql_id. So it's not as useful in real life.


  • guest Wednesday, March 28, 2012

    Great feature! Is there a way to make it do force matching when literals are used rather than bind variables?


  • guest Tuesday, June 26, 2012

    Any chance you could also upload the demo pieces in text format?


  • guest Tuesday, June 26, 2012

    Any chance you could also upload the demo pieces in text format?


  • Maria Colgan Tuesday, July 3, 2012

    I have included a link to the script I used to generate these commands to the end of the post.


  • Timur Akhmadeev Wednesday, September 12, 2012

    Hi

    it would be really nice:

    1) to have a version of this procedure which accepts SQL_ID

    2) to be able to create patches for SQL by FORCE_MATCHING_SIGNATURE

    Thanks


  • guest Tuesday, October 2, 2012

    Very nice!

    I would also prefer an interface with SQL_Id, but a simple wrapper accessing the full SQL text in V$SQL via SQL_ID would do the job, ...

    Trank you!

    /K


  • Sean Saturday, November 3, 2012

    I found out the way using SQL patch to add hint text is bit different from adding hints into the SQLs themselves. e.g. 'full(t1)' will make a FTS against the table t1. However, if just simply put 'full(t1)' into the hint text parameter in I_CREATE_PATCH, it won't work though Oracle tell you the patch is being used. To make the patch work, 'full(@sel$1 t1)' must be placed instead.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services