Oracle Blog

Insight into the workings of the Optimizer

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.

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?

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.

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.