Utilisation de global temporary tables

**LEK** · 12/09/2012, 17h44

Bonjour,
j'ai une table de travail générique (sous Oracle 10gr2) qui est du type global temporary tables
et qui possède 100 champs du type "CHAMP1 VARCHAR2(1000),CHAMP2 VARCHAR2(1000),...." . Elle permet de charger un ensemble de données et d'effectuer un certains nombre de traitements avant de faire un enregistrement dans les tables finales....
J'ai constaté avec son utilisation un problème de performance qui a été résolu en réduisant le nombre de champ et leur longueur...
Cela dit je pensais que les types varchar2 ne prenait que la place nécessitée par le stockage des données?
Comment connaître la limite acceptable de champ et leur longueur que je peux utiliser ?

Merci pour votre aide.

**mnitu** · 12/09/2012, 18h03

Le problème n’est pas la table qui a 100 colonnes de type varchar2(1000) mais plutôt ce qu’on fait avec, plus précisément les traitements dans quels elle est impliqué.

La résolution avec réduction des colonnes et leur taille semble plutôt à la blague avec les 4 ingénieurs : mécanicien, chimiste, électronique, informaticien et la voiture qui tombe en panne

« Et si on ouvre les portières et on les refermes par la suite? »

**LEK** · 12/09/2012, 18h27

Salut, merci de ta réponse et pour la blague... Je conaissais pas mais google est mon ami

Moi aussi je n'ai pas été complètement honnête dans mon optimisation j'ai aussi étudié le plan d'exécution de certaines requêtes que j'ai modifiées car elles effectuaient plusieurs full scan de cette table (plusieurs cas de vérificationssur des valeurs de la même table qui étaient traitée au cas par cas en joignant le tout par des unions... et j'ai retraduit l'ensemble en ne parcourant plus qu'une fois la table et en traitant mes vérifications champ par champ avec des case when... cela dit dur/dur à cause de la limitation sur le nombre de case when possibles...)
Enfin, si je posais la question c'est qu'effectivement je ne sais pas si tailler les tables de cette manière à néanmoins un impact sur mon travail (le chargement en mémoire des lignes de cette table??).

**skuatamad** · 12/09/2012, 23h20

Envoyé par LEK

Enfin, si je posais la question c'est qu'effectivement je ne sais pas si tailler les tables de cette manière à néanmoins un impact sur mon travail (le chargement en mémoire des lignes de cette table??).

Comme on ne connait pas ton travail, difficile de répondre mais c'est possible :
How are varchar stored internally in a block or page

o Ok, so the code prepares a query that select 10 columns that are varchar2. The
developer, for performance would like to array fetch (very important). They would like
to array fetch say 100 rows (very very typical). So, we have 4000 times 10 times 100 =
alsmost 4meg of RAM the developer must allocate!!

Now consider if that where 10 varchar2(80) fields (its probably much smaller then that)
-- thats about oh 78k. Ask the "data modeler" how much RAM he is willing to kick in for
this system....

Que pensez vous de cette "optimisation" ?

C'est mieux que les UNION qui devraient être des UNION ALL.
Sinon ça ressemble à des contraintes (taille de la colonne, CHECK...) qui peuvent/devraient être implémentées dans la base.

**LEK** · 13/09/2012, 00h03

Merci pour le lien, je n'avais pas vu ce billet sur le site de Tom...
Et cela confirme bien l'impression que j'ai eu dans mes tests ce qui m'a fait effectivement réduire les champs en nombre et taille (en me calant sur les nombres max que j'ai à gérer aujourd'hui)...
Touché : pour le "union all" ....
Cela dit avec mes case when, je risque aussi de reproduire le même problème en ne retenant qu'un cas d'erreur...
Pour les check et autres je suis d'accord, mais il me faudrait créer des tables de travail à la volée, insérer et récupérer en bloc les erreurs... Enfin c'est ce que je crois que tu veux dire ;-)
Et encore je pense que je ne pourrais trouver qu'un cas d'erreur : la première contrainte ou premier check qui pête...
Car dans ce que je décris je suis dans une première étape d'un processus d'"ETL" maison... suite à mes vérifs/check, je fais quelques traitements de transformations et renseigne un certain nombre de tables cibles...

**skuatamad** · 13/09/2012, 01h16

Envoyé par LEK

Pour les check et autres je suis d'accord, mais il me faudrait créer des tables de travail à la volée, insérer et récupérer en bloc les erreurs... Enfin c'est ce que je crois que tu veux dire ;-)

Rassure toi je ne proposerai jamais une folie pareille

Je voyais plutôt les contraintes sur la table temporarire ou sur les tables destinations avec par exemple du dml error logging, mais dur à dire si c'est pertinant.

Sinon s'il y a des centaines de case à faire, j'envisagerais aussi une fonction pipelined :

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
SQL> create table g_wrkTable (type_champ varchar2(30), champ2 varchar2(30));
 
Table created.
 
SQL> 
SQL> insert into g_wrkTable (type_champ, champ2) values ('CODEPOSTAL',lpad('x',15,'x'));
 
1 row created.
 
SQL> insert into g_wrkTable (type_champ, champ2) values ('CODEPOSTAL',lpad('x',5,'x'));
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> 
SQL> create or replace function validateData
  2  return sys.odcivarchar2list
  3  pipelined is
  4    l_message varchar2(500);
  5  begin
  6    for c in (select type_champ, champ2 from g_wrkTable) loop
  7      if (c.type_champ = 'CODEPOSTAL' and length(c.champ2) > 12) then
  8        l_message := ' (type_champ = '||c.type_champ||' , champ2 = '||c.champ2||') Le champ : champ2 doit avoir une longueur max de 5 ';
  9        pipe row(l_message);
 10      end if;
 11    end loop;
 12  end;
 13  /
 
Function created.
 
SQL> show err
No errors.
SQL> select * from table(validateData)
  2  /
 
COLUMN_VALUE
------------------------------------------------------------------------------------------------------------------------
 (type_champ = CODEPOSTAL , champ2 = xxxxxxxxxxxxxxx) Le champ : champ2 doit avoir une longueur max de 5
 
SQL>

En plus ça correspond plus au code de base avec UNION car en fait avec les CASE il y a autant de ligne que dans la table (dans l'exemple allégé présenté il faudrait en plus refiltrer sur message is not null)

**pachot** · 12/09/2012, 21h57

Bonjour,

Envoyé par LEK

Cela dit je pensais que les types varchar2 ne prenait que la place nécessitée par le stockage des données?

C'est exact, la taille maximum définie pour les colonnes ne change rien au stockage.

Par contre, elle peut être utilisée dans certains cas par l'optimiseur lorsqu'il estime la taille d'un résultat. Et si l'estimation est loin de la réalité, alors le plan d'exécution choisi peut être mauvais.
C'est la seule explication qui me vient à l'esprit ici. Et pour voir si c'est une explication possible, j'ai créé une global temporary table table avec 101 colonnes VARCHAR2(1000) et voici le plan d'exécution d'un 'select *' sur la table vide sans statistiques:

Code :

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

1
2
3
4
5
6
7
8
9
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 | 50702 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 | 50702 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

Il a fait du dynamic sampling puisqu'il n'y a pas de stats. Il a bien vu qu'il y avait peu de ligne (en fait 0 mais estimation à 1).
Par contre, n'ayant pas de lignes pour voir la taille moyenne d'une ligne, il estime à: 50702 octets:
Il y a 100 colonnes de taille maxi 1000 octets, et il semble qu'il estime en moyenne qu'elles sont à moitié rempli (50702/101=502 octets - c'est exactement la taille d'une chaîne de 500 caractères).

Si j'insère 10 lignes avec juste 1 caractère dans la première colonne:

Code :

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

1
2
3
4
5
6
7
8
9
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   495K|     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |    10 |   495K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

Dynamic sampling a bien vu 10 lignes, mais n'a pas regardé la taille moyenne d'un enregistrement. Il estime toujours des lignes à 50000 octets.

Maintenant je calcule les stats (ma table est en on commit preserve rows pour qu'il y ait toujours les lignes au moment du gather_table_stats):

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |    10 |    20 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Là, plus de dynamic sampling, et surtout une bonne estimation de la taille moyenne d'une ligne: 2 octets par ligne.

Donc je pense que le problème de performance vient d'un mauvais plan d'exécution.
Sur ce type d'utilisation, il serait peut-être judicieux de calculer les stats une fois que la table est remplie.

Cordialement,
Franck.

**LEK** · 12/09/2012, 22h43

Merci pour l'explication claire,nette et pour les exemples Pachot!
Je vais de ce pas rajouter une collecte de statistiques suite au remplissage de ma table (sans compter qu'elle ne possède aucun index vu qu'elle est utilisée pour stocker tout type de données).
Par contre je voulais faire confirmer mon optimisation de requête.
J'ai remplacé une requête (avec code généré) du style :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 select ' Le champ : '|| champ2_name ||' doit avoir une 
  longueur max de 5 ' as message 
 from g_wrkTable
 where  
  type_champ='CODEPOSTAL' and Length (champ2)> 12 
UNION 
select ' Le champ : '|| champ6_name ||' doit figurer dans la liste : 1,2.' as message
g_wrkTable
 where  
  type_champ= 'HABITATION_TYPE' and champ6
  not in (1,2)  
union ...

par une autre approche avec des case when:

Code :

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

1
2
3
4
5
6
7
 
 select 
      case when	  type_champ='CODEPOSTAL' and Length (champ2)> 12  then ' Le champ : '|| champ2_name ||' doit avoir une longueur max de 5 '
	when   type_champ= 'HABITATION_TYPE' and champ6   not in (1,2)  then ' Le champ : '|| champ6_name ||' doit figurer dans la liste : 1,2.' as message
      end
 as message 
 from g_wrkTable

Car d'après mon analyse du plan m'indiquait que les unions faisaient de multiples full scan... Du coup je suis quand même limité par le nombre de case when possibles (127) et je dois refaire des unions ensuite ou générer des case when imbriqués...
Que pensez vous de cette "optimisation" ?
Y-a-til plus simple comme approche ? (Le but étant de générer des messages d'erreurs si de mauvaises données vont être importées dans mon système...)

Merci d'avance pour votre aide,
Lek

**Mohamed.Houri** · 13/09/2012, 15h44

Bonjour Franck,

Envoyé par pachot

Code :

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

1
2
3
4
5
6
7
8
9
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 | 50702 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 | 50702 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

Code :

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

1
2
3
4
5
6
7
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |    10 |    20 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Ton exemple montre bien la bonne estimation de la taille de la ligne faite par le CBO lorsque celui-ci dispose des statistiques adéquate (avg_col_len dans ce cas) ; il n'en reste pas moins que le calcul des statistiques n'a manifestement eu aucun effet sur le plan d'exécution dans ce cas.

En effet, le coût de l'utilisation d'une table ne dépend pas de la taille de ses colonnes mais plutôt du paramètre db_file_multiblock_read_count et du fait de l’utilisation des statistiques systèmes ou non (workload statistics or noworkload statistics).

Par contre, la taille d’une colonne (avg_col_len) a une grande importance pour le CBO pour le choix de la table directrice (build table) lors d’une jointure du type HASH.

Elle a aussi son importance lors d’un tri (ORDER BY) parce qu’Oracle va dériver le montant de la CPU pour le tri de ces colonnes et donc le volume d’I/O qui peut avoir lieu en mémoire et qui, dû à la taille excessive de ces colonnes, peut dépasser la limite.

Donc, tant que LEK n’a pas tout montré on ne peut pas savoir avec exactitude quel a été son problème de performance et quel a été l’impact réel de ce qu’il a fait sur cette table temporaire dans l’amélioration de la performance.

Je trouve que LEK devrait revoir son traitement pour le rendre ‘’restartable’’. C'est-à-dire qu’à n’importe quel moment il peut relancer son traitement en ne traitant à chaque fois que ce qui ne l’a pas été. Pour cela il peut faire en sorte que chacun de ses enregistrements d'origine soit identifié via un flag (flag_processed par exemple) qui sera à 1 lorsqu’il a été correctement traité et 0 par défaut (non traité ou traité avec une erreur) et au lieu que son traitement soit du genre ''tout ou rien'' il peut le rendre plus flexible avec ce flag et en utilisant des rollback to savepoint

**LEK** · 13/09/2012, 22h11

Salut à tous,
Rei Ichido : merci pour la démo je vois mieux comment tu voyais la chose et effectivement cela offre des possibilités intéressantes.
Mohamed.HOURI : l'ensemble du process est automatisé et je ne peux me permettre de faire un processus en n step... D'après ce que je comprend tu propose que je puisse faire des imports partiels... Mais j'ai peur que cela m'amène dans des complications (données dépendantes par exemple pour lequel j'ai une donnée chapeau qui sera importée sans données filles car celles-ci seraient en erreur par exemple... Cela provoquerait des effets de bords dans mon appli web...)
Mnitu :
je suis resté assez vague pour des raisons de droits commerciaux (..). En fait je possède une appli web (erp) qui est accédée par plusieurs utilisateurs. Chacun de ces utilisateurs travaille dans un environnement cloisonné des autres et ne manipule que ses données.
L'application permet la saisie d'un certain nombre d'informations (je sais c'est vague

), dont la volumétrie peut être trop conséquente pour n'être saisies que par IHM. Parfois aussi le client en question possède déjà sous un format type fichier csv,tableur excel ou voire base de données un historique de données qu'il désire reporté dans mon application.
Pour faciliter la vie de ces gens j'ai mis en place un compte ftp par client, qu'ils utilisent pour uploader directement leur données sous leur format actuel.
J'ai ensuite des routines (C++/C#) qui suivant le cas vont transformés ces données pour les portées vers des fichiers plat csv. Ces fichiers sont ensuite chargés via des tables externes dans ma base oracle (10gr2) directement dans ma table temporaire de travail. Un batch permet alors de transformer (si nécessaire, car mes routines en C++/C# permettent parfois de réaliser complètement la transformation ) ces données dans un format de destination que je maitrise. A partir de là mon programme PL effectue des vérifications sur le format en entrée (contraintes et check diverses) et génère suivant le cas un log digeste pour le client. Si aucune erreur n'est détectée, j'importe ou met à jour les données existantes pour mon client dans ma base de données.
Si des erreurs ont été détectées, le fichier de log est déposé en retour sur le site FTP du client avec l'ensemble des raisons de l'échec.... Ensuite c'est à lui de corriger et de relancer l'ensemble du process!
Voilà j'espère que cela t'auras éclairer sur mon besoin réel (pour les fonctions pipelines tu faisais référence à fournir une aide à l'optimiseur ?)

Sinon pour vous donnez davantage d'informations, j'ai utilisé oradebug pour tracer les infos suivantes lors des requêtes en union pour vérifier les
cas d'erreur potentiels.
Pour rappel il s'agissait d'une requête portant sur une global temporary table
ayant 100 champs du type "CHAMP1 VARCHAR2(1000),CHAMP2 VARCHAR2(1000),...."
de ce genre :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT ' Le champ : '|| champ2_name ||' doit avoir une 
  longueur max de 5 ' AS message 
 FROM g_wrkTable
 WHERE  
  type_champ='CODEPOSTAL' AND Length (champ2)> 12 
UNION 
SELECT ' Le champ : '|| champ6_name ||' doit figurer dans la liste : 1,2.' AS message
g_wrkTable
 WHERE  
  type_champ= 'HABITATION_TYPE' AND champ6
  NOT IN (1,2)  
union ...
order by nom_fichier, line_number

Dans les cas d'import où je devais gérer plusieurs fichiers simultanément, j'ai du splitter mes requêtes en plusieurs car la requête générée pouvait dépassé les 32767 caractères...

Le résultat était le suivant (là je n'ai pas chargé la table car je voulais envoyer le résultat sans avoir à attendre 1 heure) :

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
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.14       0.15          0      41100          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.21       0.22          0      41100          0           0
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40  (GEST_CLI_JOB)   (recursive depth: 2)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=41100 pr=0 pw=0 time=150536 us)
      0   SORT UNIQUE (cr=41100 pr=0 pw=0 time=150525 us)
      0    UNION-ALL  (cr=41100 pr=0 pw=0 time=150505 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=2006 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=827 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=813 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=847 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1223 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=794 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=869 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=826 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=807 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=828 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=966 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1254 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=861 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1344 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=814 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=977 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=808 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=816 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1296 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=973 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=820 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=812 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=791 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=831 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=794 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=971 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1286 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1466 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=838 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=833 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=801 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1207 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3299 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1268 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=6076 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1128 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1182 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3294 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1169 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1162 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=8625 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3834 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=862 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1222 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1199 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1264 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=853 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=5175 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1915 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1363 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=8330 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3326 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=868 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=2160 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1181 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=851 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1170 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3749 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=8681 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=839 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=859 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1555 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=809 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=852 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=813 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=899 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1274 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=844 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1192 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=907 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1827 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=995 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3603 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=844 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1402 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=797 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=836 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=779 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1158 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1920 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=883 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=799 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=910 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=797 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=890 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1343 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=849 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=783 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1322 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1155 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=837 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=863 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=799 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=850 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1257 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=805 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=767 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1183 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=756 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=761 us)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   SORT (ORDER BY)
      0    SORT (UNIQUE)
      0     UNION-ALL
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
 
********************************************************************************

Voilà, j'espère que cela vous permettra de constater les raisons de mon problème... Sur des volumes assez faibles je n'ai pas de problèmes de traitement. Dès la centaine de meg en entrée le programme commence à consommer en CPU et en temps d'exécution (l'import se déroule alors en 30/45 minutes au lieu de 2/3 minutes habituelles)....
En tout cas, merci pour vos conseils.
ps : (je tente en parallèle une méthode en c++ pour voir si peux battre oracle à ce jeux là ;-) même si je n'y crois pas trop)

**mnitu** · 14/09/2012, 10h50

Bon, je comprends qu’en entrée de votre traitement vous avez des fichiers csv. Ces fichiers sont lus via des tables externes et chargés dans une table temporaire. Question : pourquoi ne contentez-vous tout simplement d’exploiter ces fichiers directement via SQL, c’est à dire à ne plus les charger dans la table temporaire ?

Par la suite une fois chargées dans la table temporaire vos données sont transformées « dans un format que de destination que j’ai maîtrise ». Je ne peux rien dire sur cette opération obscure mais il sera sage d’étudier la possibilité de l’éliminer en appliquant les transformations au moment de chargement dans les tables. Revenez avec les détails.

Par la suite vous exécutez un programme PL/SQL qui vérifie pour l’ensemble des données les longueurs des valeurs, le domaine de définition, et qui sait encore quelle autres règles de cohérence. A priori vous avez adopté cette stratégie parce que c’est un traitement de type tout ou rien ! Deuxième contraint le client veut avoir une liste « exhaustive » des erreurs avant de relancer son traitement pour éviter les aller retour.

Jusque là rien de spécial. A moi, ce qui me fait peur dans tout cette histoire c’est le mot générique ! A priori votre table contient dans une zone type champ les « types de champ » générique : code postal, type d’habitation etc. Pour chaque « type de champ » vous avez dans une zone le nom du champ actuel et une autre zone contient la valeur du champ. Je pense que maintenant j’ai compris le but de votre « opération de transformation des données dans un format … »

Premier conseil : jetez à la poubelle tout ça et fait N traitements pour chaque type de fichier que vous recevez. Tous vos problèmes de performances vont disparaître par miracle.
Deuxième conseil, utilisez le « array processing » pour accélérer les traitements : BULK Fetch en PL/SQL Array Fetch en C, C++, java, etc. En anglais ils disent : « row by row it’s slow by slow »
Troisième conseil : n’essayez jamais de battre Oracle par un langage de troisième génération, vous allez perdre toujours.
Quatrième conseil : le cas échéant vous pouvez améliorer encore vos traitements (mais pas les génériques) sous certaines conditions avec un traitement des données en parallèle. Mais n’employez pas des techniques sophistiquées avant d’avoir tiré avantage au maximum des techniques classique.

Allez, bon courage

**Waldar** · 14/09/2012, 15h50

Je me permets d'insister sur le premier point évoqué par mnitu.
Le meilleur moyen d'obtenir de la performance est de réaliser une interface par traitement.

Ça peut paraître de prime abord plus coûteux en terme de développement, mais vous n'aurez jamais de plaintes de vos utilisateurs côté performance ou encore cohérence des données (données typées et contraintes).

En général, les utilisateurs ont rarement des besoins très disparates ni volatile, une fois qu'ils ont adopté un type de fichier .csv à vous soumettre, celui-ci bougera peu.

Utilisation de global temporary tables

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager