Explain plan, Bind variables et Histogrammes (1/2)

Par Olivier JOLYReprise pas volée, Support Niveau 3, Tuning

Lors d’une discussion sur un forum j’ai lancé l’affirmation suivante qui a laissé un membre dubitatif :

Vous utilisez ici des bind variables. Lorsque vous utilisez ce type de variable dans un explain plan, Oracle ignore les éventuels histogrammes sur les colonnes alors que lors d’une exécution réelle il les prends en compte.

Je reprends ici les éclaircissements donnés à ce sujet sur ledit forum.

Mise en place d’un environnement de test

Je me suis créé une table de test de 100 000 lignes contenant une colonne qui suit une loi normale de la manière suivante :

set autot off

drop table t1;
create table t1 ( c1 number );

insert /*+ APPEND */ into t1
select abs(ceil(dbms_random.normal*3))
from sys.dual
connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)

J’ai donc la distribution suivante pour les valeurs de ma table 

select c1, count(*)
from t1
group by c1
order by c1 ;

        C1   COUNT(*)
---------- ----------
         0      12917
         1      24741
         2      21291
         3      16074
         4      11194
         5       6775
         6       3840
         7       1778
         8        868
         9        346
        10        113
        11         51
        12         10
        13          2

Tests avec explain plan

Premier test avex une valeur fortement représentée :

variable s number

exec :s := 1 ;

explain plan for
select /* tst explain 1 */ * from t1
where c1=:s ;

@?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4291296153

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |  7143 | 21429 |    14   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |  7143 | 21429 |    14   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=TO_NUMBER(:S))

13 rows selected.

Le plan d’exécution m’indique que je vais remonter 7143 lignes. Je sais que c’est faux, la distribution des données me donne 24741 et l’histogramme ayant été calculé complètement, il donne les mêmes inforamtions à l’optimiseur.

Je fais un nouveau test avec une valeur très faiblement représentée

exec :s := 13 ;

explain plan for
select /* tst explain 13 */ * from t1
where c1=:s ;

@?/rdbms/admin/utlxplp
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4291296153

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |  7143 | 21429 |    14   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |  7143 | 21429 |    14   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=TO_NUMBER(:S))

13 rows selected.

Là encore le plan m’annonce que je vais remonter 7143 lignes, hors je sais que j’en remonterai deux

Tests d’exécution

Je refais mon test pour la valeur 13 qui doit remonter deux lignes

select /* tst exec 13 */ * from t1
where c1=:s ;

        C1
----------
        13
        13

Formidable, elle remonte bien deux lignes, l’explain plan m’avait donc bien donné une information erronée. Que dit le plan choisi par le CBO ?

select * from table( dbms_xplan.display_cursor() ) ;

------------------------------------------------------------------------------------
SQL_ID  31j5tck2g6gd2, child number 0
-------------------------------------
select /* tst exec 13 */ * from t1 where c1=:s

Plan hash value: 4291296153

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| I_T1 |     2 |     6 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:S)


18 rows selected.

Vous me direz qu’à postériori il est simple de donner le nombre de lignes qui ont été ramenées … Ok, je refais la même requête en lui demandant de me mémoriser les lignes estimées par le CBO (E-ROWS) et les lignes effectivement remontées (A-ROWS) et je refais afficher le plan.

select /*+ GATHER_PLAN_STATISTICS */ * from t1
where c1=:s ;

        C1
----------
        13
        13


select * from table( dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  5av85qaqqqynu, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from t1 where c1=:s

Plan hash value: 838529891

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      2 |00:00:00.01 |     156 |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |      2 |      2 |00:00:00.01 |     156 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"=:S)


18 rows selected.

Ok, il estme donc bien deux lignes et en retourne 2 … ça parait logique et ça rassure

Pourquoi 7143 ?

On a vu qu’un explain plan produisait systématiquement la valeur erronée 7143, d’où la tire-t-il ? Je suppose qu’il utilise la formule de cardinalité générale sans tenir compte des histogrammes :

cardialité=nombre de lignes non nulles / nombre de valeurs distinctes ce qui dans notre cas donne : 100 000 lignes non nulles pour 14 valeurs distinctes … Je vous laisse compter le temps de taper la requête :

select round(100000/14) est from dual;

       EST
----------
      7143

Donc l’explain plan ne s’est pas donné la peine de parcourir l’histogramme.

Une question reste en suspens : Pourquoi la variable n’est pas interprétée ? … Suite au prochain épisode, ça se complexifie …