Discussion :

[bockstael]

Bonjour,

je travaille sous oracle 8 et je dois créer une procédure qui insère le résultat d'une requète effectuée sur plusieurs DB dans une table située dans une autre DB.

Mon soucis est que cela prend un temps énorme.
Ce que je ne comprend pas, c'est que lorsque je ne fait que le select en SQL+, cela ne dure que entre 1.94 sec et 4.56 sec.
Dès qu'il y a le insert into, je passe à 2707.75 sec (en gros: 3/4 d'heure).

voici la requète pour un DB

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
 INSERT INTO TMP_SEND_ANTHALYS ( 
 
	SELECT distinct fmr.FAY_SEQ_NO,
			FMR.ref_no,
			fmr.NAME,
			fmr.FIRST_NAME,
			fmr.birth_dat birth_dat,
			fmr.SEX,
			pat.phone_no,
			fmr.gsm_no,
			fmr.handic_perct pourcent_handicap,
			pat.SZE_STT_SEQ_NO stt_seq_no,
			pat.HOUSE_NO,
			pat.BOX,
			stt.mun_name commune,
			stt.postal_code,
			stt.coy_name pays,
			o.seq_no mut_seq_no,
			o.name mutuelle,
			fmr.dor_seq_no,
			decode (substr('D602.WORLD',3,2)
								, '02','Nivelles'
								, '04','Bruxelles'
								, '05','Mons'
								, '06','Lobbes'
								, '08','Mouscron'
								, '09','Liège'
								, '10','Luxembourg'
								, '12','Eupen'
								, '13','Verviers'
								, '15','Namur'
								, '16','Tournai') region,
			'cjb' service,
			max(pat.mod_dat),
			max(pat.cre_dat),
			max(fmr.cre_dat),
			max(fmr.mod_dat)
	FROM patients@D602.WORLD pat,
			fam_membs@D602.WORLD fmr,
			org_membs@D602.WORLD omr,
			orgs@D602.WORLD o,
			streets_v@D602.WORLD stt
	WHERE pat.fmr_fay_seq_no=fmr.fay_seq_no
			and pat.fmr_ref_no=fmr.ref_no
			and omr.fmr_fay_seq_no=fmr.fay_seq_no
			and omr.fmr_ref_no=fmr.ref_no
			and omr.ogn_seq_no = o.seq_no
			and pat.sze_stt_seq_no =stt.stt_seq_no
			and stt.lang_code = 0
			and nvl(omr.end_dat,sysdate+1)> sysdate
			and ( to_char(pat.mod_dat,'dd-mm-yyyy') = to_char(sysdate,'dd-mm-yyyy') 
					or 
				  to_char(fmr.mod_dat,'dd-mm-yyyy') = to_char(sysdate,'dd-mm-yyyy')
					or
				  to_char(pat.cre_dat,'dd-mm-yyyy') = to_char(sysdate,'dd-mm-yyyy') 
					or 
				  to_char(fmr.cre_dat,'dd-mm-yyyy') = to_char(sysdate,'dd-mm-yyyy')
				) GROUP BY fmr.FAY_SEQ_NO,
				FMR.ref_no,
				fmr.NAME,
				fmr.FIRST_NAME,
				fmr.birth_dat,
				fmr.SEX,
				pat.phone_no,
				fmr.gsm_no,
				fmr.handic_perct,
				pat.SZE_STT_SEQ_NO,
				pat.HOUSE_NO,
				pat.BOX,
				stt.mun_name,
				stt.postal_code,
				stt.coy_name,
				o.seq_no,
				o.name,
				fmr.dor_seq_no,
				decode (substr('D602.WORLD',3,2)
								, '02','Nivelles'
								, '04','Bruxelles'
								, '05','Mons'
								, '06','Lobbes'
								, '08','Mouscron'
								, '09','Liège'
								, '10','Luxembourg'
								, '12','Eupen'
								, '13','Verviers'
								, '15','Namur'
								, '16','Tournai'),
				'CJB'
 
                                    )

Toute suggestion est la bien venue puisque cette procédure doit tourner quotidiennement

[orafrance]

Faudrait au moins le plan d'exécution

Ca peut aussi aider de créer une vue dans ta base distante (qui contiendra ta requête) et faire un SELECT * de cette vue.

[mnitu]

Vous parlez d’une procédure mais vous nous montrez qu’un select. Donc est-ce que l’instruction insert into select from prend beaucoup ou toute la procédure ? Si oui fait un test avec la table TMP_SEND… en local.

De toute façon, exécutez votre procédure avec la trace SQL étendue activée et vous allez pouvoir apprendre où le temps passe.

[bockstael]

merci de vos réponses.

Faudrait au moins le plan d'exécution

la procédure suivante sera exécutée tous les jours via un job

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
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
PROCEDURE P_SEND_ANTHALYS is
 
	ls_db           varchar2(30);
	ls_selectString_cjb VARCHAR2(4000);
	ls_fromString_cjb	VARCHAR2(4000);
	ls_whereString_cjb 	VARCHAR2(4000);
	ls_group_by_cjb     VARCHAR2(4000);
	ls_selectString_fa VARCHAR2(4000); 
	ls_fromString_fa	VARCHAR2(4000);
	ls_whereString_fa 	VARCHAR2(4000);
	ls_group_by_fa     VARCHAR2(4000);
	ls_dmlString 	VARCHAR2(4000);
	cursor_dbms           INTEGER;
	rows_returned         INTEGER;
 
	ls_directory varchar2(50) := 'C:\';
	ls_record varchar2(15000);
	output_file utl_file.file_type;
	ls_nom varchar2(100) := to_char(sysdate,'yyyymmdd')||'_beneficiaire.xml';
	ls_region varchar2(40);
 
 
	-- prend toutes les DB CJB et FA
	cursor C_db is
	    select  dblink_name from REPL_CENTRE_INFO
	    where  ( substr(dblink_name,2,1) like ('6')
					or
				 substr(dblink_name,2,1) like ('0')
				)
	        and substr(dblink_name,2,3) not like ('014')
	        and substr(dblink_name,2,3) not like ('012')
	        and substr(dblink_name,2,3) = '602'
        order by dblink_name;
 
    cursor C_data is
    select * from tmp_send_anthalys;
 
    cursor C_streets is
    select s.seq_no, s.mun_seq_no, s.name from streets s
    where trunc(cre_dat) = trunc (sysdate);
 
begin 
 
    delete tmp_send_anthalys;
 
    for R_db in C_db loop 
 
        declare   
	        operation_time_out exception;
	        pragma exception_init (operation_time_out,-12535);   
 
		begin	
 
	        ls_db := R_db.dblink_name;
 
			-- requète pour les CJB                                               
	        ls_selectString_cjb :=  'distinct fmr.FAY_SEQ_NO,
								FMR.ref_no,
								fmr.NAME,
								fmr.FIRST_NAME,
								fmr.birth_dat birth_dat,
								fmr.SEX,
								pat.phone_no,
								fmr.gsm_no,
								fmr.handic_perct pourcent_handicap,
								pat.SZE_STT_SEQ_NO stt_seq_no,
								pat.HOUSE_NO,
								pat.BOX,
								stt.mun_name commune,
								stt.postal_code,
								stt.coy_name pays,
								o.seq_no mut_seq_no,
								o.name mutuelle,
								fmr.dor_seq_no,
								decode (substr('''||ls_db||''',2,3), ''002'',''Nivelles''
													, ''602'',''Nivelles''
													, ''004'',''Bruxelles''
													, ''604'',''Bruxelles''
													, ''005'',''Mons''
													, ''605'',''Mons''
													, ''006'',''Lobbes''
													, ''606'',''Lobbes''
													, ''008'',''Mouscron''
													, ''608'',''Mouscron''
													, ''009'',''Liège''
													, ''609'',''Liège''
													, ''010'',''Luxembourg''
													, ''610'',''Luxembourg''
													, ''012'',''Eupen''
													, ''013'',''Verviers''
													, ''613'',''Verviers''
													, ''015'',''Namur''
													, ''615'',''Namur''
													, ''016'',''Tournai''
													, ''616'',''Tournai'') region,
								''cjb'' service,
								max(pat.mod_dat),
								max(pat.cre_dat),
								max(fmr.cre_dat),
								max(fmr.mod_dat)';
 
	        ls_fromString_cjb :=    'patients@'||ls_db||' pat,
								fam_membs@'||ls_db||' fmr,
								org_membs@'||ls_db||' omr,
								orgs@'||ls_db||' o,
								streets_v@'||ls_db||' stt';
 
	        ls_whereString_cjb :=   'pat.fmr_fay_seq_no=fmr.fay_seq_no
						        and pat.fmr_ref_no=fmr.ref_no
						        and omr.fmr_fay_seq_no=fmr.fay_seq_no
						        and omr.fmr_ref_no=fmr.ref_no
						        and omr.ogn_seq_no = o.seq_no
						        and pat.sze_stt_seq_no =stt.stt_seq_no
						        and stt.lang_code = 0
						        and nvl(omr.end_dat,sysdate+1)> sysdate
						        and ( to_char(pat.mod_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'') 
						                or 
						              to_char(fmr.mod_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'')
						                or
						              to_char(pat.cre_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'') 
						                or 
						              to_char(fmr.cre_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'')
						            )';
 
	        ls_group_by_cjb := ' fmr.FAY_SEQ_NO,
						            FMR.ref_no,
						            fmr.NAME,
						            fmr.FIRST_NAME,
						            fmr.birth_dat,
						            fmr.SEX,
						            pat.phone_no,
						            fmr.gsm_no,
						            fmr.handic_perct,
						            pat.SZE_STT_SEQ_NO,
						            pat.HOUSE_NO,
						            pat.BOX,
						            stt.mun_name,
						            stt.postal_code,
						            stt.coy_name,
						            o.seq_no,
						            o.name,
						            fmr.dor_seq_no,
						            decode (substr('''||ls_db||''',2,3), ''002'',''Nivelles''
													, ''602'',''Nivelles''
													, ''004'',''Bruxelles''
													, ''604'',''Bruxelles''
													, ''005'',''Mons''
													, ''605'',''Mons''
													, ''006'',''Lobbes''
													, ''606'',''Lobbes''
													, ''008'',''Mouscron''
													, ''608'',''Mouscron''
													, ''009'',''Liège''
													, ''609'',''Liège''
													, ''010'',''Luxembourg''
													, ''610'',''Luxembourg''
													, ''012'',''Eupen''
													, ''013'',''Verviers''
													, ''613'',''Verviers''
													, ''015'',''Namur''
													, ''615'',''Namur''
													, ''016'',''Tournai''
													, ''616'',''Tournai''),
						            ''CJB''';
 
			-- requète pour les FA
	        ls_selectString_fa :=  'distinct fmr.FAY_SEQ_NO,
								FMR.ref_no,
								fmr.NAME,
								fmr.FIRST_NAME,
								fmr.birth_dat birth_dat,
								fmr.SEX,
								f.phone_no,
								'''' gsm_no,
								fmr.handic_perct pourcent_handicap,
								f.STT_SEQ_NO stt_seq_no,
								f.HOUSE_NO,
								f.BOX,
								stt.mun_name commune,
								stt.postal_code,
								stt.coy_name pays,
								o.seq_no mut_seq_no,
								o.name mutuelle,
								fmr.dor_seq_no,
								decode (substr('''||ls_db||''',2,3), ''002'',''Nivelles''
													, ''602'',''Nivelles''
													, ''004'',''Bruxelles''
													, ''604'',''Bruxelles''
													, ''005'',''Mons''
													, ''605'',''Mons''
													, ''006'',''Lobbes''
													, ''606'',''Lobbes''
													, ''008'',''Mouscron''
													, ''608'',''Mouscron''
													, ''009'',''Liège''
													, ''609'',''Liège''
													, ''010'',''Luxembourg''
													, ''610'',''Luxembourg''
													, ''012'',''Eupen''
													, ''013'',''Verviers''
													, ''613'',''Verviers''
													, ''015'',''Namur''
													, ''615'',''Namur''
													, ''016'',''Tournai''
													, ''616'',''Tournai'') region,
								p.name service,
								max(f.mod_dat),
								max(f.cre_dat),
								max(fmr.cre_dat),
								max(fmr.mod_dat)';
 
	        ls_fromString_fa :=    'fams@'||ls_db||' f,
								fam_membs@'||ls_db||' fmr,
								org_membs@'||ls_db||' omr,
								orgs@'||ls_db||' o,
								streets_v@'||ls_db||' stt,
	                            fam_assists@'||ls_db||' fa,
	                            projects@'||ls_db||' p';
 
	        ls_whereString_fa :=   'f.seq_no=fmr.fay_seq_no
						        and omr.fmr_fay_seq_no=fmr.fay_seq_no
						        and omr.fmr_ref_no=fmr.ref_no
						        and omr.ogn_seq_no = o.seq_no
						        and f.stt_seq_no =stt.stt_seq_no
						        and stt.lang_code = 0
						        and fa.fay_seq_no = fmr.fay_seq_no
						        and fa.pjt_code = p.code
						        and nvl(omr.end_dat,sysdate+1)> sysdate
						        and ( to_char(f.mod_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'') 
						                or 
						              to_char(fmr.mod_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'')
						                or
						              to_char(f.cre_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'') 
						                or 
						              to_char(fmr.cre_dat,''dd-mm-yyyy'') = to_char(sysdate,''dd-mm-yyyy'')
						            )';
 
	        ls_group_by_fa := ' fmr.FAY_SEQ_NO,
						        FMR.ref_no,
						        fmr.NAME,
								fmr.FIRST_NAME,
								fmr.birth_dat,
								fmr.SEX,
								f.phone_no,
								'''',
								fmr.handic_perct,
								f.STT_SEQ_NO,
								f.HOUSE_NO,
								f.BOX,
								stt.mun_name,
								stt.postal_code,
								stt.coy_name,
								o.seq_no,
								o.name,
								fmr.dor_seq_no,
						            decode (substr('''||ls_db||''',2,3), ''002'',''Nivelles''
													, ''602'',''Nivelles''
													, ''004'',''Bruxelles''
													, ''604'',''Bruxelles''
													, ''005'',''Mons''
													, ''605'',''Mons''
													, ''006'',''Lobbes''
													, ''606'',''Lobbes''
													, ''008'',''Mouscron''
													, ''608'',''Mouscron''
													, ''009'',''Liège''
													, ''609'',''Liège''
													, ''010'',''Luxembourg''
													, ''610'',''Luxembourg''
													, ''012'',''Eupen''
													, ''013'',''Verviers''
													, ''613'',''Verviers''
													, ''015'',''Namur''
													, ''615'',''Namur''
													, ''016'',''Tournai''
													, ''616'',''Tournai''),
						            p.name';
 
 
	        if substr(ls_db,2,1) = '6' then
 
				ls_dmlString := ' INSERT INTO TMP_SEND_ANTHALYS ('||
			                        ' SELECT '||ls_selectString_cjb||
			                        ' FROM '||ls_fromString_cjb||
			                        ' WHERE '||ls_whereString_cjb||
			                        ' GROUP BY'||ls_group_by_cjb||
			                        ')';
 
			elsif substr(ls_db,2,1) = '0' then
 
		        ls_dmlString := ' INSERT INTO TMP_SEND_ANTHALYS ('||
		                        ' SELECT '||ls_selectString_fa||
		                        ' FROM '||ls_fromString_fa||
		                        ' WHERE '||ls_whereString_fa||
		                        ' GROUP BY'||ls_group_by_fa||
		                        ')';
	        end if;
 
	        cursor_dbms := dbms_sql.open_cursor;
	        dbms_sql.parse(cursor_dbms,ls_dmlString,2);
	        rows_returned := dbms_sql.execute(cursor_dbms);
	        dbms_sql.close_cursor(cursor_dbms);
	        commit;
 
        exception
        when operation_time_out then
            insert into TMP_SEND_ANTHALYS (region) values (ls_db||'  operation_time_out');commit ;
            null;
        when others then
            insert into TMP_SEND_ANTHALYS (region) values (ls_db||'  others exeption');commit ;
            null;
        end; 
 
    end loop;
 
output_file := utl_file.fopen(ls_directory,ls_nom,'a');
 
ls_record := '<?xml version="1.0" encoding="ISO-8859"?>';
utl_file.put_line(output_file,ls_record);
 
ls_record := '<patient_all>';
utl_file.put_line(output_file,ls_record);
 
         for R_data in C_data loop
            ls_record := '<patient>'||
                         '<region>'||R_data.region||'</region>'||
                         '<FAY_SEQ_NO>'||R_data.FAY_SEQ_NO||'</FAY_SEQ_NO>'||
                         '<ref_no>'||R_data.fay_ref_no||'</ref_no>'||
                         '<NOM>'||R_data.NOM||'</NOM>'||
                         '<PRENOM>'||R_data.PRENOM||'</PRENOM>'||
                         '<dat_naissance>'||R_data.dat_naissance||'</dat_naissance>'||
                         '<SEX>'||R_data.SEX||'</SEX>'||
                         '<PHONE_NO>'||R_data.PHONE_NO||'</PHONE_NO>'||
                         '<GSM_NO>'||R_data.GSM_NO||'</GSM_NO>'||
                         '<POURCENT_HANDIC>'||R_data.POURCENT_HANDIC||'</POURCENT_HANDIC>'||
                         '<STT_SEQ_NO>'||R_data.STT_SEQ_NO||'</STT_SEQ_NO>'||
                         '<HOUSE_NO>'||R_data.HOUSE_NO||'</HOUSE_NO>'||
                         '<BOX>'||R_data.BOX||'</BOX>'||
                         '<COMMUNE>'||R_data.COMMUNE||'</COMMUNE>'||
                         '<CP>'||R_data.CP||'</CP>'||
                         '<PAYS>'||R_data.PAYS||'</PAYS>'||
                         '<MUT_SEQ_NO>'||R_data.MUT_SEQ_NO||'</MUT_SEQ_NO>'||
                         '<MUTUELLE>'||R_data.MUTUELLE||'</MUTUELLE>'||
                         '<DOC_SEQ_NO>'||R_data.DOC_SEQ_NO||'</DOC_SEQ_NO>'||
                         '<SERVICE>'||R_data.SERVICE||'</SERVICE>'||
                         '</patient>';
 
            utl_file.put_line(output_file,ls_record);
        end loop;
 
 
ls_record := '</patient_all>';
utl_file.put_line(output_file,ls_record);
 
ls_record := '<streets_all>';
utl_file.put_line(output_file,ls_record);
    for R_streets in C_streets loop
        ls_record :=    '<street_seq_no>'||R_streets.seq_no||'</street_seq_no>'||
                        '<nom_rue>'||R_streets.name||'</nom_rue>'||
                        '<commune_seq_no>'||R_streets.mun_seq_no||'</commune_seq_no>';
        utl_file.put_line(output_file,ls_record);
    end loop;
 
ls_record := '</streets_all>';
utl_file.put_line(output_file,ls_record);
 
commit;
utl_file.fclose(output_file);  
 
end;

Si oui fait un test avec la table TMP_SEND… en local

c'est justement là où le bas blesse:
les temps de requète ont été fait sur la DB qui appel la procédure. Donc le temps de réponse du select (qlq secondes) est bon considérant le DB-link.

En fait, lorsque je reprend le code de mon premier message sur la DB qui contient la table TMP_SEND_ANTHALYS et que je ne sélectionne que le select dans SQL+, ca va vite; si je prend tous le code, cela prend 3/4 d'heure.


En espérant avoir été plus clair pour vous ...

[mnitu]

Ajoutez

Code :

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

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

au début de la procédure et

Code :

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

1
2
 
execute immediate 'alter session set events ''10046 trace name context of''';

à la fin pour activer la trace SQL étenduée. Analysez ensuite la fichier trace pour comprendre où le temps passe.

[mongilotti]

merci de marque ce sujet comme résolu.

[pachot]

Bonjour,

Ce que je ne comprend pas, c'est que lorsque je ne fait que le select en SQL+, cela ne dure que entre 1.94 sec et 4.56 sec.

Juste pour être sûr, les quelques secondes, c'est pour récupérer tous les enregistrements, ou seulement les 15 premiers sous TOAD par exemple ?

Sinon, il faudrait voir le plan d'exécution pour voir où s'exécute le select (sur la base distante ou locale ?) et si c'est là le problème, le hint DRIVING_SITE peut aider.

Cordialement,
Franck.