Résumé des épisodes précédents

Lors d’un précédent article j’ai montré que l’instruction Explain Plan donnait un résultat discutable dès lors que la requête étudiée impliquait des bind variables. Une question reste en suspens : Pour quelle raison les cardinalités calculées par Explain plan sont-elles fausses ?
Je vois circuler sur le net une explication farfelue selon laquelle explain plan ne déclencherait pas de hard parse et qu’en conséquence il n’irait pas rechercher la valeur des bind variables, explication plus ou moins mélangée avec une demie vérité selon laquelle explain plan considérerait toutes les bind variables comme étant de type varchar2 (vrais) et que de ce fait il serait incapable d’aller chercher la valeur correspondante dans la table (faux).
Avant toute chose, il est important de se référer à Tom Kyte qui en la matière est quasiment indiscutable (après tout, tout le monde peut se tromper) :

« explain plan always does a hard parse, it evaluated the query plan « in the here and now, as of this moment in time » »

Il est donc plus que probable que l’explication selon laquelle explain plan ne fait pas de hard parse est fausse. Pour s’en convaincre, je ferai des traces grâce à l’évènement 10053 qui ne produit une trace que lors de hard parses.
Les tests effectués dans ce billet le sont en 11gR2.

Des objets pour tester …

Avant toutes choses, on se crée une petite table de test, indexée afin que l’optimiseur puisse choisir ou non de prendre l’index et avec des histogrammes pour que les opérations de bind peeking aient un sens.

drop table t1;
 create table t1 ( c1 number, c2 number );
 insert /*+ APPEND */ into t1
 select abs(ceil(dbms_random.normal*3)), rownum
 from sys.dual
 connect by rownum <= 1E6 ;
 exec dbms_stats.gather_table_stats(user, T1, method_opt=>'FOR COLUMNS C1 SIZE 254', estimate_percent=>100);

 
La commande suivante liste les cardinalités (pêchées dans l’histogramme sur la colonne) associées à chaque valeurs de la colonne C1 de la table de test T1.

select TABLE_NAME
     , COLUMN_NAME
     , ENDPOINT_NUMBER - nvl(lag(ENDPOINT_NUMBER, 1) over (partition by TABLE_NAME, COLUMN_NAME order by ENDPOINT_NUMBER) , 0 ) card
     , ENDPOINT_VALUE
from dba_tab_histograms
where table_name='T1'
  and owner=user ;
TABLE_NAME                     C_N       CARD ENDPOINT_VALUE
------------------------------ --- ---------- --------------
T1                             C1       13088              0
T1                             C1       24444              1
T1                             C1       21250              2
T1                             C1       16146              3
T1                             C1       11145              4
T1                             C1        6867              5
T1                             C1        3752              6
T1                             C1        1948              7
T1                             C1         865              8
T1                             C1         316              9
T1                             C1         121             10
T1                             C1          42             11
T1                             C1          12             12
T1                             C1           3             13
T1                             C1           1             14

On a donc une colonne dont les valeurs ne sont pas uniformément réparties et si l’optimiseur fait son travail correctement, pour un contrainte de type C1=13 il devrait choisir un accès par index et pour une contrainte de type C1=1 il devrait choisir un parcours complet de la table.

Jeux de tests

Test 1 : Exécution d’une requête SQL avec bind variable dont la valeur produira peu de lignes (C1=13)

La mémoire est vide de tout plan pour cette table nouvellement créée, on s’attend à ce que, par le biais de l’histogramme, l’optimiseur s’aperçoive que la valeur 13 est peu représentée pour la colonne C1 et choisisse un accès par index.

variable s number
exec :s := 13
alter session set tracefile_identifier = 'T1_EXE_NUM' ;
alter session set events='10053 trace name context forever' ;
select * from t1 where c1 = :s ;
        C1         C2
---------- ----------
        13      61103
        13      79854
        13      95305
alter session set events='10053 trace name context off' ;
alter session set tracefile_identifier='' ;

Trois lignes de remontées sur 100 000, on espère qu’un index a été utilisé …

select * from table ( dbms_xplan.display_cursor() ) ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  6v6vzbxcpb91s, child number 0
-------------------------------------
select * from t1 where c1 = :s
Plan hash value: 1198593617
---------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost | Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     3 |    24 |     2| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1_C1 |     3 |       |     1| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"=:S)

Formidable, le plan choisi est le bon, ou du moins celui que l’on attend, le curseur est en mémoire, on continue.

Test 2 : Production du plan d’exécution par explain plan

On joue cette fois-ci un explain plan, le curseur n’a pas quitté la mémoire, la variable ‘s’ n’a pas changé de valeur on pourrait penser :

• Que le curseur étant en mémoire, explain plan va se contenter d’effectuer un soft parse et s’apercevant qu’il a déjà un curseur, produire le plan correspondant.
• Que l’instruction explain plan ignore les curseurs en mémoire, va donc utiliser ce qu’il a à disposition (bind variable et histogramme) pour produire le même plan que plus haut.

alter session set tracefile_identifier = 'T2_XPL_NUM' ;
alter session set events='10053 trace name context forever' ;
explain plan for select * from t1 where c1 = :s ;
Explained.
alter session set events='10053 trace name context off' ;
alter session set tracefile_identifier='' ;

On le sait, on l’a vu lors de mon précédent article sur le sujet, ni le plan en mémoire, ni le même déduit à partir de la valeur de la variable ne sera produit :

select * from table ( dbms_xplan.display() ) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 838529891
--------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost | Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  7143 | 57144 |    35| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  7143 | 57144 |    35| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"=TO_NUMBER(:S))
13 rows selected.

Au passage on vérifie que des hard parses ont étés faits pour chacun de nos tests :

ho ls -rt $BDUMP/TTEST*T*trc
TTEST_ora_20250650_T1_EXE_NUM.trc
TTEST_ora_20250650_T2_XPL_NUM.trc

Comme prévu une analyse complète de la requête a été faite dans les deux cas et le plan généré est différent du plan initial.
On constate aussi un traitement différent :

• Exécution :
  • 2 – access(« C1″=:S)
• Explain plan
  • 1 – filter(« C1″=TO_NUMBER(:S))

Que dit Tom Kyte quand il évoque le sujet ?

« The last bit about explain plan […] is the fact that explain plan doesn’t see your bind datatype. It presumes all binds are varchar2’s regardless of how the developer is binding. »

On pourrait penser du fait que l’explain plan considère les bind variables systématiquement comme un type varchar2, la conversion influe sur le plan d’exécution et sur les cardinalités, et on se fourvoierait :

Test 3 : Exécution la même requête SQL avec bind variable de type varchar2

alter system flush shared_pool ;
variable s2 varchar2(3)
exec :s2 := '13'
alter session set tracefile_identifier = 'T3_EXE_VC2' ;
alter session set events='10053 trace name context forever' ;
select * from t1 where c1 = :s2 ;
        C1         C2
---------- ----------
        13      61103
        13      79854
        13      95305
alter session set events='10053 trace name context off' ;
alter session set tracefile_identifier='' ;

Si l’explication selon laquelle un type varchar2 égare le CBO est vraie, le plan utilisé devrait utiliser un parcours complet de la table, il prendrait un index dans le cas contraire.

select * from table ( dbms_xplan.display_cursor() ) ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  bmawjntgyr2p7, child number 0
-------------------------------------
select * from t1 where c1 = :s2
Plan hash value: 1198593617
---------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost | Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     3 |    24 |     2| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1_C1 |     3 |       |     1| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"=TO_NUMBER(:S2))

Lors de l’exécution avec une bind variable de type varchar2, les opérations de bind peeking ont bien été faites et le bon plan choisi, la supposition que l’absence de bind peeking dans l’explain plan provient du type de la bind variable est fausse. Alors pourquoi ?
On s’intéresse ici aux contenus des fichiers de trace générés et surtout à la section où la Bind Variable est lue.

• Lors de l’exécution on a le paragraphe suivant :

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=110950ff8  bln=22  avl=02  flg=05
  value=13
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "SYS"."T1" "T1" WHERE "T1"."C1"=:B1

• Lors de l’explain plan on a le paragraphe suivant :

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1010000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=11097d100  bln=32  avl=00  flg=05
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "SYS"."T1" "T1" WHERE "T1"."C1"=TO_NUMBER(:B1)

Dans les deux cas, l’analyseur de requête constate qu’il a une bind variable, mais dans le second il ne sait pas lui associer une valeur. On peut en déduire que l’instruction explain plan ne donne pas la valeur de la variable à l’analyseur de requête.

(Ma) Conclusion

Si explain plan ne produit pas le plan d’exécution qui sera celui de la requête lors de la concommitance de bind variables et d’histogrammes c’est parce que la valeur de la variable n’est pas transmise au CBO et de fait celui-ci doit se rabattre sur un calcul qui suppose qu’au travers de cette bind variable n’importe quelle valeur peut être passée. De là il calcule une cardinalité moyenne qui l’oriente à bon ou mauvais escient sur un plan qui ne représente pas toujours la réalité.
Dans ces conditions peut-on compter sur explain plan pour faire du tuning ? Je dirais oui, le but d’explain plan est de répondre à la question qu’on lui pose, en l’occurrence et dans tout ce billet, « quel plan prendrait l’optimiseur si on le contraignait avec une valeur variable sur la colonne C1 », la réponse qu’il donne, à savoir « un parcours complet de la table » est vraie pour des valeurs de la variable de 0 à 7 (8 valeurs) et fausse si la variable est comprise entre 8 et 14 (7 valeurs) où le parcours par index est moins coûteux.
Si on souhaite connaitre le plan que l’optimiseur utiliserait pour une valeur précise, il faut la lui donner et ne pas passer par une variable.
On est loin de l’ésotérique absence de hard parse ou de la tout aussi surprenante perte d’information lors d’une conversion de type et quelque part ça rassure non ?

---

Blogographie

Tom Kyte: When the explanation doesn’t sound quite right…