Optimisation de requête : recherche boule de crystal

**StringBuilder** · 29/02/2012, 16h52

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 !

**mnitu** · 29/02/2012, 17h28

Envoyé par StringBuilder

...
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 !

Il y a un certain nombre des explications rationnelles nulle besoin de sacrifier vos poulles

Divers différences dans le paramétrage, statistiques systèmes, statistiques objets entre les deux bases, voir quelques phénomènes comme l’introspection des variables de liaison (bind variable peeking)
Le calcul du coût se base sur des estimations et donc ne vaut pas l’exécution de la requête. Pour ne parler que si vous ne fait pas correctement les choses vous comparez des poules avec des dindons facilement
C’est un mythe que les requêtes scalaires sont toujours moins performantes. En fait Oracle utilise un cache pour ne exécuter les requêtes scalaires que si c'est nécessaire et donc dans certaines conditions elles peuvent être bien plus performantes.

Il va falloir mettre de l’ordre dans votre tentative d’optimisation si vous voulez vous en sortir.

**StringBuilder** · 29/02/2012, 17h39

Envoyé par mnitu

C’est un mythe que les requêtes scalaires sont toujours moins performantes. En fait Oracle utilise un cache pour ne exécuter les requêtes scalaires que si c'est nécessaire et donc dans certaines conditions elles peuvent être bien plus performantes.

Dans mon cas, le sous-select est bien exécuté à chaque ligne, puisqu'il a forcément une valeur pour chaque ligne retournée : en effet, il s'agit du champ "dat1" de la vue, qui est ensuite filtré avec un between sur la requêtes "englobante". Oracle est donc obligé de se pallucher la requête pour toutes les lignes.

Tiens, ça me fait penser qu'au lieu d'utiliser un left outer join, je peux utiliser un inner join et virer le nvl() autour du max ! Vais voir si ça change quelque chose...

Envoyé par mnitu

Il va falloir mettre de l’ordre dans votre tentative d’optimisation si vous voulez vous en sortir.

Bah je vois pas ce que je peux faire de plus (mise à par le coup du inner join, m'a l'air pas mal lui).
En effet, il s'agit d'une extraction de données, donc toutes les tables lues sont nécessaires, puisqu'elles permettent de récupérer les données en question.
La chiée de decode() qui me sortent par les yeux, idem...
Quand aux jointures, elles se font systématiquement sur des index, et la plupart du temps sur des index uniques (il n'y a pas de PK définies dans la base)

**StringBuilder** · 29/02/2012, 18h04

Bon, ben inner ou outer... même donne

J'ai gagné deux minutes sur la version optimisée

Mais j'ai gagné aussi deux minutes sur la version que j'ai pas touché

Y'en a marre aussi de bosser sur un serveur virtualisé tout merdique.
Je suis pourtant absolument seul sur ce serveur, y'a pas un seul traitement qui tourne... Mais non, d'une heure à l'autre le serveur est plus ou moins lent... Pfffff...

Du coup j'ai toujours un écart de l'ordre de 2 minutes entre mes deux requêtes...
Bon, demain je teste avec des sous-select partout, peut-être que finalement l'optimiseur fait son travail quand on écrit des requêtes pourries ? Plus ça va, plus j'en suis convaincu... Comme le jour où j'ai réécrit avec des "inner join" une requête où les tables étaient énumérées à la suite... 4 fois plus lent le résultat... tout ça parce que l'ordre des tables dans le from permet à Oracle d'utiliser un plan d'exécution différent, chose que l'optimiseur est incapable de faire avec la syntaxe standard ! Pffffff...

**pachot** · 01/03/2012, 21h49

Bonjour,

je vais essayer d'être plus court que sur http://www.developpez.net/forums/d11...is-suis-lourd/

Le coût estimé n'a pas de sens car ce n'est qu'une estimation pour Oracle.
Un coût de 405 c'est faible: le coût est une approximation du nombre d'i/o à faire. Dans les 400 c'est qu'il s'attend à faire moins de quelques secondes. Donc il y a probablement une erreur d'estimation.
Il faut regarder le plan d'exécution complet.

Le temps d'exécution n'a pas de sens sur un test. Il y a des données qui restent en cache et des plans qui sont parfois optimisés d'une requête à l'autre.
Il faut regarder les statistiques (autotrace sous sqlplus par exemple)

Avec statistics_level=all et dbms_xplan il assez facile de comparer les estimations avec le réel. SQLDeveloper offre une belle interface pour cela.

- Base d'un ERP : impossible de modifier le modèle des données, ni les index

Il est clair que l'appli est pourrie, à en voir:
- Les LIB1, LIB2, LIB3, LIB4, LIB5, LIB6...
- Les select max sur lesquels vont s'appliquer des prédicats
- les dates au format chaîne de caractère
- les ' '
- les to_number sans préciser le format du nombre
etc...

Faut pas espérer des miracles

Cordialement,
Franck.

**Rei Ichido** · 02/03/2012, 10h32

Envoyé par StringBuilder

- 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.

Différences de statistiques au moment de la génération du plan.

- Comment une requête ayant un plan d'exécution moins coûteux puisse être plus lente

Le "coût" est une donnée estimée, ne pas s'y fier.

- 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 !)

L'aggrégation sur l'ensemble des résultats n'est forcément brillant non plus. Selon les volumétries mises en jeu - mais là encore, comme le signalent mnitu et pachot : il faut voir au cas par cas - il peut être plus intéressant de réaliser l'aggrégation avant la jointure.

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 !

Ça serait du gachis ! Alors que rôtis, mhhh

Optimisation de requête : recherche boule de crystal

SQL Oracle

Discussions similaires

Partager

Partager