How to turn on the Adaptive Cursor Sharing at statement level even though, _optim_peek_user_binds=false.

Although, the Adaptive Cursor Sharing(ACS) is a very useful feature, but, if you experiences some performance problem of bind peeking, maybe you turn off the bind peeking feature(_optim_peek_user_binds=false). In my previous post, I mentioned that if you turn off the bind peeking then the ACS won’t work.

In this case, we use OPT_PARAM and BIND_AWARE hint for using ACS at statement level.

 SQL> show parameter _optim

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
_optim_peek_user_binds               boolean                FALSE
_optimizer_adaptive_cursor_sharing   boolean                FALSE
_optimizer_extended_cursor_sharing_r string                 NONE
el


var b1 number
exec :b1:=10;
 
select /*+BIND_AWARE opt_param('_optim_peek_user_binds','true') */
       count(*), max(c1), avg(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)    AVG(C1)
---------- ---------- ----------
         1         10         10

select * from table(dbms_xplan.display_cursor);

SQL_ID  2xsn3xundg0yz, 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='2xsn3xundg0yz';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y

exec :b1:=1001; 

select /*+BIND_AWARE opt_param('_optim_peek_user_binds','true') */
       count(*), max(c1), avg(c1)
from   t1
where  c2=:b1;
 

  COUNT(*)    MAX(C1)    AVG(C1)
---------- ---------- ----------
     99000     100000    50500.5

 
SQL_ID  2xsn3xundg0yz, 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='2xsn3xundg0yz';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y
           1          1       14593 Y                 Y