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

It would not have been fair to show how Oracle 11g optimizer can now re-parse a query based on the execution statistics associated with a bind value compared to the previous executions of the same query with different bind values. It would not have been fair, to show you that without showing what happens when the second execution is faster that the first one, even with a sub optimal plan. This is what I’ll do now !

Basically, what is done in the example below it the opposite of what I’ve done in my previous post. You’ll need to create and fill the table according to that post in order to run the queries below :

1°- Query the table with :value =1

var value number;
exec :value := 1

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

select *
  from table(dbms_xplan.display_cursor);

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

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

Plan hash value: 2364300905
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   144 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |       |
|*  2 |   TABLE ACCESS FULL| GARK | 98425 |  1057K|   144 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=:VALUE)

2°- Execute the same query with :value = 2

As expected, there is no change in the plan :

var value number;
exec :value := 2

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

select * from
  table(dbms_xplan.display_cursor);

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

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

Plan hash value: 2364300905
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   144 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |       |
|*  2 |   TABLE ACCESS FULL| GARK | 98425 |  1057K|   144 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=:VALUE)

3°- Execute the same query with :value = 2 (again and again…)

This time the plan may not change… the second time you execute but at the 3rd or 4th execution. Anyway after a while, the plan is the one you would have dream of :

var value number;
exec :value := 2

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

select * from
  table(dbms_xplan.display_cursor);

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

SQL> SQL>   2
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  1xnsvsxnm557v, child number 1
-------------------------------------
select /* test2 */ count(text)   from gark   where id=:value

Plan hash value: 2577124290

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

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

4°- 3 = 2

Now if you execute the same query with :value = 3, you have the same plan as the one with :value = 2

var value number;
exec :value := 2

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

select * from
  table(dbms_xplan.display_cursor);

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

SQL> SQL>   2
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  1xnsvsxnm557v, child number 1
-------------------------------------
select /* test2 */ count(text)   from gark   where id=:value

Plan hash value: 2577124290

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

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

Does it means 11g will solve every bind variable related problem of OLTP workloads ? We’ll see on production. What do you think ?

-Grégory