The behavior of BIND_AWARE hint.

Oracle supports BIND_AWARE and NO_BIND_AWARE hints that could control the Adaptive Cursor Sharing. From the name of each hint, We could expect the behavior. The test of NO_BIND_AWARE hint is unnecessary. That hint is so clear. But, BIND_AWARE hint isn’t clear to me. So let’s try to test.

BIND_AWARE hint.
For the test, the disable parameter value would be needed.

alter session set "_optimizer_adaptive_cursor_sharing"=false;
alter session set "_optimizer_extended_cursor_sharing_rel"=false;

Next, execute query using hint. The test environment is all same as previous post.
From the test result, we know that BIND_WARE hint works well regardless of the parameter value.

var b1 number
exec :b1:=10; 
 
select /*+BIND_AWARE*/
       count(*), max(c1)
from   t1
where  c2=:b1;
  
  COUNT(*)    MAX(C1)
---------- ----------
         1         10 
 
select * from table(dbms_xplan.display_cursor);

SQL_ID  9avbm7xarm430, child number 0
------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    17 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='9avbm7xarm430';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y
    
exec :b1:=1001;
 
select /*+BIND_AWARE*/
       count(*), max(c1)
from   t1
where  c2=:b1;
  COUNT(*)    MAX(C1)
---------- ----------
     99000     100000
  
SQL_ID  9avbm7xarm430, child number 1
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4134 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99016 |  1643K|  4134   (1)| 00:00:50 |
---------------------------------------------------------------------------
 
select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='9avbm7xarm430';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y
           1          1       14593 Y                 Y

After the test, I wonder that BIND_AWARE hint can even work, even if “_optim_peek_user_binds”=false? As expected, if “_optim_peek_user_binds”=false then ACS can’t work. Because, if you turn off the feature of bind peeking then the oracle doesn’t peek any bind variable.

alter session set "_optim_peek_user_binds"=false; 

var b1 number
exec :b1:=10;
 
select /*+BIND_AWARE*/
       count(*), max(c1), min(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)    MIN(C1)
---------- ---------- ----------
         1         10         10
 
select * from table(dbms_xplan.display_cursor);

SQL_ID  1wx1pmnfj5ajp, child number 0
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |    16 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |   100 |  1700 |    16   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='1wx1pmnfj5ajp';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 N                 N