How do I force a query to use bind-aware cursor sharing?

How do I force a query to use bind-aware cursor sharing?     

By Allison on Feb 13, 2012

no_BIND_AWARE是恰恰相反的作用

Way back in our original post on adaptive cursor sharing, a commenter asked if there was a way to skip the monitoring for a query and go straight to bind-aware cursor sharing.  We mentioned then that we would add a hint for this purpose, but we never addressed the new hint here on the blog.  Starting in 11.1.0.7, the BIND_AWARE hint can be used to force bind-aware cursor sharing from the first execution.  The hint only works if all of the other criteria required for bind-aware cursor sharing are met; for instance, the query must have binds, the binds must appear in where-clause predicates, and the columns in those predicates must have the proper statistics (e.g. a histogram) to allow the plan to actually change when the query is executed with different bind values.

Let's look again at the query we used in that original post on ACS.

select count(*), max(empno)
  from emp
  where deptno = :deptno;

Recall that in that example, we had to execute the query twice, with different bind values, before we switched to bind-aware cursor sharing.  So we had to run the query a total of at least four times in order to populate the cursor cache with the two plans we want to use, depending on bind value.  This is what the cursor cache looked like after 5 executions:

Now let's look at the behavior with the BIND_AWARE hint:

After two executions with two different bind values, there are two bind-aware child cursors.  And as expected, the very selective bind value (9) used an index scan, while the not-so-selective bind value (10) used a full table scan:

Now that the cursor cache is populated with these two bind-aware child cursors, they will behave just as if they were created due to adaptive cursor sharing. For instance, if I run the query again with another very selective bind value, the first child cursor will be used.

Before you start using this hint all over the place, keep in mind that there is a reason why we monitor queries before deciding to use bind-aware cursor sharing by default.  There are various forms of overhead from using this form of cursor sharing.  These include:

• A small amount of additional cursor memory, to store the information we need to pick the child cursor to use for a particular bind value.
• Additional overhead for soft parse, since we have to compute the selectivity with the current bind value in order to decide which child cursor, if any, is right for that bind value.
• Additional hard parses, which may not actually generate different plans for different bind values.
• More child cursors, which may cause cursor cache contention.
  

These are all of the reasons why we take an adaptive approach to cursor sharing.  But for certain queries, which you know will benefit from different plans for different bind values, the hint can be helpful.

Some of you might be wondering how you can use this hint for queries in a packaged application (where you cannot edit the query text).  I will discuss this in my next post. You can get a copy of the script I used to generate this post here.

Category: How do I

        

Tags:             cursor_sharing       how_do_i     

        

Permanent link to this entry