Optimisation d'un écran : paramétrage Oracle déconnant ?

**StringBuilder** · 10/03/2014, 10h05

Bonjour,

J'essaie d'optimiser le temps d'affichage d'un écran d'une application web.

L'application repose sur un Framework qui permet de matcher une table à un objet métier.
Ainsi, dans les écrans complexes où il y a de nombreuses requêtes, on se retrouve avec ce genre de choses :

Select principal (point d'entrée)

Code sql :

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

1
2
3
4
5
6
7
 
select *
form table1
where [filtres]
and pk not in (select table2)
and pk in (select table3)
...

Et ensuite, pour chaque ligne retournée par table1 et pour chaque colonne de clé étrangère dont on veut connaître le détail (avec un niveau de cascade parfois important) on a :

Code sql :

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

1
2
3
 
select *
from table3 where pk = fk_table1

Lorsque la première requête ramène par exemple 100 lignes, vu qu'on affiche que les 25 premières, ces requêtes pour retrouver les valeurs des clés étrangères ne sont exécutées que sur les 25 premières lignes.

En consultant le log de l'application, pour charger la page que j'essaie d'optimiser, je me retrouver avec... 2500 requêtes (oui oui, pour une seule page).

Je retrousse mes manches.

Et j'écrit une requête d'une cinquantaine de lignes, qui va directement rechercher tout ce qu'il faut partout. Il n'y a plus de "in" et "exists" à toutes les sauces.
Et le chargement de la page ne fait plus qu'une seule requête.

En revanche, évidement, si 100 lignes sont retournées, les valeurs des clés étrangères sont récupérées pour les 100 lignes...

Voici les résultats sur nos différents environnement :

Plan d'exécution identique sur l'ensemble des 3 environnement (constaté avec la console Oracle) :
- Une quinzaine de UNIQUE INDEX SEEK
- Deux INDEX RANGE SCAN
- Coût de 68000 malgré la vingtaine de jointures

DEV : (base petite, configuration limitée)
Ancienne page : 5 minutes
Nouvelle page : 3 secondes
Résultat plutôt encourageant !

REC : (base de même volume que la prod, et machine très limitée)
Anciene page : 8 minutes
Nouvelle page : 10 minutes ou plus (on a un timeout après 10 minutes, et on l'obtiens 2 fois sur 3)
Résultat très déroutant !

PROD : (base de grande taille, et machine très puissante)
Ancienne page : 25 secondes
Nouvelle page : 14 secondes
Je suis perplexe

Bref, je ne m'explique pas du tout pourquoi :
- En recette c'est pire avec la nouvelle requête
- En production le gain est aussi limité alors qu'en DEV il est extraordinaire

Y a-t-il un paramètre dans Oracle qui fasse que des petites requêtes unitaires sur une PK soient extrêmement performantes, alors que des requêtes avec jointures (il n'y a aucune FK explicite dans la base, ceci dit, pas de PK non plus, il n'y a que des UNIQUE INDEX) soit lente ?

Accessoirement, les requêtes originale font systématiquement un SELECT de l'ensemble des colonnes de chaque table (plus de 200 au final) alors que ma requête écrite à la main ne récupère que les quelques dizaines dont j'ai besoin.

Voici la tronche de ma requête "optimisée" :

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
 
SELECT
  EVP.CODSOC,
  EVP.ACHVTE,
  EVP.TYPEVE,
  EVP.NUMEVE,
  EVP.NUMPOS,
  EVP.QTEINI,
  EVP.QTECDE,
  EVP.CODUNI,
  EVP.TYPTIE,
  EVP.SIGMAG,
  EVP.CODPRO,
  EVP.CODFIL,
  EVP.SIGREP,
  EVP.NUMCNT,
  EVL.INDAPR EVl_INDAPR,
  EVL.DATLIV EVL_DATLIV,
  EVL.DATEXP EVL_DATEXP,
  EVTP.NUMLIG,
  EVTP.COMMEN1,
  EVTP.COMMEN2,
  EVTP.COMMEN3,
  EVTP.COMMEN4,
  EVTP.COMMEN5,
  EVTP.COMMEN6,
  EVTP.COMMEN7,
  EVTP.COMMEN8,
  EVTP.COMMEN9,
  EVTP.COMMEN10,
  EVTP.CODZN12 EVP_CODZN12,
  EVTP.CODZN13 EVP_CODZN13,
  EVTP.CODZN14 EVP_CODZN14,
  EVTP.CODZN15 EVP_CODZN15,
  FIL_E1.LIBFIL LIBFIL_1,
  FIL_E1.VALZN2 VALZN2_1,
  FIL_E1.VALZN5 VALZN5_1,
  FIL_E2.LIBFIL LIBFIL_2,
  FIL_E2.VALZN2 VALZN2_2,
  FIL_E2.VALZN5 VALZN5_2,
  MAG.NOMTIE,
  EVE.SIGDEP,
  EVE.DATEVE EVE_DATEVE,
  EVE.SIGACT,
  EVE.SIGTIE,
  EVE.CODETA,
  EVE.INDAPR EVE_INDAPR,
  EVE.CODCTG,
  EVE.DATLIV EVE_DATLIV,
  EVE.DATEXP EVE_DATEXP,
  EVE.NUMEVO,
  EVE.DATMOD EVE_DATMOD,
  EVTE.CODZN16 EVE_CODZN16,
  ZODDEP201.VALZOD DEP_VALZOD201,
  ZODDEP203.VALZOD DEP_VALZOD203,
  PRCDEP.CODZN1 PRCDEP_CODZN1,
  PRCDEP.CODZN2 PRCDEP_CODZN2,
  ZODPR3156.VALZOD PR3_VALZOD156,
  PRCFOU.CODZN1 PRCFOU_CODZN1,
  PRCFOU.CODZN2 PRCFOU_CODZN2,
  PRO.NOMPRO
FROM
  mev meveve
INNER JOIN mev mevdep
ON
  mevdep.codsoc    = meveve.codsoc
AND mevdep.codent  = 'TIE'
AND mevdep.segment = 'DEP'
INNER JOIN mev mevmag
ON
  mevmag.codsoc    = meveve.codsoc
AND mevmag.codent  = 'TIE'
AND mevmag.segment = 'FOU'
INNER JOIN mev mevfile
ON
  mevfile.codsoc    = meveve.codsoc
AND mevfile.codent  = 'FIL_E'
AND mevfile.segment = ' '
INNER JOIN mev mevzoddep
ON
  mevzoddep.codsoc    = meveve.codsoc
AND mevzoddep.codent  = 'ZOD'
AND mevzoddep.segment = 'DEP'
INNER JOIN eve
ON
  eve.codsoc = meveve.codsoc_phy
INNER JOIN evp
ON
  evp.codsoc   = eve.codsoc
AND evp.achvte = eve.achvte
AND evp.typeve = eve.typeve
AND evp.numeve = eve.numeve
INNER JOIN evl
ON
  evl.codsoc   = evp.codsoc
AND evl.achvte = evp.achvte
AND evl.typeve = evp.typeve
AND evl.numeve = evp.numeve
AND evl.numpos = evp.numpos
AND evl.numlig = 1
INNER JOIN evt evte
ON
  evte.codsoc   = eve.codsoc
AND evte.achvte = eve.achvte
AND evte.typeve = eve.typeve
AND evte.numeve = eve.numeve
AND evte.numpos = 0
AND evte.numlig = 0
AND evte.numspo = 0
AND evte.numblo = 0
INNER JOIN evt evtp
ON
  evtp.codsoc   = evp.codsoc
AND evtp.achvte = evp.achvte
AND evtp.typeve = evp.typeve
AND evtp.numeve = evp.numeve
AND evtp.numpos = evp.numpos
AND evtp.numlig = 0
AND evtp.numspo = 0
AND evtp.numblo = 0
INNER JOIN tie dep
ON
  dep.codsoc   = mevdep.codsoc_phy
AND dep.typtie = mevdep.segment
AND dep.sigtie = evl.sigdep
INNER JOIN tie mag
ON
  mag.codsoc   = mevmag.codsoc_phy
AND mag.typtie = mevmag.segment
AND mag.sigtie = evp.sigmag
INNER JOIN pro
ON
  pro.codsoc   = 1
AND pro.codpro = evp.codpro
LEFT OUTER JOIN fil_e fil_e1
ON
  fil_e1.codsoc   = mevfile.codsoc_phy
AND fil_e1.codfil = evp.codfil
LEFT OUTER JOIN fil_e fil_e2
ON
  fil_e2.codsoc   = mevfile.codsoc_phy
AND fil_e2.codfil = evtp.codzn15
LEFT OUTER JOIN zod zoddep201
ON
  zoddep201.codsoc   = mevzoddep.codsoc_phy
AND zoddep201.typzod = mevzoddep.segment
AND zoddep201.numzod = 201
AND zoddep201.clezod = eve.sigdep
LEFT OUTER JOIN zod zoddep203
ON
  zoddep203.codsoc   = mevzoddep.codsoc_phy
AND zoddep203.typzod = mevzoddep.segment
AND zoddep203.numzod = 203
AND zoddep203.clezod = eve.sigdep
LEFT OUTER JOIN prc prcdep
ON
  prcdep.codsoc   = 1
AND prcdep.typtie = 'DEP'
AND prcdep.sigfou = eve.sigdep
AND prcdep.codpro = evp.codpro
LEFT OUTER JOIN prc prcfou
ON
  prcfou.codsoc   = 1
AND prcfou.typtie = 'FOU'
AND prcfou.sigfou = eve.sigmag
AND prcfou.codpro = evp.codpro
LEFT OUTER JOIN zod zodpr3156
ON
  zodpr3156.codsoc   = meveve.codsoc_phy
AND zodpr3156.typzod = 'PR3'
AND zodpr3156.numzod = 156
AND zodpr3156.clezod = evp.codpro
WHERE
  meveve.codsoc    = ?{1=Codsoc}
AND meveve.codent  = 'EVE'
AND meveve.segment = ' '
AND eve.achvte     = ?{2=PEV:ACHVTE}
AND eve.typeve     = ?{3=PEV:TYPEVE} [
AND evl.sigdep     = ?{4=Sigdep}]
AND
  (
    ?{5         =Indapr} = 'N'
  OR evl.indapr = ?{6=Indapr}
  )
AND
  (
    (
      (
        evl.indapr  = 'X'
      OR evl.indapr = 'O'
      )
    AND evl.utimod = ?{7=Uti}
    )
  OR evl.indapr = 'N'
  )
AND
  (
    (
      evl.indapr   = 'O'
    AND evl.datmod = TO_CHAR(SYSDATE, 'YYYYMMDD')
    )
  OR evl.indapr <> 'O'
  )
AND
  (
    (
      (
        SUBSTR(evtp.codzn12, 1, 1) = ?{8=Typarb} [
      AND evp.codfil               = ?{9=Codfil}] [
      OR
        (
          NVL(evp.Codfil, ' ') = ' '
        AND 'NoFil'            = ?{10=NoFil}
        )
        ]
      )
      [
    AND 'NoFilOnly' != ?{11=NoFilOnly}]
    )
    [
  OR
    (
      NVL(evp.codfil, ' ') = ' '
    AND 'NoFilOnly'        = ?{12=NoFilOnly}
    )
    ]
  )
AND eve.Codeta = ?{13=PEV:VALETA} [
AND dep.Codzn2 = ?{14=Agri}
OR
  (
    trim(fil_e1.valzn7)   IS NOT NULL
  AND fil_e1.valzn7        = ?{15=Agri}
  AND trim(fil_e2.valzn7) IS NOT NULL
  AND fil_e2.valzn7        = ?{16=Agri}
  )
  ] [
AND evp.codpro  = ?{17=Codpro}] [
AND pro.codzn1  = ?{18=Codzn1Pro}] [
AND pro.codzn2 IN (~{19=Codzn2Pro})] [
AND evp.sigmag  = ?{20=Sigmag}]

Les parties entre crochet signifient qu'elles sont optionnelles et donc pas forcément présentes au moment de l'exécution (ça dépends si le paramètre correspondant est présent ou non)

**mnitu** · 10/03/2014, 14h06

Difficile de répondre avec seulement ces informations. En principe votre démarche est correct mais cela ne va pas vous dire que se passet-il réellement.
Commencez par vous assurez que le paramétrage des bases est bien le même ainsi que le calcul des statistiques, etc. Par la suite essayez d’activer une trace SQL étendue au niveau applicatif pour avoir une image de ce que se passe dans chaque cas.
Et donnez-nous aussi une petite idée de la volumétrie impliquée dans chaque situation ainsi que la version d'Oracle.

**Rei Ichido** · 12/03/2014, 11h42

Envoyé par StringBuilder

Plan d'exécution identique sur l'ensemble des 3 environnement (constaté avec la console Oracle) :
- Une quinzaine de UNIQUE INDEX SEEK
- Deux INDEX RANGE SCAN
- Coût de 68000 malgré la vingtaine de jointures

Dans les 3 environnements il fait les mêmes résolutions de jointure (Nested Loop / Hash Join) ?
Selon les statistiques (qui peuvent être mal actualisées) sur la recette, cela pourrait l'induire à choisir un type de jointure contre-productif. Ou à ne pas partir de la bonne table.
Si les volumétries sont très différentes fonctionnellement parlant (même nombre d'objets, mais ils sont tous dans un statut donné alors que sur la prod c'est très réparti entre 10 statuts, par exemple), là encore il y a un impact fort (le même filtre n'a pas du tout la même importance sur les 2 environnements).

Il faudrait voir le détail des plans !

**skuatamad** · 14/03/2014, 14h29

Plan d'exécution identique sur l'ensemble des 3 environnement

Pour votre requête avec jointure regardez le hint GATHER_PLAN_STATISTICS pour une comparaison rapide des estimations vs la réalité :
Afficher le plan d’exécution avec les stats de chaque étape

Optimisation d'un écran : paramétrage Oracle déconnant ?

SQL Oracle

Discussions similaires

Partager

Partager