L’algorithme de Bind Peeking apparu en 9i peut-être tout aussi la source de tous vos ennuis que le sauveur de vos performances. En 11g, le bind peeking évolue et l’optimiseur peut être amené à créer des plans différents en fonction des valeurs de Bind… Terminé les malheurs à cause de cette fonctionnalité, ce ne sera bientôt plus que du bonheur. Voici un exemple :

1. Créer une table exemple

Avant de commencer, voici le script qui vous permet de créer la table et de la remplir pour notre pour illustrer cette nouvelle fonctionnalité :

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. Exécutez une requête pour :value = 2

Voici ci-dessous le résultat de cette requête ainsi que son plan (Le vrai, dans la shared pool, affiché avec la fonction display_cursor) :

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)

2. Exécutez la même requête avec :value = 1

Le plan de change pas et pourtant, un changement serait utile, non ?

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)

3. re-Exécutez la même requête avec :value = 1

Par magie, le plan change, comme vous pouvez le voir :

var value number;
exec :value := 1

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

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)

Et pourtant, c’est bien la même requête; cursor_sharing=exact. A partir de ce moment, chaque fois que vous exécuterez la requête avec :value=1, elle prendra ce second plan et si vous l’exécutez avec :value=2, elle prendra le premier plan. Remarquez que si vous exécutezle scénario avec 1 d’abord puis 2 ensuite, le plan de change pas (sans doute Oracle traque les variations à la hausse uniquement)

4. 3=2

Coup de chance ou pas, quand :value=3, c’est le plan identique à la valeur 2 qui est choisi dans dans ce qui suit :

var value number;
exec :value := 3

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

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

SQL> SQL>   2
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID  f8pb9f81acgz6, 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. Supprimer la table et son index

Ci-dessous, le script pour supprimer l’index et la table

drop index gark_idx;
drop table gark;

5. Conclusion

Je ne sais plus quoi dire voila !

-GarK!