Problème de performance sur une requête SQL

Version imprimable

Bonjour,

J'essaye de créer une vue Oracle avec une requête un petit peu complexe.

Cette requête est très lente. J'ai essayé de l'optimiser en utilisant des hints sur les index des différentes tables mais je n'y arrive pas. Çà n'a fait qu'augmenter le cost du plan d'exécution.

Si quelqu'un pourrait jeter un coup d’œil, je suis preneur.

Merci de votre aide

Voici ma requête:

Code:

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
255
256
257
258
259
260
261
262
263
264
265
266
 
Select 
 
	   gjl.ledger_id            as SET_OF_BOOKS_ID
     , gcc.segment1             as SOCIETE
     , gcc.segment2             as PCL
     , gcc.segment5             as RESP_BUD
     , gcc.segment7             as NAT_BUD
     , gcc.code_combination_id  as CCID
     , gcc.segment8             as PROJET_CCID
     , (select FFVT2.DESCRIPTION
           from FND_FLEX_VALUE_SETS   FFVS2,
                FND_FLEX_VALUES       FFV2,
                FND_FLEX_VALUES_TL    FFVT2 
          where FFV2.FLEX_VALUE_SET_ID        = FFVS2.FLEX_VALUE_SET_ID
                AND FFVT2.FLEX_VALUE_ID       = FFV2.FLEX_VALUE_ID
                AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET' 
                AND ffv2.flex_value           = gcc.segment8
                AND FFVT2.LANGUAGE            =  'F')  as LIB_PROJET_CCID 
     , gjh.je_header_id         as NUM_PIECE
     , fl.nls_language          as LANGUAGE
     , decode ( gjh.actual_flag, 'A', 'REALISE', 'E', 'ENGAGE', 'BUDGET')
                                as TYPE_COMPTA
     , gjl.period_name          as PERIODE
     , gjst.user_je_source_name as ORIGINE
     , ori_achats.vendor_name
                                as FOURNISSEUR
     , pha.SEGMENT1          as NUM_CDE
     ,  Null 
                                as NUM_FACT
	 ,ori_achats.location_code  as LIVRAISON
     , gjh.doc_sequence_value   as DOCUMENT
     , gjh.name                 as DESCRIPTION
     , nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
                                as DATE_VALEUR
     , ORI_ACHATS.CATEGORIE     as CATEGORIE
     , ORI_ACHATS.NUM_DDI       as NUM_DDI   
     , ORI_ACHATS.attribute1    as PROJET
     , ORI_ACHATS.attribute2    as NAT_BUDGET
     , nvl(ORI_ACHATS.attribute3, gcc.segment6)
                                as CENTRE_COUT
     , ORI_ACHATS.attribute4    as RUBRIQUE_BUDGET
     , to_char(ORI_ACHATS.creation_date,'DDMMYYYY')
                                as DATE_PO
     , ORI_ACHATS.line_num      as LINE_NUM
     , ORI_ACHATS.item_description
                                as ITEM_DESCRIPTION
     , to_char(ORI_ACHATS.approved_date,'DDMMYYYY')
                                as APPROVED_DATE
     , substr(ORI_ACHATS.lib_prj,1,50)
                                as LIB_PROJET
     , substr(ORI_ACHATS.lib_prj,51,5)
                                as CLARITY
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'B', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_BUDGET
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'E', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_ENGAGE
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'A', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_REALISE
From   gl_code_combinations gcc, gl_je_lines gjl, gl_je_headers gjh
   ,   gl_je_sources_tl gjst, fnd_languages fl
   ,   ( select pda1.po_header_id, pda1.po_distribution_id, pv1.vendor_name, hla1.location_code
               ,cat1.segment1 as CATEGORIE ,pha1.attribute2 as NUM_DDI
               ,pla1.attribute1 ,pla1.attribute2 ,pla1.attribute3 ,pla1.attribute4, pha1.creation_date
               ,pla1.line_num, pla1.item_description, pha1.approved_date, 
              ( select FFVT2.DESCRIPTION from FND_FLEX_VALUE_SETS   FFVS2
                                    ,FND_FLEX_VALUES       FFV2
                                    ,FND_FLEX_VALUES_TL    FFVT2 
                                where FFV2.FLEX_VALUE_SET_ID = FFVS2.FLEX_VALUE_SET_ID
                                  AND FFVT2.FLEX_VALUE_ID     = FFV2.FLEX_VALUE_ID
                                  AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET' 
                                  AND ffv2.flex_value = pla1.attribute1
                                  AND FFVT2.LANGUAGE =  'F') LIB_PRJ
         from   po.po_headers_all pha1, po.po_distributions_all pda1, po.po_line_locations_all pll1
 
			  , AP.AP_SUPPLIERS pv1
              , hr.hr_locations_all hla1, po.po_lines_all pla1
              , inv.mtl_categories_b cat1
         where  pha1.po_header_id     = pda1.po_header_id
         and    pll1.line_location_id = pda1.line_location_id
         and    hla1.location_id      = nvl(pda1.deliver_to_location_id, pll1.ship_to_location_id)
         and    pv1.vendor_id         = pha1.vendor_id 
         and    pda1.po_line_id       = pla1.po_line_id
         and    pla1.category_id      = cat1.category_id)                           ORI_ACHATS
 
		 ,xla.xla_ae_headers xah,XLA.XLA_TRANSACTION_ENTITIES XTE, xla.xla_ae_lines xal,
		 xla.XLA_DISTRIBUTION_LINKS XDL,PO.PO_HEADERS_ALL PHA,PO.PO_DISTRIBUTIONS_ALL PDA
Where  1=1
 
and    (     to_char(ori_achats.po_header_id(+))               = PHA.PO_HEADER_ID
         and to_char(ori_achats.po_distribution_id(+))         = PDA.PO_DISTRIBUTION_ID )
AND XTE.ENTITY_ID = XAH.ENTITY_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XAL.GL_SL_LINK_ID  = gjl.GL_SL_LINK_ID
AND PHA.PO_HEADER_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_CODE = 'PURCHASE_ORDER'
AND XTE.APPLICATION_ID = 201
AND PDA.PO_DISTRIBUTION_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'PO_DISTRIBUTIONS_ALL'
and gjl.code_combination_id   = gcc.code_combination_id
AND XAL.code_combination_id=gcc.code_combination_id												   
and    gjh.je_header_id          = gjl.je_header_id
and    gjh.PERIOD_NAME          = gjl.PERIOD_NAME
and    gjst.je_source_name       = gjh.je_source
and    fl.language_code          = gjst.language
and    gjl.status = 'P'
and    gjh.ledger_id=gjl.ledger_id
and    gcc.template_id is null
group by gjl.ledger_id, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment5, gcc.segment7, gcc.code_combination_id
       , gcc.segment8
       , gjh.je_header_id, fl.nls_language, gjh.actual_flag, gjl.period_name, gjst.user_je_source_name
       , ori_achats.vendor_name
       ,  pha.SEGMENT1 
       , NULL
       ,  ori_achats.location_code 
       , gjh.doc_sequence_value, gjh.name, nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
       ,  ORI_ACHATS.CATEGORIE
       ,  ORI_ACHATS.NUM_DDI
       , ORI_ACHATS.attribute1
       , ORI_ACHATS.attribute2
       , nvl(ORI_ACHATS.attribute3, gcc.segment6)
       , ORI_ACHATS.attribute4
       , to_char(ORI_ACHATS.creation_date,'DDMMYYYY')
       , ORI_ACHATS.line_num
       , ORI_ACHATS.item_description
       , to_char(ORI_ACHATS.approved_date,'DDMMYYYY')
       , substr(ORI_ACHATS.lib_prj,1,50)
       , substr(ORI_ACHATS.lib_prj,51,5)
having sum ( nvl(gjl.accounted_dr,0)+ nvl((gjl.accounted_cr*-1),0) ) !=0
UNION
Select 
 
	   gjl.ledger_id            as SET_OF_BOOKS_ID
     , gcc.segment1             as SOCIETE
     , gcc.segment2             as PCL
     , gcc.segment5             as RESP_BUD
     , gcc.segment7             as NAT_BUD
     , gcc.code_combination_id  as CCID
     , gcc.segment8             as PROJET_CCID
     , (select FFVT2.DESCRIPTION
           from FND_FLEX_VALUE_SETS   FFVS2,
                FND_FLEX_VALUES       FFV2,
                FND_FLEX_VALUES_TL    FFVT2 
          where FFV2.FLEX_VALUE_SET_ID        = FFVS2.FLEX_VALUE_SET_ID
                AND FFVT2.FLEX_VALUE_ID       = FFV2.FLEX_VALUE_ID
                AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET' 
                AND ffv2.flex_value           = gcc.segment8
                AND FFVT2.LANGUAGE            =  'F')  as LIB_PROJET_CCID 
     , gjh.je_header_id         as NUM_PIECE
     , fl.nls_language          as LANGUAGE
     , decode ( gjh.actual_flag, 'A', 'REALISE', 'E', 'ENGAGE', 'BUDGET')
                                as TYPE_COMPTA
     , gjl.period_name          as PERIODE
     , gjst.user_je_source_name as ORIGINE
     , nvl(ori_factures.vendor_name, aps.vendor_name)
                                as FOURNISSEUR
     , ori_factures.segment1
                                as NUM_CDE
     , AIA.INVOICE_NUM
                                as NUM_FACT
     , ori_factures.location_code
                                as LIVRAISON
     , gjh.doc_sequence_value   as DOCUMENT
     , gjh.name                 as DESCRIPTION
     , nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
                                as DATE_VALEUR
     , ORI_FACTURES.CATEGORIE   as CATEGORIE
     , ORI_FACTURES.NUM_DDI     as NUM_DDI   
     , ORI_FACTURES.attribute1  as PROJET
     , ORI_FACTURES.attribute2  as NAT_BUDGET
     , ORI_FACTURES.attribute3
                                as CENTRE_COUT
     , ORI_FACTURES.attribute4  as RUBRIQUE_BUDGET
     , to_char(ORI_FACTURES.creation_date,'DDMMYYYY')
                                as DATE_PO
     , ORI_FACTURES.line_num    as LINE_NUM
     , ORI_FACTURES.item_description
								as ITEM_DESCRIPTION
     , to_char(ORI_FACTURES.approved_date,'DDMMYYYY')
                                as APPROVED_DATE
     , substr(ORI_FACTURES.lib_prj,1,50)
                                as LIB_PROJET
     , substr(ORI_FACTURES.lib_prj,51,5)
                                as CLARITY
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'B', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_BUDGET
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'E', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_ENGAGE
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'A', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_REALISE
From   gl_code_combinations gcc, gl_je_lines gjl, gl_je_headers gjh
   ,   gl_je_sources_tl gjst, fnd_languages fl
   ,   ( select aida2.INVOICE_DISTRIBUTION_ID, aida2.invoice_id, aida2.distribution_line_number, pha2.segment1, hla2.location_code
               ,cat2.segment1 as CATEGORIE ,pha2.attribute2 as NUM_DDI
               ,pla2.attribute1 ,pla2.attribute2 ,pla2.attribute3 ,pla2.attribute4, pha2.creation_date
               ,pla2.line_num, pla2.item_description, pha2.approved_date, pv2.vendor_name,
               ( select FFVT2.DESCRIPTION from FND_FLEX_VALUE_SETS   FFVS2
                                    ,FND_FLEX_VALUES       FFV2
                                    ,FND_FLEX_VALUES_TL    FFVT2 
                                where FFV2.FLEX_VALUE_SET_ID = FFVS2.FLEX_VALUE_SET_ID
                                  AND FFVT2.FLEX_VALUE_ID     = FFV2.FLEX_VALUE_ID
                                  AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET' 
                                  AND ffv2.flex_value = pla2.attribute1
                                  AND FFVT2.LANGUAGE = 'F') LIB_PRJ
         from   ap_invoice_distributions_all aida2
              , po.po_headers_all pha2, po.po_distributions_all pda2, po.po_line_locations_all pll2
              , hr.hr_locations_all hla2, po.po_lines_all pla2
            --, inv.mtl_categories_b cat2, po.po_vendors pv2-- HABA v1.0.00 le 10/10/2018 => Remplacement de la table po_vendors par AP_SUPPLIERS
			, inv.mtl_categories_b cat2, AP.AP_SUPPLIERS pv2
         where  pda2.po_distribution_id = aida2.po_distribution_id
		 AND    pda2.org_id=aida2.org_id								
         and    pha2.po_header_id       = pda2.po_header_id
         and    pll2.line_location_id   = pda2.line_location_id
         and    hla2.location_id        = nvl(pda2.deliver_to_location_id, pll2.ship_to_location_id) 
         and    pda2.po_line_id         = pla2.po_line_id
         and    pla2.category_id        = cat2.category_id
         and    pha2.vendor_id          = pv2.vendor_id  )                       ORI_FACTURES
 
		 ,xla.xla_ae_headers xah,XLA.XLA_TRANSACTION_ENTITIES XTE, xla.xla_ae_lines xal,
		 xla.XLA_DISTRIBUTION_LINKS XDL,AP.AP_INVOICES_ALL  AIA,AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA, AP.AP_SUPPLIERS APS
Where  1=1
--AHAM Migration R12
and    (     to_char(ori_factures.invoice_id(+))               = AIA.INVOICE_ID
         and to_char(ori_factures.distribution_line_number(+)) = AIDA.distribution_line_number )
AND XTE.ENTITY_ID = XAH.ENTITY_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID  = gjl.GL_SL_LINK_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XAL.SOURCE_TABLE = 'AP_INVOICE_DISTRIBUTIONS'
AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XTE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND AIDA.INVOICE_DISTRIBUTION_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND AIA.vendor_id=APS.vendor_id
and gjl.code_combination_id   = gcc.code_combination_id
AND XAL.code_combination_id=gcc.code_combination_id												   
and    gjh.ledger_id=gjl.ledger_id
and    gjh.PERIOD_NAME          = gjl.PERIOD_NAME
and    gjh.je_header_id          = gjl.je_header_id
and    gjst.je_source_name       = gjh.je_source
and    fl.language_code          = gjst.language
and    gjl.status = 'P'
and    gcc.template_id is null
 
group by gjl.ledger_id, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment5, gcc.segment7, gcc.code_combination_id
       , gcc.segment8
       , gjh.je_header_id, fl.nls_language, gjh.actual_flag, gjl.period_name, gjst.user_je_source_name
       , nvl(ori_factures.vendor_name, aps.vendor_name)
       , ori_factures.segment1
       , AIA.INVOICE_NUM
       , ori_factures.location_code
       , gjh.doc_sequence_value, gjh.name, nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
       , ORI_FACTURES.CATEGORIE
       , ORI_FACTURES.NUM_DDI
       , ORI_FACTURES.attribute1
       , ORI_FACTURES.attribute2
       , ORI_FACTURES.attribute3
       , ORI_FACTURES.attribute4
       , to_char(ORI_FACTURES.creation_date,'DDMMYYYY')
       , ORI_FACTURES.line_num
       , ORI_FACTURES.item_description
       , to_char(ORI_FACTURES.approved_date,'DDMMYYYY')
       ,  substr(ORI_FACTURES.lib_prj,1,50)
       ,  substr(ORI_FACTURES.lib_prj,51,5)
having sum ( nvl(gjl.accounted_dr,0)+ nvl((gjl.accounted_cr*-1),0) ) !=0;

Et voici l'explain plan généré :

Code:

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
 
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                 | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                          |                              |    11 |  7320 |       |  8355K  (2)|       |       |
|   1 |  SORT UNIQUE                                              |                              |    11 |  7320 |       |  8355K  (2)|       |       |
|   2 |   UNION-ALL                                               |                              |       |       |       |            |       |       |
|   3 |    NESTED LOOPS                                           |                              |     1 |    95 |       |     7   (0)|       |       |
|   4 |     NESTED LOOPS                                          |                              |     1 |    95 |       |     7   (0)|       |       |
|   5 |      NESTED LOOPS                                         |                              |     1 |    60 |       |     5   (0)|       |       |
|   6 |       TABLE ACCESS BY INDEX ROWID                         | FND_FLEX_VALUE_SETS          |     1 |    33 |       |     2   (0)|       |       |
|   7 |        INDEX UNIQUE SCAN                                  | FND_FLEX_VALUE_SETS_U2       |     1 |       |       |     1   (0)|       |       |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED                 | FND_FLEX_VALUES              |     1 |    27 |       |     3   (0)|       |       |
|   9 |        INDEX RANGE SCAN                                   | FND_FLEX_VALUES_N1           |     1 |       |       |     2   (0)|       |       |
|  10 |      INDEX UNIQUE SCAN                                    | FND_FLEX_VALUES_TL_U1        |     1 |       |       |     1   (0)|       |       |
|  11 |     TABLE ACCESS BY INDEX ROWID                           | FND_FLEX_VALUES_TL           |     2 |    70 |       |     2   (0)|       |       |
|  12 |    NESTED LOOPS                                           |                              |     1 |    95 |       |     7   (0)|       |       |
|  13 |     NESTED LOOPS                                          |                              |     1 |    95 |       |     7   (0)|       |       |
|  14 |      NESTED LOOPS                                         |                              |     1 |    60 |       |     5   (0)|       |       |
|  15 |       TABLE ACCESS BY INDEX ROWID                         | FND_FLEX_VALUE_SETS          |     1 |    33 |       |     2   (0)|       |       |
|  16 |        INDEX UNIQUE SCAN                                  | FND_FLEX_VALUE_SETS_U2       |     1 |       |       |     1   (0)|       |       |
|  17 |       TABLE ACCESS BY INDEX ROWID BATCHED                 | FND_FLEX_VALUES              |     1 |    27 |       |     3   (0)|       |       |
|  18 |        INDEX RANGE SCAN                                   | FND_FLEX_VALUES_N1           |     1 |       |       |     2   (0)|       |       |
|  19 |      INDEX UNIQUE SCAN                                    | FND_FLEX_VALUES_TL_U1        |     1 |       |       |     1   (0)|       |       |
|  20 |     TABLE ACCESS BY INDEX ROWID                           | FND_FLEX_VALUES_TL           |     2 |    70 |       |     2   (0)|       |       |
|  21 |    FILTER                                                 |                              |       |       |       |            |       |       |
|  22 |     HASH GROUP BY                                         |                              |     1 |   620 |       |   112K  (1)|       |       |
|  23 |      HASH JOIN OUTER                                      |                              |     1 |   620 |       |   112K  (1)|       |       |
|  24 |       NESTED LOOPS                                        |                              |     1 |   342 |       |   100K  (1)|       |       |
|  25 |        NESTED LOOPS                                       |                              |     1 |   337 |       |   100K  (1)|       |       |
|  26 |         NESTED LOOPS                                      |                              |     1 |   314 |       |   100K  (1)|       |       |
|  27 |          NESTED LOOPS                                     |                              |     1 |   294 |       |   100K  (1)|       |       |
|  28 |           NESTED LOOPS                                    |                              |     1 |   261 |       |   100K  (1)|       |       |
|  29 |            NESTED LOOPS                                   |                              |     1 |   250 |       |   100K  (1)|       |       |
|  30 |             NESTED LOOPS                                  |                              |     1 |   167 |       |   100K  (1)|       |       |
|  31 |              NESTED LOOPS                                 |                              |   113 | 13899 |       |  2079   (1)|       |       |
|  32 |               NESTED LOOPS                                |                              |   113 |  7571 |       |  1853   (1)|       |       |
|  33 |                NESTED LOOPS                               |                              |    20 |   840 |       |   943   (0)|       |       |
|  34 |                 PARTITION LIST SINGLE                     |                              |    20 |   600 |       |     3   (0)|   KEY |   KEY |
|  35 |                  TABLE ACCESS FULL                        | XLA_TRANSACTION_ENTITIES     |    20 |   600 |       |     3   (0)|     4 |     4 |
|  36 |                 PARTITION LIST ALL                        |                              |     1 |    12 |       |    47   (0)|     1 |    23 |
|  37 |                  TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XLA_AE_HEADERS               |     1 |    12 |       |    47   (0)|     1 |    23 |
|  38 |                   INDEX RANGE SCAN                        | XLA_AE_HEADERS_N3            |     1 |       |       |    46   (0)|     1 |    23 |
|  39 |                PARTITION LIST ALL                         |                              |     6 |   150 |       |    46   (0)|     1 |    22 |
|  40 |                 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | XLA_AE_LINES                 |     6 |   150 |       |    46   (0)|     1 |    22 |
|  41 |                  INDEX RANGE SCAN                         | XLA_AE_LINES_U1              |     6 |       |       |    44   (0)|     1 |    22 |
|  42 |               TABLE ACCESS BY GLOBAL INDEX ROWID          | GL_CODE_COMBINATIONS         |     1 |    56 |       |     2   (0)| ROWID | ROWID |
|  43 |                INDEX UNIQUE SCAN                          | GL_CODE_COMBINATIONS_U1      |     1 |       |       |     1   (0)|       |       |
|  44 |              PARTITION RANGE ALL                          |                              |     1 |    44 |       |   886   (0)|     1 |    48 |
|  45 |               PARTITION LIST ALL                          |                              |     1 |    44 |       |   886   (0)|     1 |  LAST |
|  46 |                TABLE ACCESS BY LOCAL INDEX ROWID BATCHED  | GL_JE_LINES                  |     1 |    44 |       |   886   (0)|     1 |   456 |
|  47 |                 INDEX RANGE SCAN                          | GL_JE_LINES_N1               |    43 |       |       |   864   (0)|     1 |   456 |
|  48 |             TABLE ACCESS BY INDEX ROWID                   | GL_JE_HEADERS                |     1 |    83 |       |     2   (0)|       |       |
|  49 |              INDEX UNIQUE SCAN                            | GL_JE_HEADERS_U1             |     1 |       |       |     1   (0)|       |       |
|  50 |            TABLE ACCESS BY INDEX ROWID                    | PO_HEADERS_ALL               |     1 |    11 |       |     1   (0)|       |       |
|  51 |             INDEX UNIQUE SCAN                             | PO_HEADERS_U1                |     1 |       |       |     0   (0)|       |       |
|  52 |           TABLE ACCESS BY INDEX ROWID BATCHED             | GL_JE_SOURCES_TL             |     5 |   165 |       |     5   (0)|       |       |
|  53 |            INDEX RANGE SCAN                               | GL_JE_SOURCES_TL_U1          |     5 |       |       |     1   (0)|       |       |
|  54 |          TABLE ACCESS BY INDEX ROWID                      | FND_LANGUAGES                |     1 |    20 |       |     1   (0)|       |       |
|  55 |           INDEX UNIQUE SCAN                               | FND_LANGUAGES_U1             |     1 |       |       |     0   (0)|       |       |
|  56 |         PARTITION LIST ALL                                |                              |     4 |    92 |       |    50   (0)|     1 |    19 |
|  57 |          TABLE ACCESS BY LOCAL INDEX ROWID BATCHED        | XLA_DISTRIBUTION_LINKS       |     4 |    92 |       |    50   (0)|     1 |    19 |
|  58 |           INDEX RANGE SCAN                                | XLA_DISTRIBUTION_LINKS_N1    |    20 |       |       |    38   (0)|     1 |    19 |
|  59 |        INDEX UNIQUE SCAN                                  | PO_DISTRIBUTIONS_U1          |     1 |     5 |       |     0   (0)|       |       |
|  60 |       VIEW                                                |                              |   173K|    46M|       | 12048   (1)|       |       |
|  61 |        HASH JOIN                                          |                              |   173K|    32M|       | 10556   (1)|       |       |
|  62 |         TABLE ACCESS FULL                                 | HR_LOCATIONS_ALL             |   376 | 10904 |       |     8   (0)|       |       |
|  63 |         HASH JOIN                                         |                              |   173K|    27M|       | 10547   (1)|       |       |
|  64 |          TABLE ACCESS FULL                                | MTL_CATEGORIES_B             |   148 |  1924 |       |     4   (0)|       |       |
|  65 |          HASH JOIN                                        |                              |   173K|    25M|       | 10542   (1)|       |       |
|  66 |           TABLE ACCESS FULL                               | AP_SUPPLIERS                 | 26390 |   721K|       |   336   (1)|       |       |
|  67 |           HASH JOIN                                       |                              |   173K|    20M|    11M| 10204   (1)|       |       |
|  68 |            TABLE ACCESS FULL                              | PO_LINES_ALL                 |   164K|    10M|       |  1890   (1)|       |       |
|  69 |            HASH JOIN                                      |                              |   172K|    10M|  4080K|  7123   (1)|       |       |
|  70 |             TABLE ACCESS FULL                             | PO_HEADERS_ALL               |   101K|  2882K|       |  1472   (2)|       |       |
|  71 |             HASH JOIN                                     |                              |   171K|  5368K|  3696K|  5093   (1)|       |       |
|  72 |              TABLE ACCESS FULL                            | PO_LINE_LOCATIONS_ALL        |   171K|  1677K|       |  3060   (1)|       |       |
|  73 |              TABLE ACCESS FULL                            | PO_DISTRIBUTIONS_ALL         |   173K|  3724K|       |  1571   (1)|       |       |
|  74 |    NESTED LOOPS                                           |                              |     1 |    95 |       |     7   (0)|       |       |
|  75 |     NESTED LOOPS                                          |                              |     1 |    95 |       |     7   (0)|       |       |
|  76 |      NESTED LOOPS                                         |                              |     1 |    60 |       |     5   (0)|       |       |
|  77 |       TABLE ACCESS BY INDEX ROWID                         | FND_FLEX_VALUE_SETS          |     1 |    33 |       |     2   (0)|       |       |
|  78 |        INDEX UNIQUE SCAN                                  | FND_FLEX_VALUE_SETS_U2       |     1 |       |       |     1   (0)|       |       |
|  79 |       TABLE ACCESS BY INDEX ROWID BATCHED                 | FND_FLEX_VALUES              |     1 |    27 |       |     3   (0)|       |       |
|  80 |        INDEX RANGE SCAN                                   | FND_FLEX_VALUES_N1           |     1 |       |       |     2   (0)|       |       |
|  81 |      INDEX UNIQUE SCAN                                    | FND_FLEX_VALUES_TL_U1        |     1 |       |       |     1   (0)|       |       |
|  82 |     TABLE ACCESS BY INDEX ROWID                           | FND_FLEX_VALUES_TL           |     2 |    70 |       |     2   (0)|       |       |
|  83 |    NESTED LOOPS                                           |                              |     1 |    95 |       |     7   (0)|       |       |
|  84 |     NESTED LOOPS                                          |                              |     1 |    95 |       |     7   (0)|       |       |
|  85 |      NESTED LOOPS                                         |                              |     1 |    60 |       |     5   (0)|       |       |
|  86 |       TABLE ACCESS BY INDEX ROWID                         | FND_FLEX_VALUE_SETS          |     1 |    33 |       |     2   (0)|       |       |
|  87 |        INDEX UNIQUE SCAN                                  | FND_FLEX_VALUE_SETS_U2       |     1 |       |       |     1   (0)|       |       |
|  88 |       TABLE ACCESS BY INDEX ROWID BATCHED                 | FND_FLEX_VALUES              |     1 |    27 |       |     3   (0)|       |       |
|  89 |        INDEX RANGE SCAN                                   | FND_FLEX_VALUES_N1           |     1 |       |       |     2   (0)|       |       |
|  90 |      INDEX UNIQUE SCAN                                    | FND_FLEX_VALUES_TL_U1        |     1 |       |       |     1   (0)|       |       |
|  91 |     TABLE ACCESS BY INDEX ROWID                           | FND_FLEX_VALUES_TL           |     2 |    70 |       |     2   (0)|       |       |
|  92 |    FILTER                                                 |                              |       |       |       |            |       |       |
|  93 |     HASH GROUP BY                                         |                              |    10 |  6700 |       |  8242K  (2)|       |       |
|  94 |      HASH JOIN OUTER                                      |                              |   192 |   125K|       |  8242K  (2)|       |       |
|  95 |       NESTED LOOPS                                        |                              |   192 | 74112 |       |  8186K  (2)|       |       |
|  96 |        NESTED LOOPS                                       |                              |   284 | 74112 |       |  8186K  (2)|       |       |
|  97 |         HASH JOIN                                         |                              |   284 |   104K|       |  8185K  (2)|       |       |
|  98 |          NESTED LOOPS                                     |                              |     1 |   366 |       |  8111K  (2)|       |       |
|  99 |           NESTED LOOPS                                    |                              |     1 |   366 |       |  8111K  (2)|       |       |
| 100 |            NESTED LOOPS                                   |                              |     1 |   346 |       |  8111K  (2)|       |       |
| 101 |             NESTED LOOPS                                  |                              |     1 |   313 |       |  8111K  (2)|       |       |
| 102 |              HASH JOIN                                    |                              |  4471 |  1143K|    53M|  8102K  (2)|       |       |
| 103 |               HASH JOIN                                   |                              |   294K|    50M|   168M|  7912K  (2)|       |       |
| 104 |                HASH JOIN                                  |                              |  1199K|   154M|    47M| 72047   (1)|       |       |
| 105 |                 HASH JOIN                                 |                              |   496K|    42M|    19M| 24974   (1)|       |       |
| 106 |                  PARTITION LIST ALL                       |                              |   839K|  9832K|       |  8486   (1)|     1 |    23 |
| 107 |                   TABLE ACCESS FULL                       | XLA_AE_HEADERS               |   839K|  9832K|       |  8486   (1)|     1 |    23 |
| 108 |                  HASH JOIN                                |                              |   491K|    36M|       | 13452   (1)|       |       |
| 109 |                   TABLE ACCESS FULL                       | AP_SUPPLIERS                 | 26390 |   721K|       |   336   (1)|       |       |
| 110 |                   HASH JOIN                               |                              |   491K|    22M|    15M| 13112   (1)|       |       |
| 111 |                    TABLE ACCESS FULL                      | AP_INVOICES_ALL              |   489K|    10M|       |  8340   (1)|       |       |
| 112 |                    PARTITION LIST SINGLE                  |                              |   491K|    12M|       |  3065   (1)|   KEY |   KEY |
| 113 |                     TABLE ACCESS FULL                     | XLA_TRANSACTION_ENTITIES     |   491K|    12M|       |  3065   (1)|     1 |     1 |
| 114 |                 PARTITION LIST ALL                        |                              |  1987K|    87M|       | 39217   (2)|     1 |    22 |
| 115 |                  TABLE ACCESS FULL                        | XLA_AE_LINES                 |  1987K|    87M|       | 39217   (2)|     1 |    22 |
| 116 |                PARTITION RANGE ALL                        |                              |   793M|    32G|       |  5719K  (2)|     1 |    48 |
| 117 |                 PARTITION LIST ALL                        |                              |   793M|    32G|       |  5719K  (2)|     1 |  LAST |
| 118 |                  TABLE ACCESS FULL                        | GL_JE_LINES                  |   793M|    32G|       |  5719K  (2)|     1 |   456 |
| 119 |               TABLE ACCESS FULL                           | GL_JE_HEADERS                |    12M|   953M|       |   132K  (2)|       |       |
| 120 |              TABLE ACCESS BY GLOBAL INDEX ROWID           | GL_CODE_COMBINATIONS         |     1 |    51 |       |     2   (0)| ROWID | ROWID |
| 121 |               INDEX UNIQUE SCAN                           | GL_CODE_COMBINATIONS_U1      |     1 |       |       |     1   (0)|       |       |
| 122 |             TABLE ACCESS BY INDEX ROWID BATCHED           | GL_JE_SOURCES_TL             |     5 |   165 |       |     5   (0)|       |       |
| 123 |              INDEX RANGE SCAN                             | GL_JE_SOURCES_TL_U1          |     5 |       |       |     1   (0)|       |       |
| 124 |            INDEX UNIQUE SCAN                              | FND_LANGUAGES_U1             |     1 |       |       |     0   (0)|       |       |
| 125 |           TABLE ACCESS BY INDEX ROWID                     | FND_LANGUAGES                |     1 |    20 |       |     1   (0)|       |       |
| 126 |          PARTITION LIST ALL                               |                              |  9964K|    95M|       | 74290   (1)|     1 |    19 |
| 127 |           TABLE ACCESS FULL                               | XLA_DISTRIBUTION_LINKS       |  9964K|    95M|       | 74290   (1)|     1 |    19 |
| 128 |         INDEX UNIQUE SCAN                                 | AP_INVOICE_DISTRIBUTIONS_U2  |     1 |       |       |     1   (0)|       |       |
| 129 |        TABLE ACCESS BY INDEX ROWID                        | AP_INVOICE_DISTRIBUTIONS_ALL |     1 |    10 |       |     2   (0)|       |       |
| 130 |       VIEW                                                |                              |   223K|    60M|       | 56109   (1)|       |       |
| 131 |        HASH JOIN                                          |                              |   223K|    46M|       | 54617   (1)|       |       |
| 132 |         TABLE ACCESS FULL                                 | HR_LOCATIONS_ALL             |   376 | 10904 |       |     8   (0)|       |       |
| 133 |         HASH JOIN                                         |                              |   223K|    40M|       | 54607   (1)|       |       |
| 134 |          TABLE ACCESS FULL                                | MTL_CATEGORIES_B             |   148 |  1924 |       |     4   (0)|       |       |
| 135 |          HASH JOIN                                        |                              |   223K|    37M|       | 54602   (1)|       |       |
| 136 |           TABLE ACCESS FULL                               | AP_SUPPLIERS                 | 26390 |   721K|       |   336   (1)|       |       |
| 137 |           HASH JOIN                                       |                              |   223K|    31M|    11M| 54264   (1)|       |       |
| 138 |            TABLE ACCESS FULL                              | PO_LINES_ALL                 |   164K|    10M|       |  1890   (1)|       |       |
| 139 |            HASH JOIN                                      |                              |   221K|    18M|  4672K| 50752   (1)|       |       |
| 140 |             TABLE ACCESS FULL                             | PO_HEADERS_ALL               |   101K|  3478K|       |  1472   (2)|       |       |
| 141 |             HASH JOIN                                     |                              |   220K|    10M|  7888K| 48391   (1)|       |       |
| 142 |              HASH JOIN                                    |                              |   171K|  5871K|  3696K|  5133   (1)|       |       |
| 143 |               TABLE ACCESS FULL                           | PO_LINE_LOCATIONS_ALL        |   171K|  1677K|       |  3060   (1)|       |       |
| 144 |               TABLE ACCESS FULL                           | PO_DISTRIBUTIONS_ALL         |   173K|  4231K|       |  1586   (2)|       |       |
| 145 |              TABLE ACCESS FULL                            | AP_INVOICE_DISTRIBUTIONS_ALL |   468K|  7323K|       | 42246   (1)|       |       |
-------------------------------------------------------------------------------------------------------------------------------------------------------

02/06/2019, 20h48
Michel.Priori

Bonsoir,

Petites précisions :
1- on est bien en 12c ?
2- Est que la requête est le résultat d'un SQL "dynamique" ? (ce qui me fait dire ça c'est 1=1)
3- Est-ce que sum ( nvl(gjl.accounted_dr,0)+ nvl((gjl.accounted_cr*-1),0) ) peut être négatif ?
4- En quoi le plan d’exécution est jugé mauvais ?
06/06/2019, 18h04
13thFloor

Hello,
gl_je_lines accédée en full, ça doit piquer un peu.
Il faudrait plutôt avoir le plan après exécution.
Peux-tu exécuter la requête avec /*+ gather_plan_statistics */
et nous donner le résultat de

Code:

SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

Il y aura sans peut être quelques informations utiles (statistics feedback, sql profile, adaptive plan..) à décortiquer.
Et aussi vérifier les filtres : les cardinalités sont -elels exactes ?
07/06/2019, 00h08
tbc92

Je n'ai jamais aimé les sructures du type select a, b , (select...) from ... , autrement dit, ce que tu as en lignes 11 à 149, puis idem lignes 141 à 149.
As-tu essayé d'exécuter la requête sans cette partie.
Bien sûr, il faudra réintégrer cette information d'une autre façon si tu retiens cette piste.

Bonjour HamzaNour,

Pour mieux comprendre la requête je me suis fendu de la réécriture de la 1ere partie c'est à dire jusqu'à UNION.
Ben c'est pas de la tarte !

Je me suis très certainement lourdé quelque part, merci de vérifier qu'elle fonctionne à l'identique de même partie de la requête initiale.
Voici :

Code:

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
WITH
LIB_PRJ AS
    (SELECT FFVT2.DESCRIPTION       AS description
            , ffv2.flex_value       AS flex_value
        FROM FND_FLEX_VALUE_SETS  FFVS2
            JOIN FND_FLEX_VALUES  FFV2  ON FFV2.FLEX_VALUE_SET_ID = FFVS2.FLEX_VALUE_SET_ID
            ,FND_FLEX_VALUES_TL   FFVT2 ON FFVT2.FLEX_VALUE_ID     = FFV2.FLEX_VALUE_ID
        WHERE FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET'
            AND FFVT2.LANGUAGE =  'F'
    )
,ORI_ACHATS AS
    (SELECT to_char(pda1.po_header_id)          AS po_header_id
            ,to_char(pda1.po_distribution_id)   AS po_distribution_id
            ,pv1.vendor_name
            ,hla1.location_code
            ,cat1.segment1                      AS CATEGORIE
            ,pha1.attribute2                    AS NUM_DDI
            ,pla1.attribute1
            ,pla1.attribute2
            ,pla1.attribute3
            ,pla1.attribute4
            ,pha1.creation_date
            ,pla1.line_num
            ,pla1.item_description
            ,pha1.approved_date
            ,LIB_PRJ.description
        FROM   po.po_headers_all            pha1
            JOIN po.po_distributions_all    pda1  ON pda1.po_header_id     = pha1.po_header_id
            JOIN po.po_line_locations_all   pll1  ON pll1.line_location_id = pda1.line_location_id
            JOIN AP.AP_SUPPLIERS            pv1   ON pv1.vendor_id         = pha1.vendor_id
            JOIN hr.hr_locations_all        hla1  ON hla1.location_id      = nvl(pda1.deliver_to_location_id, pll1.ship_to_location_id)
            JOIN po.po_lines_all            pla1  ON pla1.po_line_id       = pda1.po_line_id
            JOIN inv.mtl_categories_b       cat1  ON cat1.category_id      = pla1.category_id
            JOIN LIB_PRJ                          ON LIB_PRJ.flex_value = pla1.attribute1
    )
,GJL AS
    (SELECT gl_sl_link_id
            ,ledger_id
            ,period_name
            ,attribute14
            ,CASE actual_flag when 'A' then 'REALISE'
                              when 'E' then 'ENGAGE'
                              else 'BUDGET'
             end AS TYPE_COMPTA
            ,sum(accounted_dr)      AS accounted_dr
            ,sum(accounted_cr)      AS accounted_cr
        FROM gl_je_lines
        WHERE status = 'P'
        GROUP BY gl_sl_link_id
            ,ledger_id
            ,period_name
            ,attribute14
        HAVING sum(accounted_dr) <> sum(accounted_cr)
    )
/* requete */
Select GJL.ledger_id            as SET_OF_BOOKS_ID
     , gcc.segment1             as SOCIETE
     , gcc.segment2             as PCL
     , gcc.segment5             as RESP_BUD
     , gcc.segment7             as NAT_BUD
     , gcc.code_combination_id  as CCID
     , gcc.segment8             as PROJET_CCID
     , (select description
        from LIB_PRJ
        where LIB_PRJ.flex_value = gcc.segment8
        )
     , gjh.je_header_id         as NUM_PIECE
     , fl.nls_language          as LANGUAGE
     , GJL.TYPE_COMPTA
     , GJL.period_name          as PERIODE
     , gjst.user_je_source_name as ORIGINE
     , ORI_ACHATS.vendor_name   as FOURNISSEUR
     , pha.SEGMENT1             as NUM_CDE
     , Null                     as NUM_FACT
     , ORI_ACHATS.location_code as LIVRAISON
     , gjh.doc_sequence_value   as DOCUMENT
     , gjh.name                 as DESCRIPTION
     , nvl(GJL.attribute14
            , to_char(gjh.default_effective_date, 'DDMMYYYY')
           )                    as DATE_VALEUR
     , ORI_ACHATS.CATEGORIE     as CATEGORIE
     , ORI_ACHATS.NUM_DDI       as NUM_DDI
     , ORI_ACHATS.attribute1    as PROJET
     , ORI_ACHATS.attribute2    as NAT_BUDGET
     , nvl(ORI_ACHATS.attribute3
            , gcc.segment6
           )                    as CENTRE_COUT
     , ORI_ACHATS.attribute4    as RUBRIQUE_BUDGET
     , to_char(ORI_ACHATS.creation_date,'DDMMYYYY')
                                as DATE_PO
     , ORI_ACHATS.line_num      as LINE_NUM
     , ORI_ACHATS.item_description
                                as ITEM_DESCRIPTION
     , to_char(ORI_ACHATS.approved_date,'DDMMYYYY')
                                as APPROVED_DATE
     , substr(ORI_ACHATS.lib_prj,1,50)
                                as LIB_PROJET
     , substr(ORI_ACHATS.lib_prj,51,5)
                                as CLARITY
  /*   , nvl ( sum ( to_number ( decode ( gjh.actual_flag ,'B', nvl(GJL.accounted_dr,0) + nvl((GJL.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_BUDGET
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'E', nvl(GJL.accounted_dr,0) + nvl((GJL.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_ENGAGE
     , nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'A', nvl(GJL.accounted_dr,0) + nvl((GJL.accounted_cr*-1),0) ) ) ), 0)
                                as MONTANT_REALISE */
From  fnd_languages                         fl
    JOIN gl_je_sources_tl                   gjst    ON gjst.language            = fl.language_code
    JOIN gl_je_headers                      gjh     ON gjh.je_source            = gjst.je_source_name
            JOIN GJL                                ON GJL.je_header_id         = gjh.je_header_id
                                                        AND GJL.PERIOD_NAME     = gjh.PERIOD_NAME
                                                        AND GJL.ledger_id       = gjh.ledger_id
            JOIN gl_code_combinations       gcc     ON gcc.code_combination_id  = GJL.code_combination_id
    JOIN xla.xla_ae_lines                   xal     ON xal.code_combination_id  = gcc.code_combination_id
                                                        AND xal.gl_sl_link_id   = GJL.gl_sl_link_id
            JOIN xla.xla_distribution_links xdl     ON xdl.ae_line_num          = xal.ae_line_num
            JOIN po.po_distributions_all    pda     ON pda.po_distribution_id   = xdl.source_distribution_id_num_1
 
            JOIN xla.xla_ae_headers             xah ON xah.ae_header_id         = xal.ae_header_id
            JOIN xla.xla_transaction_entities   xte ON xte.entity_id            = xah.entity_id
            JOIN po.po_headers_all          pha     ON pha.po_header_id         = xte.source_id_int_1
    LEFT OUTER JOIN ORI_ACHATS                      ON ORI_ACHATS.po_header_id  = pha.po_header_id
                                                        AND ORI_ACHATS.po_distribution_id   = pda.po_distribution_id
Where
    xte.entity_code = 'PURCHASE_ORDER'
AND xte.application_id = 201
AND xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
AND gcc.template_id is null

Dans les transformations que j'ai faites la plupart sont d'ordre cosmétique, comme les CTE et les jointures selon la norme de 1992.
Par contre, selon ma compréhension, il vaut mieux faire les group by sur la projection la plus simple.
C'est pourquoi la CTE GJL est ainsi.
Ce qui m'amène à poser la question suivante :
Existe t'il une différence entre actual_flag que l'on trouve dans gl_je_lines et dans gl_je_headers ? Est-ce une donnée redondante ?
En attendant ta réponse j'ai mis les colonnes "MONTANT_%" en commentaire.

Si c'est bon, merci de donner le plan d'éxécution de cette nouvelle écriture.

Avoir 2 "boucles" de jointure dans la même requête m'interpelle quelque peu.
J'ai indenté les jointures qui représentent des boucles.
En quoi la jointure entre ~~gjh~~ gjl et xal a du sens ?

07/06/2019, 16h56
Michel.Priori

1 pièce(s) jointe(s)

Re,

Pour aller plus loin, sans invalider les questions précédentes, j'ai creusé un peu plus et j'ai voulu partager mes questions.

Il parait qu'un bon schéma vaut mieux qu'un long discours.
Du coup j'ai représenté la requête ainsi :
Pièce jointe 481753

non, je ne dirais pas que le modèle de données me semble discutable, non ;)

On remarque que 9 tables et relations sont présentes à l'identique.

On remarque aussi qu'une conditionnelle a "sauté" de xdl à xal.
Pourquoi ?
Se serait plus simple d'indexer si les colonnes sont les mêmes (et les valeurs de conditionnelles différentes bien sûr).

A mon humble avis la jointure entre gjl et xal est inutile vu que c'est la même colonne qui sert aussi de jointure entre gcc et xal.
Vu qu'il existe des conditionnelles pour gjl et gcc le "raccourci" ne peut que ralentir le travail de l'optimizer.

La logique qui voudrait qu'une même ligne de xal soit à la fois présente dans xdl et dans xah MAIS que les informations permettant de retrouver les informations ORI_% ne pointent pas forcément sur la même ligne, me laisse perplexe.
Pour la 2ieme partie du select (factures), est-ce le chemin xal --- xdl --- aida --- ori_factures représente un intérêt par rapport au chemin xal --- xah --- xte --- aia --- ori_facture ?

Et pour finir, le plus évident : UNION alors que les lignes ne peuvent être en doublon => UNION ALL

Bref, encore une fois je constate que le miroir aux alouettes que représente la recherche des HINTS cache surtout un manque de volonté de relire le code.
Une fois que la requête sera épurée, que les index utiles seront créés, alors, on pourra se poser la question de savoir pourquoi gl_je_lines est accédée en full et en quoi c'est contre performant.

Pour être clair, je déteste les HINTS.
Je trouve qu'au prix des licences ce ne doit pas être un travail qui nous incombe (ou alors on admet que les open source sont suffisants).
Le fait de pourrir une requête (même pas une vue qui aurait le mérite d'être localisée en base) bouffe une grande partie de l’intérêt de changer de version, de s’intéresser à la collecte des statistiques et tout le saint foin.
Je déteste les HINTS.
07/06/2019, 17h03
Ikebukuro

Est-ce qu'on peut lancer le SQL Tuning Advisor sur cette requête bien complexe (pour ça il faut avoir payé le Diagnostic Pack et le SQL Tuning Pack) ?

Je confirme, ne pas utiliser de hints : c'est dire à l'optimiseur "Je suis plus intelligent que les ingénieurs d'Oracle" :ptdr::ptdr:

Est-ce que les stats sont bien à jour?
07/06/2019, 23h03
pachot

Citation:

Envoyé par Ikebukuro

Je confirme, ne pas utiliser de hints : c'est dire à l'optimiseur "Je suis plus intelligent que les ingénieurs d'Oracle"

Pas tout à fait. Il s'agit plutôt de dire à l'optimiseur: je connais mieux mes données que les quelques statistiques qui les décrivent.

Le problème des hints, c'est que ce n'est pas une solution durable. Mais pour trouver le meilleur plan, c'est très utile. Ensuite, il faut voir comment rensigner l'optimiseur (statistiques, moyens d'accès) pour qu'il le trouve tout seul.

Citation:

Envoyé par HamzaNour

J'ai essayé de l'optimiser en utilisant des hints sur les index des différentes tables mais je n'y arrive pas. Çà n'a fait qu'augmenter le cost du plan d'exécution.

Là il y a un problème de logique. L'optimiseur choisit le plan qui a le 'cost' le plus faible, donc c'est normal que forcer un autre plan ait un 'cost' plus élevé. Si on se retrouve à douter du plan d'exécution c'est que les estimations de l'optimiseur ne sont pas correctes. Il est inutile alors de regarder le 'cost' puis qu'il est calculé à partir de ces estimations. il faut regarder le nombre réel de blocs lus, comme proposé par 13thFloor
10/06/2019, 09h53
Michel.Priori

Bonjour à tous,

Citation:

Envoyé par pachot

Pas tout à fait. Il s'agit plutôt de dire à l'optimiseur: je connais mieux mes données que les quelques statistiques qui les décrivent.

Oui.
Avouons quand même que cette description idyllique est rare dans les faits.
Du moins, quand c'est le cas, on est bien d'accord que la requête doit être exempte de reproches et qu'on a tenté plusieurs syntaxes alternatives.

Citation:

Envoyé par pachot

Le problème des hints, c'est que ce n'est pas une solution durable. Mais pour trouver le meilleur plan, c'est très utile. Ensuite, il faut voir comment rensigner l'optimiseur (statistiques, moyens d'accès) pour qu'il le trouve tout seul.

3 fois OUI.
Je dirais même que c'est un outil de laboratoire.
Si, après avoir tout tenté, on doit s'y résoudre alors ce sera au travers les "outlines".
Ça fera un truc de plus à revalider à chaque patch/version d'Oracle et à chaque changement de structure des tables-index-statistiques concernés par la requête... ou, au moins une fois de temps en temps.

Citation:

Envoyé par pachot

Là il y a un problème de logique. L'optimiseur choisit le plan qui a le 'cost' le plus faible, donc c'est normal que forcer un autre plan ait un 'cost' plus élevé. Si on se retrouve à douter du plan d'exécution c'est que les estimations de l'optimiseur ne sont pas correctes. Il est inutile alors de regarder le 'cost' puis qu'il est calculé à partir de ces estimations.

Merci de cette remarque.

Les gens d'Oracle passent une ingénierie incroyable pour mettre au point un optimizer qui soit à la fois léger, performant et le moins impactant globalement sur le server.
L'optimisation du code en sacrifiant le "cost" est un jeu d'équilibre entre performance de la requête et impact global.
On peut faire mieux, de temps à autre, que l'implémentation du moteur, et notamment lors des régressions de plan.
Mais faut bien avouer que c'est pas tous les jours ;)
10/06/2019, 11h19
tbc92

Hamzanour a disparu, alors restons sur cette discussion sur les hints.

Je lis régulièrement sur ce forum le même discours sur les hints, et je suis toujours surpris. J'ai appris Oracle sur le tas, aucune certification (ça ne m'intéresse pas).
Vous dites ques les ingénieurs d'Oracle ont développé un super optimizer léger, performant... oui. Mais ils ont aussi développé une super syntaxe autour des hints. S'ils ont développé cette syntaxe et tous ces hints, c'est quand même pour qu'on les utilise, non ?

Par ailleurs, dans l'entreprise où je travaille, il y a une quinzaine d'ingénieurs qui ont travaillé assez longtemps chez Oracle. Du solide. Nous ne travaillons pas sur les mêmes sujets, les mêmes Bases de données, mais ils savent que j'utilise des hints régulièrement sur des requêtes plutôt lourdes, (des tables jusqu'à 10 Milliard de lignes, fréquemment des requêtes croisant 5 ou 10 tables), ils ont été amenés un faire un audit de la BDD sur laquelle je travaille, et je n'ai pas souvenir de la moindre remarque sur les hints. Je me souviens même de conseils disant : sur le parallélisme, il faut systématiquement déclarer les tables en no-parallel et utiliser les hints.

Et clairement, j'ai un certain nombre de requêtes qui sont très longues si je n'utilise pas les hints, et qui sont instantanées dans les faits.
10/06/2019, 11h27
Michel.Priori

1 pièce(s) jointe(s)

Re,

En continuant sur cette problématique, la lecture du plan nous fait apparaitre un opérateur VIEW (id 60 et 130).
Cet opérateur correspond respectivement aux sous requêtes ORI_ACHAT et ORI_FACTURE.
Vu que c'est là que visiblement qu'on brasse inutilement de la donnée, je m'y suis penché.

Problème de l'estimation via les statistiques

Les fonctions appliquées à des colonnes ont, comme corolaire, de rendre inutile l'index sous-jacent, et, éventuellement les statistiques aussi.
Par exemple, si on prend une colonne de type INTEGER l'histogramme de répartition va se faire en suivant l'ordre des valeurs, par exemple : (1,5,10,15,20,25,30,35,40,45,50).
Le fait de transformer les valeurs en chaine bouscule l'ordre : ('1','10','15','20','25','30','35','40','45','5','50')
Et donc, invalide les statistiques (que je n'aime pas ce choix de mot :( )

Du coup il n'est pas étonnant que, dans le doute, l'optimizer suive la logique de la syntaxe

Problème des jointures externes :

Les jointures proposent un rapprochement de matrices avec l'imposition de l'égalité pour les jointure INNER et de dépasser cette imposition par les jointures OUTER.
Les OUTER permettent de "dépasser" le problème des non correspondance.

D'un point de vue de l'optimizer les jointures externes nécessitent une attention particulière.

Or, que penser de l'intérêt de faire une requête qui aurait une double projection de la même table "outer join" sur la même colonne de par et d'autre ? :weird:
Ce schéma est intéressant si la table outer est plus restreinte que la table interne (et par le jeux des jointures fournir des informations complémentaires circonstanciées )
Mais l'inverse -la source interne ayant moins de ligne que la table externe- alors là 8O :weird: 8O

Problème des "boucles de jointure"

A l'usage on remarque que les boucles de jointures sont assez rares.
En général le besoin demande à ce qu'on fasse apparaitre plusieurs fois la même table via des projections différentes.
Par exemple :
Soit les tables "LANGUE", "MESSAGE", "CLIENT" ayant en commun la colonne langue_id, et les tables MESSAGE et CLIENT ayant en commun la colonne id_client.
Afficher la langue des messages par client et celle du client va demander de faire apparaitre 2 fois la table LANGUE via 2 projections nommée différemment
Afficher les messages qui sont dans la langue du client va pourvoir être solutionné par une "boucle" de jointure.
Bref c'est rare.

De là à avoir une boucle avec 2 jointures externes pointant sur la même source 8O
Oui, faut se casser 2 neurones, mais c'est justifiable.
Les 2 sources internes peuvent pointer, les 2 ensembles ou l'une des 2 ou aucune, une ligne de la source OUTER.
Si on avait fait apparaitre plusieurs projections, pour obtenir le même résultat on aurait du jouer avec COALESCE pour gérer les cas.

Problème de sémantique

Sans avoir le schéma des tables en documentation on est réduit à supputer en fonction de ce qu'on a à lire.
Il me semble acceptable de penser que si la table s'appelle po.po_distributions_all, la colonne po_distribution_id en représente sa clé primaire :roll:

Si c'est le cas alors la suite prend une importance capitale pour la ré-écriture de la requête

Problème des vues

L'encapsulation du code est une technique de base en programmation 3G. Tellement de base que lorsqu'on passe en 4G ou oublie de remettre en le cause.
Le fait de faire des vues et de les réutiliser peut être un problème de performance.
On le voit particulièrement bien ici dans le plan d’exécution des sous requêtes ORI_%

Au delà des problématiques d’exécution, ces encapsulations masque le détail de la réalité.

Si vous avez suivi tout ce laïus, alors la conclusion : "Pour atteindre de bonnes performances, il serait bien de réécrire la requête" s'imposera d'elle même par la lecture attentive de ce nouveau schéma.

Pièce jointe 482227
10/06/2019, 12h07
Michel.Priori

Bonjour tbc92,

Citation:

Envoyé par tbc92

Hamzanour a disparu, alors restons sur cette discussion sur les hints.

La justification des hints est historique : dans les années 90, faire sans était stupide.
Depuis les choses ont changé ;)

Il existe une différence entre ordonnancer l'usage des outils et en bannir un.
Le "langage" à tenir sur un forum est complexe dans le sens où les subtilités ne sont que rarement lues.
Si je "déteste" les hints, cela reste que, de temps en temps, c'est une option (comme pour les régressions de plans).

Les hints sont, pour moi, le dernier recours.
Et encore, je ne les utilise plus qu'au travers les outlines.
Pourquoi ?
Parce que je n'ai pas la "chance" de m'occuper d'une seule base, elle même administrée et développée par du personnel compétent.
En général les requêtes proviennent d'applications distantes.
A partir de là plusieurs solutions :
1- on peut agir sur le code appelant
a- faire une vue (ou une Ps chez M$) pour "rapatrier" le SQL sur le serveur
b- faire comprendre les limites du SQL dynamique voire le reprendre
2- on ne peut pas agir sur le code appelant
a- bien faire comprendre la perte financière
b- utiliser les outlines

Ce qui est particulièrement détestable, ce sont les hints codés coté applicatif alors qu'on n'a pas accès au code appelant.

Ce que je remarque souvent c'est que le travail de fond sur le code SQL est douteux.

Au sujet des audits, au nom de quoi un hint serait relevé par un audit ?
Est-ce une faille de sécurité ?
Remettre en cause leur usage passerait par la démonstration que vos plans d'exécution sont, en moyenne, moins bon que les plans standards. Trop de travail. :roll:
Donc cela ne m’étonne pas.

Puis-je retourner la question ?
Selon ton expérience, quels sont les facteurs d'optimisation pourrait-on attendre via les hints par rapport à la requête de Hamzanour ?

Je suis près à miser un caramel ou une mousse sur un concours de ce type par rapport à une requête qui ne soit ni de ta base ni de mes sujets.
Et ce sera avec joie que j'accepterais ma défaite, encore faut-il que tu gagnes, car la leçon vaut largement une mousse ;)
En espérant que Hamzanour se manifeste à nouveau ...
10/06/2019, 14h38
tbc92

Les experts oracle que je côtoie ont largement 50 ans, donc ça peut expliquer leur appétence pour les hints. C'était une pratique tout à fait normale quand ils ont été formés.

L'audit que j'ai subi était un audit de performance, de bonnes pratiques, de 'code-review'. Pour anticiper les problèmes à venir lors de migrations Oracle, ils auraient pu me conseiller de ne pas utiliser les hints.

Sur la requête de Hamzanour, j'ai fait une première réponse, et je ne parlais pas de hint, mais de réorganisation de la requête.
11/06/2019, 15h41
Michel.Priori

Citation:

Envoyé par tbc92

L'audit que j'ai subi était un audit de performance, de bonnes pratiques, de 'code-review'. Pour anticiper les problèmes à venir lors de migrations Oracle, ils auraient pu me conseiller de ne pas utiliser les hints.

Dans ce cas là, oui c'est dommage. :calim2:
Ils auraient au moins pu soulever le warning.

Encore une fois, le double inconvénient des hints :
-ils sont DANS le code
-ils stabilisent le plan d'exécution
Lors de la prochaine migration il n'y aura pas de surprises.
Ni bonnes, ni mauvaises. :?
Et vu le prix de la nouvelle licence, c'est dommage.

Quels sont les éléments qui influent sur un plan d'exécution ?
- les données :
. . entre hier et demain les volumétries ne sont peut être plus les mêmes
- la version de l'optimizer :
. . la collecte des stat,
. . la formule de calcul des coûts de chaque 'étape de traitement'
. . le nombre et le contenu des 'étapes de traitement'

Petit moment de réclame pour les outlines ;)

Le fait d'abuser des outlines est qu'on peut les remettre en cause car ils forment une bibliothèque.
Aller à la recherche des hints individuellement est juste inimaginable.

Lors de la migration on peut rapidement "stabiliser" une requête via les outlines (si on a les 2 moteurs en parallèle bien sûr).

Post migration, on peut reprendre un par un chaque outline et valider rapidement sont degré de pertinence en le mettant OFF ou ON.
Et ainsi, de proche en proche, on va affiner la compréhension de quand ils sont utiles tout en découvrant les nouveaux usages "standards".