Erreur sur requête avec gros volumes de données

[justinedr71]

Bonjour,

Je travaille sous Talend version 4.1.2 avec une base de données Oracle 11g.

J'ai lancé mon application hier à 15h30 et en revenant ce matin à 9h30 j'ai trouvé un message d'erreur sur mon écran. Après avoir chargé les paramètres du contexte, il s'affiche :

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
Exception IN component tOracleInput_1
java.sql.SQLRecoverableException: Exception d'E/S: Connection reset
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:521)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1013)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:314)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:228)
at projet_cleva.contrats_final_0_1.Contrats_Final.tOracleInput_1Process(Contrats_Final.java:2969)
at projet_cleva.contrats_final_0_1.Contrats_Final.tOracleConnection_1Process(Contrats_Final.java:615)
at projet_cleva.contrats_final_0_1.Contrats_Final.tPostjob_1Process(Contrats_Final.java:498)
at projet_cleva.contrats_final_0_1.Contrats_Final.runJobInTOS(Contrats_Final.java:5809)
at projet_cleva.contrats_final_0_1.Contrats_Final.main(Contrats_Final.java:5625)
Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java:168)
at oracle.net.ns.Packet.receive(Packet.java:240)
at oracle.net.ns.DataPacket.receive(DataPacket.java:92)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:172)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:117)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:92)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:77)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1034)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1010)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:588)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1006)
... 7 more

vous trouverez les détails du job dans l'image ci-dessous.

error.png

Cette requête est censée récupérer plus d'un million de lignes... Comment je peux éviter ce problème?

Merci.

[Benoit_Durand]

Visiblement Oracle a coupé la connexion.
C'est le serveur Oracle qui rame autant ?
1 million de lignes ce n'est pas la mer à boire non plus.

On pourrait avoir accès à la requête ?.

Pour les fortes volumétries j'ai tendance à déplacer la charge de travail au maximum sur le serveur SQL qui est fait pour ça (comme le tUniq).

cdt,

[justinedr71]

Bonjour,

Merci de votre réponse.
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
"select pol_numpol, sor_ident, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_libelle,
gad_prime_nette, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl,
in_type
from f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire,
f_tarif_ass, t_intercalaire, f_tarif_gar, t_cdecpt
where gad_ptrsorid = sor_ident
and sor_ptrpolid = pol_ident
and sor_ident = mvt_ptrsuivantid
and mvt_ptrverid = ver_ident
and pol_ptrpasid = pas_ident
and pas_ident = pri_ptrpasident
and sor_ident = sar_ptrsorid (+)
and sar_ptrassid = bpass_ident (+)
and bpass_ptrbppident = bpp_ident (+)
and bpp_ptrmarid = mar_ident (+)
and pol_ptrinid = in_ident (+)
and sor_datetarif = tas_dateffet
and tas_codeintercalaire = ta_code
and ta_cle = tga_cletarif
and tga_code_compta = ccp_code
and gad_code = tga_code
and tga_genre = 'GAR'
and gad_prime_nette is not null and gad_prime_nette != 0
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval('03-00')))
order by pol_numpol asc, ver_compteur asc, sor_ident asc, gad_libelle asc"

les jointures sont donc déjà toutes dans la requête, je n'ai qu'un seul input dans mon job...

P.S j'ai oublié de le préciser, mais lors de l'éxecution de mon job Talend, le débit du nombre de lignes par secondes ne fait que baisser tout au long de mon exécution. Cette nuit je n'ai pas pu observer la valeur minimale atteinte, mais je pense que le débit baisse en continue progressivement, jusqu'à atteindre 0...

Lorsque vous dites "déplacer la charge de travail sur le serveur SQL", comment puis-je procéder pour faire ça? Si mes bases client sont sur des server Oracle c'est quand même faisable? Sans installer de server SQL sur leur poste?

[Benoit_Durand]

Envoyé par justinedr71

P.S j'ai oublié de le préciser, mais lors de l'éxecution de mon job Talend, le débit du nombre de lignes par secondes ne fait que baisser tout au long de mon exécution. Cette nuit je n'ai pas pu observer la valeur minimale atteinte, mais je pense que le débit baisse en continue progressivement, jusqu'à atteindre 0...

ok donc je pense que c'est Talend qui a du mal a traiter la volumétrie en mémoire.

Concernant le sql, il faudrait vérifier que le plan d'exécution est optimisé et notamment que les index sont bien utilisés.

Pour le tUniqRow il doit être remplaçable dans le sql par un distinct ou group by ou row_number. Ou au mieux bien vérifier que le stockage sur disque est activé.
A voir s'il n'y a pas d'autres amélioration à faire avec les autres composants.

[justinedr71]

En reliant mon tInput à un tJavaRow qui ne fait rien, après un petit moment de 'starting' il part à 2300 row/s et le débit augmente (jusqu'à 5300)! J'ai donc un problème d'étouffement à la suite de ma requête du aux multiples étapes de mon traitement...

Avec cette configuration, ma requête s'exécute en 9 minutes. On est loin de toute la nuit comme précédemment, sans aboutissement d'ailleurs!

[justinedr71]

Concernant le sql, il faudrait vérifier que le plan d'exécution est optimisé et notamment que les index sont bien utilisés.

Lorsque vous dites que le plan d'exécution doit être optimisé, vous parlez de la mise en forme de ma requête? Pour l'utilisation des index, cela se règle dans Oracle non?

Pour le tUniqRow il doit être remplaçable dans le sql par un distinct ou group by ou row_number.

En ne laissant que le 'Remove Copy', le débit diminue déjà jusqu'à 400 row/s (avec progression jusqu'à 600, mais je ne l'ai pas laissé s’exécuter jusqu'au bout afin d'avoir le débit maximum atteint). Ce composant est inutile puisqu'il est appliqué à tous les champs, sont objectifs étant de supprimer tous les doublons présents dans ma table.

Un simple distinct au début de ma requête me donne l'erreur suivante :

Exception in component tOracleInput_1
java.sql.SQLSyntaxErrorException: ORA-01791: cette expression n'a pas été SELECTionnée

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4Call.receive(T4Call.java:1034)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
at projet_cleva.testjob_0_1.testJob.tOracleInput_1Process(testJob.java:2960)
at projet_cleva.testjob_0_1.testJob.tOracleConnection_1Process(testJob.java:615)
at projet_cleva.testjob_0_1.testJob.tPostjob_1Process(testJob.java:498)
at projet_cleva.testjob_0_1.testJob.runJobInTOS(testJob.java:5809)
at projet_cleva.testjob_0_1.testJob.main(testJob.java:5625)

Je ne vois pas comment le supprimer en modifiant ma requête autrement qu'avec un distinct...

Ou au mieux bien vérifier que le stockage sur disque est activé.

Où puis-je l'activer?

[justinedr71]

J'ai suivi votre conseil, et utilisé un Group By. Ma requête est donc ainsi :

Code SQL :

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
 
"select pol_numpol, sor_ident, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_libelle,
gad_prime_nette, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl,
in_type
from f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire,
f_tarif_ass, t_intercalaire, f_tarif_gar, t_cdecpt
where gad_ptrsorid = sor_ident
and sor_ptrpolid = pol_ident
and sor_ident = mvt_ptrsuivantid
and mvt_ptrverid = ver_ident
and pol_ptrpasid = pas_ident
and pas_ident = pri_ptrpasident
and sor_ident = sar_ptrsorid (+)
and sar_ptrassid = bpass_ident (+)
and bpass_ptrbppident = bpp_ident (+)
and bpp_ptrmarid = mar_ident (+)
and pol_ptrinid = in_ident (+)
and sor_datetarif = tas_dateffet
and tas_codeintercalaire = ta_code
and ta_cle = tga_cletarif
and tga_code_compta = ccp_code
and gad_code = tga_code
and tga_genre = 'GAR'
and gad_prime_nette is not null and gad_prime_nette != 0
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval('03-00')))
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, gad_libelle, gad_prime_nette, ccp_libelle, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type 
order by pol_numpol asc, ver_compteur asc, sor_ident asc, gad_libelle asc"

Et j'ai pu enlever le tUniqRow (Remove Copy)

[Benoit_Durand]

et il y a du mieux ?

Si la requête ne dure que 9min on peut oublier l'optimisation par index le problème ne doit pas venir de là.

[justinedr71]

En ne laissant que certains composants, j'obtiens les temps d’exécution et débit maximum suivant :

tInput => tMap : 9 min, 3547.19 row/s
tInput => tMap => tFilter : 8 min, 4096.99 row/s
tInput => tMap => tFilter => tPivotToColumns : stoppé à 12 minutes, < 90 row/s

[justinedr71]

J'ai re-modélisé mon application, j'ai ainsi pu trouver le composant qui pose problème... C'est le tPivot!
test.jpg
Seulement, je ne peux pas me passer de ce composant...
Une solution pour éviter ce goulot d'étranglement?

[DevNico]

Bonjour,

Le facteur limitant sur l'exécution du tPivot doit être le cpu.
Si la machine sur laquelle tu fais tourner le traitement a plusieurs cpu, peut-être pourrais-tu découper ton fichier temporaire en plusieurs fichier, et traiter chaque fichier en parallèle ?

Sinon une autre piste serait de revoir si l'utilisation du tPivot est vraiment nécessaire, et si tu ne peux pas utiliser d'autres composants qui utilisent moins de ressources.
(genre un tMap avec plusieurs flux de sortie, ou un tNormalize suivi d'un tMap (mais je ne suis pas certain que ce soit moins couteux)).

Nicolas

[justinedr71]

Je ne peux pas découper le fichier temporaire et effectuer les traitements séparément pour deux raisons :

- le script sera effectué sur des machines clients dont je ne connais pas les caractéristiques...
- le transfert des lignes en colonne dépend des lignes précédemment switchées... Exemple :

Row en entrée de mon tPivot :

"Numéro police" "Date souscription" "Type Garantie" "Prime garantie"
"1" "23/11/2010" "Tout risques" "500"
"1" "23/11/2010" "Bris de glace" "100"
"2" "22/08/2009" "Dommages" "300"
"2" "22/08/2009" "Bris de glace" "200"
"3" "10/03/2009" "Dommages" "100"
"4" "05/04/2009" "Catastrophes" "50"
"4" "05/04/2009" "Tout risques" "20"
"4" "05/04/2009" "Dommages" "80"

Row en sortie de mon tPivot :

"Numéro police" "Date souscription" "Tout risques" "Bris de glace" "Dommages" "Catastrophes"
"1" "23/11/2010" "500" "100"
"2" "22/08/2009" "" "200" "300"
"3" "10/03/2009" "" "" "100"
"4" "05/04/2009" "20" "" "80" "50"

Ainsi je ne peux pas dissocier les traitements de chaque ligne, imaginons que je split mes row en entrée en deux fichiers :

Fichier 1 :
"Numéro police" "Date souscription" "Type Garantie" "Prime garantie"
"1" "23/11/2010" "Tout risques" "500"
"1" "23/11/2010" "Bris de glace" "100"
"2" "22/08/2009" "Dommages" "300"
"2" "22/08/2009" "Bris de glace" "200"

Fichier 2 :
"Numéro police" "Date souscription" "Type Garantie" "Prime garantie"
"3" "10/03/2009" "Dommages" "100"
"4" "05/04/2009" "Catastrophes" "50"
"4" "05/04/2009" "Tout risques" "20"
"4" "05/04/2009" "Dommages" "80"

J'obtiendrai en sortie :

Première sortie :
"Numéro police" "Date souscription" "Tout risques" "Bris de glace" "Dommages"
"1" "23/11/2010" "500" "100"
"2" "22/08/2009" "" "200" "300"

Deuxième sortie :
"Numéro police" "Date souscription" "Tout risques" "Dommages" "Catastrophes"
"3" "10/03/2009" "" "100"
"4" "05/04/2009" "20" "80" "50"

Je n'ai donc pas les même intitulés de colonnes...

[justinedr71]

Je ne pense pas que mon tPivot puisse être remplacé par un autre composant. Maintenant que j'ai expliqué le traitement qu'il me permettait de faire, si vous pensez que c'est possible dites-le moi

Merci!

[DevNico]

Ok, je vois...
Est-ce que les différents types de garanties que tu peux avoir sont fixés ou est-ce qu'ils peuvent évoluer ?

[justinedr71]

Ils ne sont pas fixes du tout, la est tout le problème, je ne peux pas pré-nommer les entêtes des champs de mon fichier... Le nombre et le nom des garanties est complètement variable...

[DevNico]

Ok, je pense que le tPivotToColumnsDelimited est un peu surdimensionné pour faire ce que tu souhaites (tu n'as pas besoin de sa fonctionnalité d'agrégation par exemple), du coup il utilise plus de ressources que nécessaire.

D'un autre coté, je ne vois pas comment faire ce que tu souhaites sans utiliser ce tPivot...
Peut-être que quelqu'un d'autre aurait une idée ?

[justinedr71]

Oui, j'ai essayé aussi de trouver une autre solution que ce tPivot, mais c'est la seule que j'ai obtenue pour l'instant... Donc je me vois mal m'en passer maintenant! ^^.

Oui quelqu'un, help please

[Benoit_Durand]

Vous utilisez bien du sql server ?
J'ai trouvé ça sur internet :

http://fadace.developpez.com/mssql/pivot/
http://msdn.microsoft.com/fr-fr/library/ms177410.aspx

A fouiller.
SQl server sera peut être le plus puissant que Talend sur ce coup là.
IL faudra peut être charger les données dans une table temporaire m=pour faire les manipulations.

[justinedr71]

Bonjour Benoit.

Non, toutes mes tables client sont sur une base Oracle 11g...

[DevNico]

Et bien cela existe aussi sous oracle
http://www.orafaq.com/wiki/PIVOT