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

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.

Comments:

The BIND_AWARE / NO_BIND_AWARE hints are not documented as far as the official Oracle documentation library goes.

Does this article here mean that it is now officially sanctioned by Oracle to use this hint in a production environment and support will be provided in case of issues?

Thanks,
Randolf

Posted by Randolf Geist on February 14, 2012 at 01:28 PM PST #

Great tips!

Tnx Randolf

Posted by guest on March 02, 2012 at 03:27 AM PST #

Wouldn't it make sense to store the BIND_AWARE hint in the outline data from SMB / SQL plan management once a cursor is noticed and stored as bind aware?

Thanks.

Jan

Posted by guest on May 30, 2012 at 06:40 AM PDT #

Would BIND_AWARE hint work for cases where we pass some collection as
bind variable and use it in a table function?

for example:

create type t_employee_ids_collection as table of number;
/

declare
the_emp_ids_collection t_employee_ids_collection;

procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is
begin
dbms_output.put_line('Starting to print');
for r in (
select /*+ BIND_AWARE find_it_easy_1 */ *
from employees
where employee_id in (select * from table(p_emp_ids_collection)))
loop
dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );
end loop;
dbms_output.put_line('End of printing '||p_emp_ids_collection.count||' records.');
end;

begin
select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)
into the_emp_ids_collection from employees;
print_collection(the_emp_ids_collection);

the_emp_ids_collection := t_employee_ids_collection (100,102,108);
print_collection(the_emp_ids_collection);

end;
/

It would be good if the cardinality could be determined by the optimizer depending on the value of the collection.

Posted by mdaskalo on August 22, 2012 at 02:31 PM 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