Bind variable peeking in 11G

In one of my recent posts I mentioned about Gregory Guillou's posts about bind variable peeking in 11G. I did a simple test and research on this.

First here is the test case.

YAS@11G>create table t(id number,name varchar2(30));  

Table created.

YAS@11G>insert into t select mod(rownum,2),object_name from all_objects;

64901 rows created.

YAS@11G>commit;

Commit complete.

YAS@11G>create index t_ind on t(id);

Index created.

YAS@11G>drop table t;

Table dropped.

YAS@11G>create table t(id number,name varchar2(30));

Table created.

YAS@11G>insert into t select mod(rownum,2),object_name from all_objects;

64901 rows created.

YAS@11G>insert into t select 2,'test' from dual;

1 row created.

YAS@11G>create index t_ind on t(id);

Index created.

YAS@11G>exec dbms_stats.gather_table_stats(user,'T',cascade=>true,method_opt=>'for columns id size 254');

PL/SQL procedure successfully completed.

Now we have a table with three distinct values in its ID column, values 0 and 1 have more than 30,000 rows each and value 2 has only one row.

YAS@11G>var v_id number;
YAS@11G>exec :v_id := 0;

PL/SQL procedure successfully completed.

YAS@11G>set lines 200
YAS@11G>column PLAN_TABLE_OUTPUT format a100
YAS@11G>set pages 50
YAS@11G>select count(name) from t where id = :v_id;

COUNT(NAME)
-----------
32450

YAS@11G>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gsmm31bu4zyca, child number 0
-------------------------------------
select count(name) from t where id = :v_id

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 103 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
|* 2 | TABLE ACCESS FULL| T | 32433 | 633K| 103 (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"=:V_ID)

19 rows selected.

A plan using a full table scan is used for this value. Prior to 11G this plan would be fixed and used for all other bind variables regardless of their value if you have CURSOR_SHARING=EXACT, if cursor_sharing is not set to EXACT things are different.
In 11G if we run the same sql with a different value things change.

YAS@11G>exec :v_id := 2;

PL/SQL procedure successfully completed.

YAS@11G>select count(name) from t where id = :v_id;

COUNT(NAME)
-----------
1

YAS@11G>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gsmm31bu4zyca, child number 1
-------------------------------------
select count(name) from t where id = :v_id

Plan hash value: 2602990223

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID"=:V_ID)

20 rows selected.

Notice that I had to run this sql several times for this plan to change. After several runs of the same statement with the value 2 the plan changes to an index range scan. Why is this so?

This is a change in behaviour in bind variable peeking in 11G described here. No need to rephrase what the documentation says here, just to quote:

"To ensure the optimal choice of cursor for a given bind value, Oracle Database uses bind-aware cursor matching. The system monitors the data access performed by the query over time, depending on the bind values. If bind peeking takes place, and a histogram is used to compute selectivity of the predicate containing the bind variable, then the cursor is marked as a bind-sensitive cursor. Whenever a cursor is determined to produce significantly different data access patterns depending on the bind values, that cursor is marked as bind-aware, and Oracle Database will switch to bind-aware cursor matching to select the cursor for that statement. When bind-aware cursor matching is enabled, plans are selected based on the bind value and the optimizer's estimate of its selectivity. With bind-aware cursor matching, it is possible that a SQL statement with user-defined bind variable will have multiple execution plans, depending on the bind values."


The view V$SQL_CS_STATISTICS gives information about the cursor statistics. We can use that to see the effect of this change.

YAS@11G>alter system flush shared_pool;

System altered.

YAS@11G>exec :v_id := 0;

PL/SQL procedure successfully completed.

YAS@11G>select /* test */ count(name) from t where id = :v_id;

COUNT(NAME)
-----------
  32450

YAS@11G>select sql_text,sql_id from v$sql where upper(sql_text) like 'SELECT%TEST%';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
-------------
select /* test */ count(name) from t where id = :v_id
12dcwm6639zmr

select sql_text,sql_id from v$sql where upper(sql_text) like 'SELECT%TEST%'
51hmrnzmuaswy


YAS@11G>select child_number,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS
2  from V$SQL_CS_STATISTICS
3  where sql_id='12dcwm6639zmr';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ ------------------- - ---------- -------------- -----------
   0              1475158189 Y          1          32451         380

YAS@11G>exec :v_id := 2;

PL/SQL procedure successfully completed.

YAS@11G>select /* test */ count(name) from t where id = :v_id;

COUNT(NAME)
-----------
  1

YAS@11G>select child_number,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS
2  from V$SQL_CS_STATISTICS
3  where sql_id='12dcwm6639zmr';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ ------------------- - ---------- -------------- -----------
   1              2064090006 Y          1              3           3
   0              1475158189 Y          1          32451         380

As you can see there are two child cursors for this sql because of the different bind values.