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

Aug 10, 2007 / By Grégory Guillou

Tags:

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

2 Responses to “The good and the bad about bind variables, revisited in 11g !”

  • LSC says:

    Hi

    The new adaptive cursor sharing seems good idea. I have done some tests and it seems that it stops peeking when it reaches a stability. For example if we have a table with 9 values with low cardinality and 1 with high if we start testing from low to high, since 1 to 9 has low cardinality the plan does not change (uses nice index range scan), when we reach to 10 we would expect a FTS but it does not seems to peek anymore and stays with index range scan plan. So not perfect yet…

  • Mathew says:

    What is the cursor_sharing parameter value set for the above test?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>