Using SQL Patch to add hints to a packaged application

In my 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.

Comments:

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

Posted by Jagjeet on February 28, 2012 at 01:42 AM PST #

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

Posted by Dominic Brooks on February 28, 2012 at 01:45 AM PST #

Hello,

Does this package dbms_sqldiag_internal need any additional license?

Thanks,

Heitor

Posted by Heitor on February 28, 2012 at 06:13 AM PST #

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.

Posted by guest on February 28, 2012 at 11:47 AM PST #

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?

Posted by guest on February 28, 2012 at 01:39 PM PST #

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

Posted by Wolfgang Breitling on February 28, 2012 at 02:37 PM PST #

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

Posted by guest on March 01, 2012 at 11:11 AM PST #

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

Posted by guest on March 28, 2012 at 01:41 PM PDT #

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

Posted by guest on June 26, 2012 at 04:10 PM PDT #

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

Posted by guest on June 26, 2012 at 04:10 PM PDT #

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

Posted by Maria Colgan on July 03, 2012 at 03:50 PM PDT #

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

Posted by Timur Akhmadeev on September 12, 2012 at 03:51 AM PDT #

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

Posted by guest on October 02, 2012 at 10:35 AM PDT #

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.

Posted by Sean on November 03, 2012 at 01:48 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today