Exécution plus longue avec une condition supplémentaire

**titwix** · 22/10/2013, 16h02

Bonjour,

Lorsque je lance cette requete :

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
select o.ord_id,
       l.orl_id,
       o.odo_code,
       nvl(l.rcp_company, o.rcp_company) as rcp_company,
       nvl(l.rcp_contact, o.rcp_contact) as rcp_contact,
       o.ord_pricettc / 10000 as ord_pricettc,
       o.ord_reliquat / 10000 as ord_reliquat,
       abs(l.orl_qtytodel) as orl_qtytodel,
       abs(l.orl_qty) as orl_qty,
       l.orl_detail as prd_title,
       o.sta_invcode,
       l.sta_code,
       decode(l.sta_code, 'V830', 'A envoyer', 'Commande à éditer') libStatut,
       a.nfr_id,
       ld.dlo_code,
       nvl(l.oad_id, o.oad_id) as oad_id,
       o.oad_invid
  from ctb_order o
 inner join ctb_ordline l
    on (l.ord_id = o.ord_id)
 inner join ctb_ordaddress a
    on (l.oad_id = a.oad_id )
  left join ctb_ordldelivery ld
    on (ld.ord_id = l.ord_id and ld.orl_id = l.orl_id and
       ld.sta_code < 'Z999')
 where l.sta_code >= 'O500'
   and l.sta_code < 'V840'
   and l.del_code = 'REIM'
   and l.orl_canceldt is null

Elle s'exécute assez rapidement, environ 0,5 sec
Voici sont plan d'exécution

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT STATEMENT, GOAL = ALL_ROWS			1049	120	25080
 NESTED LOOPS					
  NESTED LOOPS			1049	120	25080
   NESTED LOOPS OUTER			689	120	20040
    NESTED LOOPS			404	120	17040
     TABLE ACCESS BY INDEX ROWID	CCE3	CTB_ORDLINE	164	120	15600
      INDEX RANGE SCAN	CCE3	IDX$$_60380001	5	251	
     TABLE ACCESS BY INDEX ROWID	CCE3	CTB_ORDADDRESS	2	1	12
      INDEX UNIQUE SCAN	CCE3	PK_CTB_ORDADDRESS	1	1	
    TABLE ACCESS BY INDEX ROWID	CCE3	CTB_ORDLDELIVERY	3	1	25
     INDEX RANGE SCAN	CCE3	PK_CTB_ORDLDELIVERY	2	1	
   INDEX RANGE SCAN	CCE3	PK_CTB_ORDER	2	1	
  TABLE ACCESS BY INDEX ROWID	CCE3	CTB_ORDER	3	1	42

par contre ce que je n'explique pas c'est lorsque je rajoute un filtre elle s'exécute en 60 sec...

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
select o.ord_id,
       l.orl_id,
       o.odo_code,
       nvl(l.rcp_company, o.rcp_company) as rcp_company,
       nvl(l.rcp_contact, o.rcp_contact) as rcp_contact,
       o.ord_pricettc / 10000 as ord_pricettc,
       o.ord_reliquat / 10000 as ord_reliquat,
       abs(l.orl_qtytodel) as orl_qtytodel,
       abs(l.orl_qty) as orl_qty,
       l.orl_detail as prd_title,
       o.sta_invcode,
       l.sta_code,
       decode(l.sta_code, 'V830', 'A envoyer', 'Commande à éditer') libStatut,
       a.nfr_id,
       ld.dlo_code,
       nvl(l.oad_id, o.oad_id) as oad_id,
       o.oad_invid
  from ctb_order o
 inner join ctb_ordline l
    on (l.ord_id = o.ord_id)
 inner join ctb_ordaddress a
    on (l.oad_id = a.oad_id )
  left join ctb_ordldelivery ld
    on (ld.ord_id = l.ord_id and ld.orl_id = l.orl_id and
       ld.sta_code < 'Z999')
 where l.sta_code >= 'O500'
   and l.sta_code < 'V840'
   and l.del_code = 'REIM'
   and l.orl_canceldt is null
   and a.nfr_id=3159893

Pour il y a bien un index sur NFR_ID.
Voici le plan d'exécution de cette requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT STATEMENT, GOAL = ALL_ROWS			18	1	209
 NESTED LOOPS					
  NESTED LOOPS			18	1	209
   NESTED LOOPS OUTER			15	1	167
    NESTED LOOPS			12	1	142
     INDEX RANGE SCAN	CCE3	CEFKOAD_HAV_NFR2	3	3	36
     TABLE ACCESS BY INDEX ROWID	CCE3	CTB_ORDLINE	4	1	130
      INDEX RANGE SCAN	CCE3	IDX$$_55D80001	2	2	
    TABLE ACCESS BY INDEX ROWID	CCE3	CTB_ORDLDELIVERY	3	1	25
     INDEX RANGE SCAN	CCE3	PK_CTB_ORDLDELIVERY	2	1	
   INDEX RANGE SCAN	CCE3	PK_CTB_ORDER	2	1	
  TABLE ACCESS BY INDEX ROWID	CCE3	CTB_ORDER	3	1	42

Le cout est vraiment réduit pourtant.
J'ai reconstruit les index mais rien n'a changé.

Auriez-vous une idée?

Merci d'avance.

**pacmann** · 22/10/2013, 16h22

Salut,

On ne voit pas bien les cardinalités estimées dans ton plan...

Peux tu sortir la différence entre cardinalités estimées et réelles comme montré ici ?
https://blogs.oracle.com/optimizer/e...w_do_i_know_if

A vu d'oeil, je suppose que le CBO estime à tort que le critère ajouté est plus sélectif que celui-ci : AND l.del_code = 'REIM'

Est-ce que "AND l.del_code = 'REIM'" correspond à une sélection très fine dans ta table ? Si oui, y a-t-il un histogramme dessus ?

**titwix** · 22/10/2013, 16h34

Voici en screenshot les 2 plans d'exécutions.

La condition "AND l.del_code = 'REIM'" est très restrictive sur le nombre de ligne.
Par contre, del_code a seulement une cinquantaine de valeurs possible.

**pacmann** · 22/10/2013, 17h07

Un histogramme serait probablement le bienvenu sur cette colonne del_code.

Et nous envoyer le résultat décrit de la méthode décrite dans mon lien, c'est possible ?
(Ca permettrait de confirmer exactement les erreurs d'estimation)

**titwix** · 22/10/2013, 17h31

Voici la répartition pour DEL_CODE
DEL_CODE NB %
CO69 1 0,00%
FO06 2 0,00%
TNCE 7 0,00%
FO04 7 0,00%
MULT 7 0,00%
NONE 14 0,00%
FO12 23 0,00%
FO02 27 0,00%
EPM 30 0,00%
FO11 33 0,00%
ENL2 57 0,00%
FO03 64 0,00%
FO09 69 0,00%
CHR6 75 0,00%
FO10 104 0,00%
CSC 104 0,00%
CCE 130 0,00%
CHR5 135 0,00%
FO13 171 0,00%
VOYA 176 0,00%
CHRO 197 0,00%
FO01 255 0,00%
FO07 300 0,00%
COCL 317 0,00%
PERS 380 0,00%
DA05 518 0,01%
COUR 648 0,01%
FO08 671 0,01%
FO05 689 0,01%
CHR3 707 0,01%
AUCE 2934 0,03%
CAP2 3507 0,04%
CHR4 4330 0,05%
CO75 4582 0,05%
CO92 4707 0,06%
CHR2 7519 0,09%
POST 8282 0,10%
VADE 8995 0,11%
GRAT 16793 0,20%
REIM 17939 0,21%
PONO 45117 0,53%
MAIL 63047 0,74%
CAP1 65887 0,77%
CHR1 69237 0,81%
SUIV 75898 0,89%
RECO 87816 1,03%
LENA 95828 1,13%
ETIC 115549 1,36%
COU2 167017 1,96%
FOUR 176235 2,07%
FCSV 229579 2,70%
ENGR 239298 2,81%
ENLE 315737 3,71%
COU1 320249 3,77%
FCCS 525707 6,18%
LEAS 1376207 16,18%
4451255 52,34%
TOTAL 8505169

Dans le fichier excel ci joint le résultat des 2 extractions.
1 et 2 .xls
J'ai changé volontairement des données pour des raisons de confidentialités.

**titwix** · 22/10/2013, 17h36

Pour la méthode dans le lien, désolée j'ai pas compris ce que je devais faire...

**pacmann** · 23/10/2013, 09h37

A) Concernant la méthode :
1) Exécute ta requête en ajoutant le hint /*+GATHER_PLAN_STATISTICS */
2) Exécute : SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
3) Le résultat est un tableau qui te permet de comparer pour chaque étape du plan d'exécution la colonne "E-rows" comme estimation à la colonne "A-rows" comme "actual" c-à-d effectif. Cela te permet de confirmer à quel endroit l'optimiseur s'est vautré dans ses estimations.

B) Recalcule tes statistiques en construisant un histogramme pour ta colonne DEL_CODE, cela permettra de prendre en compte le fait que la distribution des valeurs est très irrégulière

**titwix** · 23/10/2013, 10h58

voici ce que j'ai lorsque j'exécute SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID 9m7787camwh4m, child number 5

begin :id := sys.dbms_transaction.local_transaction_id; end;

NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 5
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Ensuite en ce qui concerne l'histogramme je me demande si j'ai tout bien compris.
Qu'est-ce que vous entendez exactement par histogramme?
Y a -t-il une instruction particulière?

**pacmann** · 24/10/2013, 13h25

Oracle, pour calculer combien de lignes il retourne, fait très basiquement : nombre de lignes au total / nombre de valeurs distinctes sur la colonne, ce qui signifie en gros que chaque valeur de la colonne est représentée à part égale dans la table.
Alors que quand il y a un histogramme, il est capable de faire une estimation qui tient compte de l'irrégularité de la distributions des valeurs.

Pour une description complète des calculs de l'optimiseur, cet article de Mnitu :
http://marius-nitu.developpez.com/tu...-calcule-cout/

Pour calculer un histogramme, il faut le préciser dans le paramètre METHOD_OPT de l'appel à dbms_stats :
dbms_stats.gather_tables_stats(ton_schema, ta_table, method => 'FOR COLUMNS del_code')

A discuter avec ton dba, bien sûr.

Explications plus complètes ici :
http://docs.oracle.com/cd/E16655_01/...gsql_histo.htm
https://blogs.oracle.com/optimizer/e...the_method_opt

**titwix** · 24/10/2013, 15h31

Voici ce que j'obtiens lors que je lance

Code :

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

1
2
3
4
5
 
SELECT  ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM   USER_HISTOGRAMS u
WHERE  TABLE_NAME='CTB_ORDLINE'
AND    COLUMN_NAME='DEL_CODE';

2 3,39228630301266E35
114 3,49208599602675E35
119 3,4920859991216E35
212 3,49350734926225E35
224 3,4935073523571E35
231 3,4935073585468E35
232 3,4935073647365E35
236 3,49490573871003E35
244 3,49490731398873E35
245 3,49491531727109E35
659 3,49492949477938E35
879 3,49492949787424E35
880 3,54398362397949E35
1229 3,59856161804135E35
1230 3,59856548041427E35
1676 3,59856553921642E35
1822 3,59978010075806E35
2549 3,64825035553864E35
2875 3,6482630413292E35
2876 3,6506690861153E35
3130 3,65069850266548E35
3150 3,70321410409614E35
4993 3,96019223067955E35
5111 3,96020247463338E35
5216 4,01131021946981E35
5271 4,16992263326304E35
5288 4,16992661014542E35
5404 4,27173161437549E35
5428 4,27173636187555E35
5535 4,32690454385113E35
5550 4,47861295366587E35
5551 4,4814691165451E35

Exécution plus longue avec une condition supplémentaire

SQL Oracle

Discussions similaires

Partager

Partager