Bonjour,

Depuis plusieurs jours, je m'arrache le peu de cheveux qu'il me reste afin d'optimiser une requête.

Le contexte :
- Base d'un ERP : impossible de modifier le modèle des données, ni les index
- Requête basée sur une vue : je n'ai accès qu'à la vue, je ne peux pas modifier la requête "englobante"
- Lors de la mise en production, le traitement durait 40 minutes
- Dès la semaine suivante, il est passé en dessous de 30 minutes
- Depuis le milieu de semaine dernière, il est passé à 2h40, alors qu'absolument rien n'a changé ni au niveau paramétrage, ni au niveau volumétrie
- Sur la base de DEV, qui est une copie récente de la PROD (moins de 1 semaine), le plan d'exécution proposé est deux fois plus long qu'en PROD

La vue à optimiser :
Code sql : 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
37
38
39
40
41
42
43
44
 
CREATE OR REPLACE VIEW SOC1.WV_QIFM30 (CODSOC, LIB1, LIB2, LIB3, LIB4, LIB5, LIB6, LIB7, LIB8, LIB9, LIB10, LIB11, LIB12, LIB13, LIB14, LIB15, LIB16, LIB17, LIB18, LIB19, LIB20, DAT1, DAT2, DAT3, DAT4, DAT5, NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09, NUM10, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20) AS 
select cde.codsoc,
 cde.achvte,cde.typeve,
 decode(
        UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16),
                                       '000'||substr(tie.sigtie,2,5)),
 cde.codeta,tie.codett,
 SUBSTR(decode(nvl(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, decode(substr(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8), /*liv.datliv*/
(
select max(liv.datliv)
from eve liv
where liv.codsoc = cde.codsoc and liv.achvto = cde.achvte and liv.typevo = cde.typeve and liv.numevo = cde.numeve
),
 cdp.codpro,' ',pro.codzn10,
 cde.codctg,'XXX',
 ' ',' ',' ',
 ' ',' ',' ',' ',' ',
 /*liv.datliv*/
(
select max(liv.datliv)
from eve liv
where liv.codsoc = cde.codsoc and liv.achvto = cde.achvte and liv.typevo = cde.typeve and liv.numevo = cde.numeve
)
 ,' ',' ',' ',' ',
 cde.numeve,
 nvl(to_number(trim(evt.codzn5)), cdp.qtecde),
 0,0,0,
 0,0,0,0,0,
 pru.coefuv,0,0,0,0,
 0,0,0,0,0,
 0,0,0,0,0,
 0,0,0,0,0
from 
eve cde 
inner join tie on tie.codsoc = cde.codsoc and tie.typtie = 'CLI' and tie.sigtie = cde.sigtie
inner join evp cdp on cdp.codsoc = cde.codsoc and cdp.achvte = cde.achvte and cdp.typeve = cde.typeve and cdp.numeve = cde.numeve
inner join pro on pro.codsoc = cdp.codsoc and pro.codpro = cdp.codpro
inner join prl on prl.codsoc = pro.codsoc and prl.codpro = pro.codpro and prl.typtie  = ' ' and prl.sigtie = ' ' and prl.codva1= '00' and prl.codva2 = SUBSTR(pro.codpro,8,2) and prl.cntcod = ' '
inner join pru on pru.codsoc = prl.codsoc and pru.codpro = prl.codpro and pru.coduni = prl.coduni1
inner join evt on evt.codsoc = cdp.codsoc and evt.achvte = cdp.achvte and evt.typeve = cdp.typeve and evt.numeve = cdp.numeve and evt.numpos = cdp.numpos and evt.numlig = 0 and evt.numspo = 0 and evt.numblo = 0
left outer join prc on prc.codsoc = cde.codsoc and prc.typtie = 'CLI' and prc.sigfou = tie.sigtie and prc.codpro = pro.codpro
left outer join pro p2 on p2.codsoc = prc.codsoc and p2.codpro = prc.codzn2
where cde.codctg not in ('DE','GR','EC');

Elle est utilisée, pour les tests, avec la requête :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
 
select count(*)  from wv_qifm30 where codsoc = 100 and lib1 = 'V' and lib2 = 'CDE' and dat1 BETWEEN '20120215' and '20120228' and lib8 = '212690000' and  (lib4 = 'V' or lib4 = 'S')  and  (lib5 = 'ME1' or lib5 = 'ME2');

Sur la DEV, le coût estimé est de 405.

La piste que j'ai choisi pour optimiser, c'est le remplacement du double sous-select dans les champs par un max() et l'ajout d'une jointure.

On a donc à la place :
Code sql : 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
37
38
39
40
41
42
43
44
45
46
47
48
49
 
CREATE OR REPLACE VIEW SOC1.WV_QIFM30_BIS (CODSOC, LIB1, LIB2, LIB3, LIB4, LIB5, LIB6, LIB7, LIB8, LIB9, LIB10, LIB11, LIB12, LIB13, LIB14, LIB15, LIB16, LIB17, LIB18, LIB19, LIB20, DAT1, DAT2, DAT3, DAT4, DAT5, NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09, NUM10, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20) AS 
select cde.codsoc,
 cde.achvte,cde.typeve,
 decode(
        UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16),
                                       '000'||substr(tie.sigtie,2,5)),
 cde.codeta,tie.codett,
 SUBSTR(decode(nvl(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, decode(substr(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8), /*liv.datliv*/
nvl(max(liv.datliv), ' '),
 cdp.codpro,' ',pro.codzn10,
 cde.codctg,'XXX',
 ' ',' ',' ',
 ' ',' ',' ',' ',' ',
 /*liv.datliv*/
nvl(max(liv.datliv), ' ')
,' ',' ',' ',' ',
 cde.numeve,
 nvl(to_number(trim(evt.codzn5)), cdp.qtecde),
 0,0,0,
 0,0,0,0,0,
 pru.coefuv,0,0,0,0,
 0,0,0,0,0,
 0,0,0,0,0,
 0,0,0,0,0
from 
eve cde 
inner join tie on tie.codsoc = cde.codsoc and tie.typtie = 'CLI' and tie.sigtie = cde.sigtie
inner join evp cdp on cdp.codsoc = cde.codsoc and cdp.achvte = cde.achvte and cdp.typeve = cde.typeve and cdp.numeve = cde.numeve
inner join pro on pro.codsoc = cdp.codsoc and pro.codpro = cdp.codpro
inner join prl on prl.codsoc = pro.codsoc and prl.codpro = pro.codpro and prl.typtie  = ' ' and prl.sigtie = ' ' and prl.codva1= '00' and prl.codva2 = SUBSTR(pro.codpro,8,2) and prl.cntcod = ' '
inner join pru on pru.codsoc = prl.codsoc and pru.codpro = prl.codpro and pru.coduni = prl.coduni1
inner join evt on evt.codsoc = cdp.codsoc and evt.achvte = cdp.achvte and evt.typeve = cdp.typeve and evt.numeve = cdp.numeve and evt.numpos = cdp.numpos and evt.numlig = 0 and evt.numspo = 0 and evt.numblo = 0
left outer join prc on prc.codsoc = cde.codsoc and prc.typtie = 'CLI' and prc.sigfou = tie.sigtie and prc.codpro = pro.codpro
left outer join pro p2 on p2.codsoc = prc.codsoc and p2.codpro = prc.codzn2
left outer join eve liv on liv.codsoc = cde.codsoc and liv.achvto = cde.achvte and liv.typevo = cde.typeve and liv.numevo = cde.numeve
where cde.codctg not in ('DE','GR','EC')
group by cde.codsoc,
cde.achvte,cde.typeve,
 decode(
        UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16),
                                       '000'||substr(tie.sigtie,2,5)),
 cde.codeta,tie.codett,
 SUBSTR(decode(nvl(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, decode(substr(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8),
 cdp.codpro,' ',pro.codzn10,
 cde.codctg,
 cde.numeve,
 nvl(to_number(trim(evt.codzn5)), cdp.qtecde),
 pru.coefuv;

Je l'appelle avec la requête suivante :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
 
select count(*) from wv_qifm30_bis where codsoc = 100 and lib1 = 'V' and lib2 = 'CDE' and dat1 BETWEEN '20120215' and '20120228' and lib8 LIKE '212690000' and  (lib4 = 'V' or lib4 = 'S')  and  (lib5 = 'ME1' or lib5 = 'ME2');
Coût estimé en DEV 401
=> Chouette me dis-je, c'est pas grand chose, mais c'est toujours ça de gagné !

En revanche, quand je lance la première requête, elle donne 101 lignes pour 10 minutes et 20 secondes de traitement.
Et quand le lance la seconde requête, elle donne bien 101 lignes, mais passe à 12 minutes et 20 secondes de traitement.

Le serveur de DEV étant virtualisé, je ne peux pas garantir l'absence de charge. Mais j'ai fais tourner plusieurs fois les requêtes, et c'est le même ordre de grandeur à chaque fois.

Et sur la production, les coûts estimés sont de 223 pour les deux requêtes. Je n'ai pas encore testé combien de temps ça durait.

J'aimerais donc comprendre :
- Comment il est possible que la même requête ait un plan d'exécution totalement différent entre PROD et DEV, alors que les bases sont identiques.
- Comment une requête ayant un plan d'exécution moins coûteux puisse être plus lente
- Et surtout, comment un left outer join avec un agrégat peut être plus lent qu'un sous-select de bourrin directement dans la clause select (réputé pourtant comme étant particulièrement lent !)

Franchement, Oracle porte bien son nom... Demain je vais sacrifier un poulet sur mon clavier, j'y verrai peut-être plus clair dans ses entrailles !