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