Discussion :

[Nico57]

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

[mnitu]

Faite une trace sql étendue qui affiche les WAITS

Code :

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

1
2
 
alter session set events '10046 trace name context forever, level 8'

ou similaire. Comme ça vous aurez la réponse.

[Nico57]

Mnitu, merci pour votre réponse.

Voici ce que ça donne :

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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
********************************************************************************
 
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.10       0.10          0          3          0           0
Fetch        5      0.40       2.34       1213      23469          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.50       2.44       1213      23472          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)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       913        0.05          1.60
  direct path write temp                         26        0.00          0.01
  direct path read temp                          20        0.01          0.03
********************************************************************************
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.16       0.20          0          3          0           0
Fetch        1      1.79       6.58       7159      11017          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.95       6.78       7159      11020          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       NESTED LOOPS
      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 (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     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))
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      7159        0.06          4.98
********************************************************************************

Deux remarques :

Les 2 requêtes sont + rapides aujourd'hui : machine moins chargée? Accès disque + rapides car moins sollicité?

En tous les cas, les WAIT semblent bel et bien venir du disque? Que puis-je y faire?

Merci d'avance pour vos réponses.

[mnitu]

L'événement qui compte c'est le «db file sequential Read*» qui indique que le problème est lié à la lecture d'un (des) index(es). Malheureusement, vous n'avez pas clôturés les curseurs avant d'exécuter tkprof et donc les nombres des lignes manipulées en chaque étape n'est pas disponible.

Il y a plusieurs façons de tirer un chat par sa queue: commencez par regarder dans le fichier trc les lignes de type WAIT pour le «*db file ...*» et essayez de trouver l'index ou les indexes en question. Aidez vous par les paramètres p1, p2 et p3 des lignes de type WAIT pour ça.

Une autre idée est d'examiner le plan d'exécution et de voir si il n'y pas mieux à faire. Pour cella refait le tkprof pour avoir le nombre des lignes manipulées dans chaque étape du plan d'exécution.

En fin, téléchargez tvd$xtat et examinez le fichier trace de votre traitement. Pour optimiser un traitement il n'a y pas de mieux à faire que le profil de temps de réponse du traitement.

[Nico57]

Malheureusement, vous n'avez pas clôturés les curseurs avant d'exécuter tkprof et donc les nombres des lignes manipulées en chaque étape n'est pas disponible.

Justement, je me demandais pourquoi j'avais toujours 0 indiqué dans la colonne "Rows"... Pourtant, je ferme systématiquement les curseurs. Voici un exemple de ce que j'avais fait :

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
  OPEN c_elm27;
 
  LOOP
    FETCH c_elm27
     INTO r_idtelm
        , r_idtaflpln
        , r_nivapl
        , r_valelm
        , r_numord
        , r_nomafl
        , r_prnafl
        , r_mtlafl;
    EXIT WHEN c_elm27%NOTFOUND;
  END LOOP;
 
  CLOSE c_elm27;

[mnitu]

Pourtant, je ferme systématiquement les curseurs. ...

Non, vous demandez au PL/SQL de les fermer mais, le PL/SQL ne les ferme pas! Terminez la session et ensuite faite le TKPROF.