Bind Peeking et Variables Bind en 11g

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!

1 réflexion sur “Bind Peeking et Variables Bind en 11g”

  1. Quelqu’un vient de m’envoyer un lien vers cet article en me demandant s’il s’agissait d’Adaptive Cursor Sharing(ACS).
    Comme la fonctionnalité n’est pas explicitement citée dans cet article, ça vaut le coup de le préciser: c’est bien ACS qui est mis en avant dans cet excellent post.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *