The good and the bad about bind variables, revisited in 11g !

You all know the good and the bad about bind variables. You all know the drawbacks of “bind peeking”. To follow the Battle Against Any Guess, 11g will change the way you look at Oracle cursor sharing.

Note :
The example that follows is intended to be use as a demonstration of the new bind peeking algorithm of 11g. You’ll have to understand how it works before screaming! If it significantly enhances the behavior of the optimizer, I’ll show you in a next post it also has its own drawbacks, you’ll have to overcome…

1°- Create and fill a table to be queried

create table gark
(id number,
text varchar2(10));

begin
  for i in 1..100000 loop
    insert into gark values (i, 'example1');
    insert into gark values (1, 'example2');
  end loop;
  commit;
end;
/

create index gark_idx
     on gark(id);

exec dbms_stats.gather_table_stats(-
     USER,-
     'GARK',-
     cascade=>true,-
     method_opt=>'for all indexed columns size 254',-
     no_invalidate=>false)

2°- Query the table with :value = 2

var value number;
exec :value := 2

select /* test */ count(text)
  from gark
  where id=:value;

select *
  from table(dbms_xplan.display_cursor);

COUNT(TEXT)
-----------
   1

PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID  95xkphfdfm9ng, child number 0
-------------------------------------
select /* test */ count(text)   from gark   where
id=:value

Plan hash value: 2577124290

---------------------------------------------------------
| Id  | Operation                    | Name     | Rows  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |
|   1 |  SORT AGGREGATE              |          |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| GARK     |     1 |
|*  3 |    INDEX RANGE SCAN          | GARK_IDX |     1 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:VALUE)

3°- Use the same query with :value = 1

Actually as you can see below, there is no change to the plan. It’s a shame as you know performing a FTS would probably speed up the query…

var value number;
exec :value := 1

select /* test */ count(text)
  from gark
  where id=:value;

select * from
  table(dbms_xplan.display_cursor);

COUNT(TEXT)
-----------
100001

PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID  95xkphfdfm9ng, child number 0
-------------------------------------
select /* test */ count(text)   from gark   where
id=:value

Plan hash value: 2577124290

---------------------------------------------------------
| Id  | Operation                    | Name     | Rows  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |
|   1 |  SORT AGGREGATE              |          |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| GARK     |     1 |
|*  3 |    INDEX RANGE SCAN          | GARK_IDX |     1 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:VALUE)

4°- Use the same query with :value = 1 (again)

This time the plan changes. It is obvious the optimizer has detected the previous execution was far behind the execution statistics from the first execution and decided to create another child cursor for :value = 1

var value number;
exec :value := 1

select /* test */ count(text)
  from gark
  where id=:value;

select * from
  table(dbms_xplan.display_cursor);

COUNT(TEXT)
-----------
100001

PLAN_TABLE_OUTPUT
---------------------------------------------------
SQL_ID  82qnsy0562gqb, child number 0
-------------------------------------
select /* test */ count(text)  from gark  where
id=:value

Plan hash value: 2364300905

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| GARK | 98425 |
-------------------------------------------

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

2 - filter("ID"=:VALUE)

-Gregory