Insights into Statistics, Query Optimization and the Oracle Optimizer

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

Maria Colgan
Distinguished Product Manager

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.  From Oracle Database 11g, 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.

Join the discussion

Comments ( 8 )
  • Randolf Geist Tuesday, February 14, 2012

    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?



  • guest Friday, March 2, 2012

    Great tips!

    Tnx Randolf

  • guest Wednesday, May 30, 2012

    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?



  • mdaskalo Wednesday, August 22, 2012

    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;



    the_emp_ids_collection t_employee_ids_collection;

    procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is


    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)))


    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.');



    select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)

    into the_emp_ids_collection from employees;


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




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

  • Arvind Friday, December 20, 2019
    We constantly face the challenge of data distribution for example One chart of account may have few line entries in GL compared to other which is typical business use case which is practical the question is how do we make optimizer pick a plan accurately with no manual tuning at production run time window ? For e.g. even session level directives like "alter session set" would also help controls like this for e.g. would be useful.
  • Nigel Bayliss Thursday, January 16, 2020
    Hi Arvind,
    Histograms are designed to solve problems like this. Aren't you using them?
  • Narendra Friday, July 24, 2020
    Hello Maria,

    Thank you for this nice post.
    1. We are (still) using and one of the challenges that we are facing is with data skew. We do have histograms but we use bind variables and the optimizer does not appear to be peeking into binds for skewed data (when peeking would actually help) and tends to reuse the cursor and results in poor performance. Any suggestions on how to let optimizer handle this?
    2. Some of the sqls have a lot of bind variables so I was wondering whether forcing the cursors to be bind-aware will work because note 1983132.1 appears to suggest a cursor can only be bind sensitive if number of bind variables are under 8 and another note appears to suggest that a cursor can not be bind aware if it can not be bind sensitive. So will adding BIND_AWARE hint help?
  • Nigel Bayliss Tuesday, July 28, 2020
    Hi Narendra -

    There is a hard limit of 14 binds. The max number an individual query can have is sometimes reduced by query internals - but 14 will be OK in most cases. As for "1", there is no easy answer. There is an enhancement request to lift the limit, but this has not yet been implemented. If you really have to use a large list of binds, and the skew is making a catastrophic difference in performance, then the only real option is to use the application to adjust the shape or content of the query depending on some bind value 'rule' - defined by the application. For example, you can add a comment in the query text to (in effect) control which SQL plan baseline is matched with the query, and then use the SQL plan baseline to control what plan to use - effectively using the comment to control which plan to use..

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