Mauvais plan d'exécution pour Microsoft Dynamics AX

**xanav** · 25/07/2012, 08h34

Bonjour,

Je viens vers vous car nous avons un problème entre Oracle et notre ERP. Nous sommes en cours de migration de Axapta V3.0 à Dynamics AX 2009. Nous étions sous Oracle et nous n'avions aucun soucis mais là nous sommes face à un problème étrange.
Il semblerait qu'Oracle n'utilise pas le bon plan d'éxécution lorsqu'il reçoit une requête en provenance d'AX.

Voici, par exemple, une requête exécutée par l'ERP (capturée via Toad) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT /*+ FIRST_ROWS */
       a.recid
  FROM docuref a
 WHERE (    (    (SUBSTR (NLS_LOWER (refcompanyid), 1, 4) = NLS_LOWER (:in1))
             AND (reftableid = :in2)
            )
        AND (refrecid = :in3)
       )

Si on regarde le plan d'exécution utilisé, on voit qu'oracle utilise l'index "RECIDX" qui possède les 2 champs suivant :
- SUBSTR (NLS_LOWER (refcompanyid), 1, 4)
- RecId

Si on copie la requête dans le navigateur Toad et qu'on regarde le plan d'exécution, il est différent et utilise l'index "REFIDX" qui possède les champs suivant :
- SUBSTR (NLS_LOWER (refcompanyid), 1, 4)
- RefTableId
- RefRecId
- CreatedDateTim
- SUBSTR (NLS_LOWER ("CreatedBy"), 1, 5)

La deuxième solution est clairement meilleur et incomparable en terme de temps de réponse.
Quelqu'un aurait-il une idée ? un retour d'expérience sur Dynamics AX ?
Merci.

**ojo77** · 25/07/2012, 09h05

Les plans d'exécution des requêtes dépendent des variables de session.

TOAD a la sale habitude de modifier les variables de session sans prévenir (c'est très certainement configurable) il est donc fort probable qu'un plan d'exécution généré avec TOAD diffère du plan réel.

De plus 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 dune exécution réelle il les prends en compte.

Pour effectuer un diagnostique précis de votre requête, je vous recommande de ne pas utiliser TOAD et de plutôt vous diriger vers SQLT qui vous produira un rapport HTML détaillant les divers plans d'exécution, les statistiques sur les objets, les dates auxquelles les divers plans ont pu être pris en compte ...

**Mohamed.Houri** · 25/07/2012, 12h03

@ xanav
Un plan d'exécution sans sa partie "Predicate" est à moitié inexploitable.

Le mieux que vous puissiez faire c'est de faire ceci sous sqlplus

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT /*+ FIRST_ROWS */
       a.recid
  FROM docuref a
 WHERE (    (    (SUBSTR (NLS_LOWER (refcompanyid), 1, 4) = NLS_LOWER (:in1))
             AND (reftableid = :in2)
            )
        AND (refrecid = :in3);
 
select * from table(dbms_xplan.display_cursor);

Et postez ici le nouveau plan d'exécution généré.

@ojo77

Je n'ai pas bien compris ceci

"De plus 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 dune exécution réelle il les prends en compte."

Pouvez-vous me donner plus de détails ?

**ojo77** · 25/07/2012, 13h32

Envoyé par Mohamed.Houri

@ojo77
Pouvez-vous me donner plus de détails ?

Oui je peux

Je crée une table de test avec une répartition de données qui suit une loi normale :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
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 <=1E5 ;
 
create index i_t1 on t1(c1) ;
 
exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)

La répartitiond es données est la suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

Je fais mes explain plan pour des valeurs n'ayant pas le même nombre d'occurrences :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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.

estimé => 7143, valeur réelle du nombre de lignes qui seront retournées => 24741

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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.

Estimé toujours 7143, valeur réelle du nombre de lignes qui seront retournées => 2

Je passe maintenant à l'exécution réelle toujour pour s = 13:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
select /* tst exec 13 */ * from t1
where c1=:s ;
 
SQL> 
        C1
----------
        13
        13
 
 
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.

Estimé 2 obtenu 2 ... c'est mieux

Cadeau bonus, j'ai 100000 lignes et 14 valeurs distinctes (aucune n'étant nulle)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SQL> select round(100000/14) est from dual
  2  /
 
       EST
----------
      7143

Tiens 7143, ça me dit quelque chose

Cadeau bonus n°2 pour être bien rigoureux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SQL> alter system flush shared_pool ;
 
System altered.
 
SQL> select /*+ GATHER_PLAN_STATISTICS */ * from t1
  2  where c1=:s ;
 
        C1
----------
        13
        13
 
SQL> select * from table( dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5av85qaqqqynu, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from t1 where c1=:s
 
Plan hash value: 4291296153
 
-----------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      2 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN| I_T1 |      1 |      2 |      2 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("C1"=:S)
 
 
18 rows selected.

Donc quand je fais un explain plan il ne prend pas en compte l'histogramme pour estimer la cardinalité alors qu'il le prend en compte lorsque j'exécute effectivement la requête.

**Mohamed.Houri** · 25/07/2012, 15h05

Envoyé par ojo77

Oui je peux

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

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

13 rows selected.
[/code]

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

1 - access("C1"=:S)

18 rows selected.
[/code]

Merci pour avoir pris la peine d'expliquer avec des exemples.

Avez-vous remarqué quelques choses dans les différents Predicates. N’avez-vous pas remarqué la conversion implicite TO_NUMBER() dans le plan d’exécution fourni par le explain plan for ? Comment voudriez-vous avoir de bonnes estimations dans ce cas ?

En réalité, il ne faut jamais se baser sur l’instruction explain plan for suivi d’un select * from table(dbms_xplan.display). Car dans ce cas, il y a deux problèmes:

1. Toutes les bind variables sont considérées comme des varchar2 ; c’est pourquoi vous voyez une conversion implicite to_number qui ne se produit pas lors de l’exécution réelle de la requête

2.Lors de cette phase de génération du plan d’exécution la requête n’est pas exécutée et, vu que le ‘’bind variable peeking’’ se produit durant la partie ‘’harde parse’’ il est donc tout à fait normal que les estimations soient approximatives

http://hourim.wordpress.com/2011/12/...-explain-plan/

Les plans d’exécution donnés pas Toad et SQL Developer ont de fortes chances d’utiliser explain plan for et donc d'êtres approximatifs

Pour le reste de ce que vous avez montré on entre dans le classique problème des bind-variables, des histogrammes et du rôle antagoniste pour lequel ils ont été créés.

**ojo77** · 25/07/2012, 15h42

Envoyé par Mohamed.Houri

N’avez-vous pas remarqué la conversion implicite TO_NUMBER() dans le plan d’exécution fourni par le explain plan for ? Comment voudriez-vous avoir de bonnes estimations dans ce cas ?

Bien essayé, mais les conversions implicites en partie droite de prédicat n'influent pas sur les cardinalités

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
variable s2 varchar2(2)
 
exec :s2 := '13' 
 
select /*+ GATHER_PLAN_STATISTICS */ * from t1
where c1=:s2 ;
 
 select * from table( dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3k7s6r7gryyhm, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from t1 where c1=:s2
 
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"=TO_NUMBER(:S2))
 
 
18 rows selected.

**Mohamed.Houri** · 25/07/2012, 16h11

Envoyé par ojo77

Bien essayé, mais les conversions implicites en partie droite de prédicat n'influent pas sur les cardinalités

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
variable s2 varchar2(2)
 
exec :s2 := '13' 
 
select /*+ GATHER_PLAN_STATISTICS */ * from t1
where c1=:s2 ;
 
 select * from table( dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3k7s6r7gryyhm, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from t1 where c1=:s2
 
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"=TO_NUMBER(:S2))
 
 
18 rows selected.

Ojo77,

Le point que je veux faire passer est le suivant:

"Ne pas se baser sur explain plan for + select * from table(dbms_xplan.display) pour avoir le bon plan d'exécution avec les bonnes estimations".

Pas plus pas moins.

En plus je l'ai dit plus haut : Il n'y a pas de bind variable peeking lors de la commande explain plan for. Pour comparer deux choses il faut être dans les mêmes conditions.

La seule fois où je juge cela opportun c'est uniquement lorque ma requête prend 2 heures pour s'exécuter. J'utilise dans ce cas explain plan for pour avoir une idée sur le plan approximatif qui sera eventuellement suivi par Oracle

Les conversions implicites de droite n'influent peut-être pas sur la cardinalité mais influent sur le choix de l'opération. Comme ici, un FULL TABLE SCAN pour générer 2 enregistrements.

Vous m'avez motivé pour aller plus en profondeur afin de voir sur quoi se base le explain plan for pour faire ses estimations

Edit : qui peut l'expliquer mieux que tom kyte

**ojo77** · 25/07/2012, 23h19

Envoyé par Mohamed.Houri

Ojo77,

Le point que je veux faire passer est le suivant:

"Ne pas se baser sur explain plan for + select * from table(dbms_xplan.display) pour avoir le bon plan d'exécution avec les bonnes estimations".

Pas plus pas moins

Donc, nous arrivons a la même conclusion => si bind variable l'explain plan n'est pas une bonne piste.

Par contre je me demande bien pourquoi vous m'avez demandé d'expliquer ce que vous saviez déjà.

**Mohamed.Houri** · 26/07/2012, 08h47

Envoyé par ojo77

Donc, nous arrivons a la même conclusion => si bind variable l'explain plan n'est pas une bonne piste.

Par contre je me demande bien pourquoi vous m'avez demandé d'expliquer ce que vous saviez déjà.

C'est tout simplement que ceci n'était pas clair (en tout cas pour moi)

De plus 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 dune exécution réelle il les prends en compte.

Je l'aurai écrit comme suit:

De plus vous utilisez ici des bind variables. Lorsque vous utilisez ce type de variable dans un explain plan for, Oracle ignorera ces bind variables, les considerera comme du type varchar2, n'en fera pas un "bind variable peeking" et donc produira un plan d'exécution approximatif.

Je sais que c'est une question de style. Si j'ai réagi c'est que je ne l'avais pas bien compris; c'est pourquoi j'ai demandé plus de détails dans un but soit d'apprendre quelque chose de nouveau soit d'apporter une clarification pour ceux et celles qui viendraient un jour à nous lire.

**Pomalaix** · 26/07/2012, 10h24

Certaines personnes, par abus de langage, confondent la commande EXPLAIN PLAN avec son résultat, qui est le plan d'exécution.
Et non des moindres : il y en a plusieurs exemples dans le dernier livre de Tom Kyte.

La doc Oracle me semble faire la distinction correctement, par exemple :

You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement.

Et on en arrive à un comble : c'est celui qui emploie le juste mot qui n'est pas compris ! Comme quoi l'acribologie ne paye pas...