Bonjour,

J'ai écrit un ensemble de requêtes, dont le temps total d'exécution n'est vraiment pas satisfaisant.

J'ai donc passé au TkProf toutes ces requêtes, et en ai isolées deux d'entres elles, pour lesquelles dans la ligne "Fetch", la valeur de la colonne "elapsed" est vraiment beaucoup plus élevée que la colonne "cpu".

Or, il est écrit, entre autres dans le guide http://oracle.developpez.com/guide/tuning/tkprof/#LV , qu'il ne faut pas que les valeurs de ces 2 colonnes soient trop éloignées.

A quoi ce souci peut-il être dû? Mes requêtes font trop d'accès disque? Se peut-il qu'il y ait des wait anormaux?

Merci d'avance.

Ci-dessous les traces des 2 requêtes.

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
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
********************************************************************************
 
SELECT EU1.IDTELM , AP1.IDTAFLPLN , NVL (EU1.NIVAPL, EL1.NIVAPL) NIVAPL , NVL 
  (VE1.VALELM, EL1.VALDEF) VALELM , EL1.NUMORD , NOMAFL , PRNAFL , MTLAFL 
FROM
 ELEMENT_UTILISE EU1 , ELEMENT_MGC EL1 , AFFILIE_PLAN AP1 , 
  VALEUR_ELEMENT_MGC VE1 , JEU_DONNEE JD1 , AFFILIE AF1 , TABLE (CAST (:B1 AS 
  T_IDT)) LD1 WHERE EU1.IDTRGG = :B4 AND EU1.IDTELM = EL1.IDTELM AND 
  VE1.IDTELM = EL1.IDTELM AND AP1.IDTPLN = :B3 AND VE1.IDTAFLPLN = 
  AP1.IDTAFLPLN AND NVL (EU1.NIVAPL, EL1.NIVAPL) = 'AFFILIE_PLAN' AND 
  JD1.IDTLOTJEU = :B2 AND JD1.IDTAFLPLN = AP1.IDTAFLPLN AND IDTVALAUT IS NULL 
  AND AP1.IDTAFL = AF1.IDTAFL AND AP1.IDTAFLPLN = LD1.IDT1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.11       0.16          0          3          0           0
Fetch        5      0.48       8.56       4215      23462          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.59       8.73       4215      23465          0           4
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 53  (a00)   (recursive depth: 1)
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: CHOOSE
      0   HASH JOIN
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      HASH JOIN
      0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                  'VALEUR_ELEMENT_MGC' (TABLE)
      0        NESTED LOOPS
      0         HASH JOIN
      0          TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                     'ELEMENT_UTILISE' (TABLE)
      0           INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                      'ELMUTL_RGG_FK_I' (INDEX)
      0          TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                     'ELEMENT_MGC' (TABLE)
      0         INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                    'VALELMMGC_UK1' (INDEX (UNIQUE))
      0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                  'AFFILIE_PLAN' (TABLE)
      0        INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                   'AFLPLN_PLN_FK_I' (INDEX)
      0      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'JEUDON_UK' (INDEX 
                 (UNIQUE))
      0     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'AFFILIE' 
                (TABLE)
      0      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'AFL_PK' (INDEX 
                 (UNIQUE))
      0    COLLECTION ITERATOR (PICKLER FETCH)
 
 
********************************************************************************
 
SELECT IDTELM , IDTAFLPLN , NIVAPL , VALDEF VALELM , NUMORD , NOMAFL , PRNAFL 
  , MTLAFL 
FROM
 ( SELECT EU1.IDTELM , AP1.IDTAFLPLN , NVL (EU1.NIVAPL, EL1.NIVAPL) NIVAPL , 
  EL1.NUMORD , NOMAFL , PRNAFL , VALDEF , MTLAFL FROM ELEMENT_UTILISE EU1 , 
  ELEMENT_MGC EL1 , AFFILIE_PLAN AP1 , JEU_DONNEE JD1 , RESULTAT_CALCUL RC1 , 
  AFFILIE AF1 , TABLE (CAST (:B1 AS T_IDT)) LD1 WHERE EU1.IDTRGG = :B3 AND 
  EU1.IDTELM = EL1.IDTELM AND NVL (EU1.NIVAPL, EL1.NIVAPL) = 
  'RESULTAT_CALCUL' AND RC1.IDTCALPLN = :B2 AND RC1.IDTJEUDON = JD1.IDTJEUDON 
  AND JD1.IDTAFLPLN = AP1.IDTAFLPLN AND AP1.IDTAFL = AF1.IDTAFL AND NVL 
  (EL1.TYPELM, '|$#') <> 'PVF' AND AP1.IDTAFLPLN = LD1.IDT1 AND NOT EXISTS ( 
  SELECT '1' FROM JEU_DONNEE JD2 , LOT_JEU_DONNEE LJ2 WHERE LJ2.IDTLOTJEU = 
  :B4 AND LJ2.IDTPLN = AP1.IDTPLN AND JD2.IDTLOTJEU = LJ2.IDTLOTJEU AND 
  JD2.IDTAFLPLN = AP1.IDTAFLPLN) MINUS ( SELECT EU1.IDTELM , AP1.IDTAFLPLN , 
  NVL (EU1.NIVAPL, EL1.NIVAPL) NIVAPL , EL1.NUMORD , NOMAFL , PRNAFL , VALDEF 
  , MTLAFL FROM ELEMENT_UTILISE EU1 , ELEMENT_MGC EL1 , AFFILIE_PLAN AP1 , 
  JEU_DONNEE JD1 , RESULTAT_CALCUL RC1 , VALEUR_ELEMENT_MGC VE1 , AFFILIE AF1 
  , TABLE (CAST (:B1 AS T_IDT)) LD1 WHERE EU1.IDTRGG = :B3 AND EU1.IDTELM = 
  EL1.IDTELM AND JD1.IDTAFLPLN = AP1.IDTAFLPLN AND VE1.IDTELM = EL1.IDTELM 
  AND VE1.IDTRSUCAL = RC1.IDTRSUCAL AND RC1.IDTCALPLN = :B2 AND RC1.IDTJEUDON 
  = JD1.IDTJEUDON AND NVL (EU1.NIVAPL, EL1.NIVAPL) = 'RESULTAT_CALCUL' AND 
  AP1.IDTAFL = AF1.IDTAFL AND NVL (EL1.TYPELM, '|$#') <> 'PVF' AND 
  AP1.IDTAFLPLN = LD1.IDT1 AND NOT EXISTS ( SELECT '1' FROM JEU_DONNEE JD2 , 
  LOT_JEU_DONNEE LJ2 WHERE LJ2.IDTLOTJEU = :B4 AND LJ2.IDTPLN = AP1.IDTPLN 
  AND JD2.IDTLOTJEU = LJ2.IDTLOTJEU AND JD2.IDTAFLPLN = AP1.IDTAFLPLN) UNION 
  ALL SELECT EU1.IDTELM , AP1.IDTAFLPLN , NVL (EU1.NIVAPL, EL2.NIVAPL) NIVAPL 
  , EL2.NUMORD , NOMAFL , PRNAFL , EL2.VALDEF , MTLAFL FROM ELEMENT_UTILISE 
  EU1 , ELEMENT_MGC EL1 , ELEMENT_MGC EL2 , AFFILIE_PLAN AP1 , JEU_DONNEE JD1 
  , RESULTAT_CALCUL RC1 , VALEUR_ELEMENT_MGC VE1 , AFFILIE AF1 , TABLE (CAST 
  (:B1 AS T_IDT)) LD1 WHERE EU1.IDTRGG = :B3 AND EU1.IDTELMRMP = EL1.IDTELM 
  AND EU1.IDTELM = EL2.IDTELM AND JD1.IDTAFLPLN = AP1.IDTAFLPLN AND 
  VE1.IDTELM = EL1.IDTELM AND VE1.IDTRSUCAL = RC1.IDTRSUCAL AND RC1.IDTCALPLN 
  = :B2 AND RC1.IDTJEUDON = JD1.IDTJEUDON AND AP1.IDTAFL = AF1.IDTAFL AND NVL 
  (EL1.TYPELM, '|$#') <> 'PVF' AND AP1.IDTAFLPLN = LD1.IDT1 AND NOT EXISTS ( 
  SELECT '1' FROM JEU_DONNEE JD2 , LOT_JEU_DONNEE LJ2 WHERE LJ2.IDTLOTJEU = 
  :B4 AND LJ2.IDTPLN = AP1.IDTPLN AND JD2.IDTLOTJEU = LJ2.IDTLOTJEU AND 
  JD2.IDTAFLPLN = AP1.IDTAFLPLN))) 
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.17       0.26          0          3          0           0
Fetch        1      1.49       8.16       3940      10857          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.66       8.42       3940      10860          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 53  (a00)   (recursive depth: 1)
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: CHOOSE
      0   VIEW
      0    MINUS
      0     SORT (UNIQUE)
      0      FILTER
      0       HASH JOIN
      0        HASH JOIN
      0         HASH JOIN
      0          HASH JOIN
      0           MERGE JOIN (CARTESIAN)
      0            HASH JOIN
      0             TABLE ACCESS   MODE: ANALYZED (BY INDEX 
                        ROWID) OF 'ELEMENT_UTILISE' (TABLE)
      0              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'ELMUTL_RGG_FK_I' (INDEX)
      0             TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                        'ELEMENT_MGC' (TABLE)
      0            BUFFER (SORT)
      0             TABLE ACCESS   MODE: ANALYZED (BY INDEX 
                        ROWID) OF 'RESULTAT_CALCUL' (TABLE)
      0              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'RSUCAL_CALPLN_FK_I' (INDEX)
      0           TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                      'JEU_DONNEE' (TABLE)
      0          TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                     'AFFILIE_PLAN' (TABLE)
      0         COLLECTION ITERATOR (PICKLER FETCH)
      0        TABLE ACCESS   MODE: ANALYZED (FULL) OF 'AFFILIE' 
                   (TABLE)
      0       NESTED LOOPS
      0        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                   'LOT_JEU_DONNEE' (TABLE)
      0         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'LOTJEU_PK' 
                    (INDEX (UNIQUE))
      0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'JEUDON_UK' 
                   (INDEX (UNIQUE))
      0     SORT (UNIQUE)
      0      UNION-ALL
      0       FILTER
      0        NESTED LOOPS
      0         HASH JOIN
      0          NESTED LOOPS
      0           NESTED LOOPS
      0            HASH JOIN
      0             TABLE ACCESS   MODE: ANALYZED (BY INDEX 
                        ROWID) OF 'RESULTAT_CALCUL' (TABLE)
      0              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'RSUCAL_CALPLN_FK_I' (INDEX)
      0             NESTED LOOPS
      0              HASH JOIN
      0               TABLE ACCESS   MODE: ANALYZED (BY INDEX 
                          ROWID) OF 'ELEMENT_UTILISE' (TABLE)
      0                INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                           'ELMUTL_RGG_FK_I' (INDEX)
      0               TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                          'ELEMENT_MGC' (TABLE)
      0              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'VALELMMGC_UK1' (INDEX (UNIQUE))
      0            TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 
                       OF 'JEU_DONNEE' (TABLE)
      0             INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                        'JEUDON_PK' (INDEX (UNIQUE))
      0           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 
                      OF 'AFFILIE_PLAN' (TABLE)
      0            INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                       'AFLPLN_PK' (INDEX (UNIQUE))
      0          COLLECTION ITERATOR (PICKLER FETCH)
      0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                    'AFFILIE' (TABLE)
      0          INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'AFL_PK' 
                     (INDEX (UNIQUE))
      0        NESTED LOOPS
      0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                    'LOT_JEU_DONNEE' (TABLE)
      0          INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                     'LOTJEU_PK' (INDEX (UNIQUE))
      0         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'JEUDON_UK' 
                    (INDEX (UNIQUE))
      0       FILTER
      0        NESTED LOOPS
      0         HASH JOIN
      0          NESTED LOOPS
      0           NESTED LOOPS
      0            HASH JOIN
      0             TABLE ACCESS   MODE: ANALYZED (BY INDEX 
                        ROWID) OF 'RESULTAT_CALCUL' (TABLE)
      0              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'RSUCAL_CALPLN_FK_I' (INDEX)
      0             NESTED LOOPS
      0              NESTED LOOPS
      0               NESTED LOOPS
      0                TABLE ACCESS   MODE: ANALYZED (BY 
                           INDEX ROWID) OF 'ELEMENT_UTILISE' (TABLE)
      0                 INDEX   MODE: ANALYZED (RANGE SCAN) 
                            OF 'ELMUTL_RGG_FK_I' (INDEX)
      0                TABLE ACCESS   MODE: ANALYZED (BY 
                           INDEX ROWID) OF 'ELEMENT_MGC' (TABLE)
      0                 INDEX   MODE: ANALYZED (UNIQUE SCAN) 
                            OF 'ELMMGC_PK' (INDEX (UNIQUE))
      0               TABLE ACCESS   MODE: ANALYZED (BY INDEX 
                          ROWID) OF 'ELEMENT_MGC' (TABLE)
      0                INDEX   MODE: ANALYZED (UNIQUE SCAN) 
                           OF 'ELMMGC_PK' (INDEX (UNIQUE))
      0              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'VALELMMGC_UK1' (INDEX (UNIQUE))
      0            TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 
                       OF 'JEU_DONNEE' (TABLE)
      0             INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                        'JEUDON_PK' (INDEX (UNIQUE))
      0           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 
                      OF 'AFFILIE_PLAN' (TABLE)
      0            INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                       'AFLPLN_PK' (INDEX (UNIQUE))
      0          COLLECTION ITERATOR (PICKLER FETCH)
      0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                    'AFFILIE' (TABLE)
      0          INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'AFL_PK' 
                     (INDEX (UNIQUE))
      0        NESTED LOOPS
      0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                    'LOT_JEU_DONNEE' (TABLE)
      0          INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                     'LOTJEU_PK' (INDEX (UNIQUE))
      0         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'JEUDON_UK' 
                    (INDEX (UNIQUE))