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 

The mechanism of Adaptive Cursor Sharing.

Adaptive Cursor Sharing(ACS) was introduced in oracle 11g. Until 10g, the oracle only supports bind peeking (from 9i). But, bind peeking has a big problem, when the column in the where clause with the bind has a skew data.To solve that problem ACS need to use a column histogram. Now, try to test to understand the ACS.

(1) Create a table and insert skew data.

create table t1 (c1 number, c2 number, dummy char(1000));
 
insert into t1
select level, level, 'dummy'
from   dual
connect by level<=1000
union all
select level+1000, 1001, 'dummy'
from   dual
connect by level<=99000;
 
commit; 
 
create index idx1 on t1(c2) nologging;
exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');

(2) c2 column has (HEIGHT BALANCED) histogram.

select column_name, histogram from user_tab_columns where table_name='T1'
order by column_id;
 
COLUMN_NAME  HISTOGRAM
-----------  ----------------
C1           NONE
C2           HEIGHT BALANCED
DUMMY        NONE

(3) Now, let’s try to test.
The value of all hidden parameters that was related to ACS are default (_optimizer_adaptive_cursor_sharing=true, _optimizer_extended_cursor_sharing_rel=simple). First bind value is 10 and the number of rows is 1, so optimizer decided to index scan. it was good choice.

var b1 number
exec :b1:=10;
 
select /*_ACS1*/
       count(*), max(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)
---------- ----------
         1         10
 
select * from table(dbms_xplan.display_cursor);
 
SQL_ID  as9xzr2tdxj5u, 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 |
-------------------------------------------------------------------------------------

And that cursor was marked IS_BIND_SENSITIVE=’Y’.

select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='as9xzr2tdxj5u';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 N

Second bind value is 1001 and the number of rows is 99,000. So, in this case, full scan is better than index scan. But, against my expectations, the optimizer still has been decided to do index scan.
exec :b1:=1001;

 
select /*_ACS1*/
       count(*), max(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)
---------- ----------
     99000     100000
 
select * from table(dbms_xplan.display_cursor);
 
SQL_ID  as9xzr2tdxj5u, 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 |
-------------------------------------------------------------------------------------

If that statement was executed one more time by same bind value (1001), then oracle will create a new child cursor(#1) and the optimizer will do full scan. And then, that cursor was marked IS_BIND_AWARE=’Y’.

select /*_ACS1*/
count(*), max(c1)
from t1
where c2=:b1;
 
 COUNT(*) MAX(C1)
---------- ----------
    99000 100000
 
select * from table(dbms_xplan.display_cursor);
         
SQL_ID  as9xzr2tdxj5u, 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='as9xzr2tdxj5u';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          2       14356 Y                 N
           1          1       14593 Y                 Y
 
The third bind value is 20 and the number of rows is 1. When we execute that sql statement, oracle creates a new child cursor(#2) and optimizer decided to index scan. From that time, the child cursor (#0) was no longer be used. That was marked IS_SHAERABLE='N'.
exec :b1:=20
 
elect /*_ACS1*/
       count(*), max(c1)
from   t1
where  c2=:b1;
 
select * from table(dbms_xplan.display_cursor);
 
SQL_ID  as9xzr2tdxj5u, child number 2
-------------------------------------------------------------------------------------
| 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, is_shareable
from   v$sql
where  sql_id='as9xzr2tdxj5u';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------ ---------- ----------- ----------------- ------------- ------------
           0          2       14356 Y                 N             N
           1          1       14593 Y                 Y             Y
           2          1           3 Y                 Y             Y