Performance problem

[Anonymous]

Hello,
I made talend job which calls other exported talend jobs with their sh files. For this purpose I use tSystem. So far so good. My problem is when I put Oracle connection (tOracleConnection) in the exported talend jobs the performance goes down very very badly.
In my main Talend job I calls 10 other exported talend jobs by sh files. If I don 't have tOracleConnection in the exported jobs everything is fine - I have immediately execution of all 10 jobs. If I put tOracleConnection then the processing time for every next called jobs is increased progressive - 1th job for 2 sec, 2th job for 5sec, 3th job for 50sec...10th job for 5-10 min. Of course I put tOracleClose in every jobs and I release the connection. I cannot use tRunJob because all jobs will live as standalone applications and they will be exported with sh files. Maybe there is Talend pool of DB connections which size must be increased ?!
Any idea how I can sove this performance problem?
Regards

[Anonymous]

Hey,
If the jobs do massive insert delete or update operations on same db object then you need to take a look at locking object in the database when running your jobs in parallel. The joke is that when oracle locks some object in isolated trasaction, even if the transaction is already commited and job finished, the lock on the object could still appears in the database. You can call it war for objects 🙂 You need to investigate it on db side I think.
Ladislav

[Anonymous]

would it not be possible to use *row* locking as opposed to table locking? (it has a while since i last used oracle)
i have seem similar problems with MSSQL as service tried to access the same table creating a 'war for objects'.
but archenroot is correct as you will have to investigate the DB end

[Anonymous]

Thank you for your answer. The joke is that in the called jobs I removed all tOracleInput and I have only one tOracleConnection (to open connection) and tOracleClose (to close it) and the performance is same - very very very very slow. So the problem is not on the INSERT/DELETE/UPDATE operation . Any other ideas ?

[Anonymous]

huh, that's strange, how many connection do initiate in parallel? What edition of Oracle do you use?
Anyway you schould check and probably increase following parameters in DB, because the defaults could fit your performance requirements:

sqlplus "/as sysdba"
or
sqlplus '/as sysdba'
 
 
sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions

To see how much resources are used you schould run following statements after talend job started:

select count(*) from v$process;
select count(*) from v$session;
select count(*) from v$transaction;

You can forget transaction system table as soon as you are not manipulating data. You better use some tool for doing this, sqldeveloper or toad can help you with this.
It is recomended to increase these parameters in Oracle db using exactly following or similar formula based on your environment:

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

You schould take a look into documentation also...
On the other hand you also could face this issue because of just some network problem, resolving ip address from host name or error in route table. I would try to run nslookup on the oracle host, try to traceroute the host and check the routing table in general. I have already faced similar situations and it was mostly clear network problem. This is just hint.

[Anonymous]

everything on Oracle side is checked...Everything is OK there - I started from this point with same SQL as yours...Do you know is there limitation in the DB pool connection size in TALEND ? Every jobs and the oracle are on same machine and DB access is by localhost

[Anonymous]

As soon as you run each job in private process you needn't to take care about this, it's always one instance of JDBC driver which connect only once.
I have an idea how this issue could be maybe workarounded, your jobs still can stay separated on filesystem as they are executed also separated and you can then create some main job where you will connect only once and share the connection to child jobs using tRunJob.

[Anonymous]

I would try it just on localhost database also and see the results if you can.

[Anonymous]

archenroot I really thank you about this discussion, you're the only one which responds me in the forum about my problem.
In fact I tried your proposal with tRunJob and dynamic call of jobs. Unfortunately the performance problem is same there. That's why I pointed my brain in the direction of pool problem or late JDBC release of resources in every called job.
What exactly I have in my jobs structrure:
1 main job which decides to call some sequence of jobs based on specific dynamic logic
I tried to use tRunJob (dynamic jobs invocatin) and tSystem - the performance is exactly same - very bad. The problem is there and in case of static usage of tRunJob but with check for "Use independent process to run subjob"
Of course I checked my Oracle 11g server for session, transaction etc.. everything is fine there. The problem is comming when every job is in different process so that's why I'm really confused what can be the reason if the Talend uses only one JDBC connection for several processes.
The talend jobs and oracle are on same machine and the oracle is accessible on the localhost. I'm really blocked and confused.
Any other ideas ?

[Anonymous]

why I'm really confused what can be the reason if the Talend uses only one JDBC connection for several processes

Maybe I misunderstood it correctly, I though that each process has it's own connection and connection close component.
What's in a log of the jobs you run? Here is for example how I run jobs via cron and forward both standard and error output to log file:

#!/bin/bash
# Script has symlink from directories /etc/cron.*, function is determined by their names.
# For example symlink with name AUTO_P01_Synchronizace_Ciselniku_NAV.sh calls script
# /ip/talend/jobs/AUTO_P01_Synchronizace_Ciselniku_NAV/AUTO_P01_Synchronizace_Ciselniku_NAV/AUTO_P01_Synchronizace_Ciselniku_NAV_run.sh
# Skript first check if it's executed on active node of the cluster
HOSTNAME=$(/bin/hostname)
/usr/sbin/crm_resource -W -r Shared-IP-Address 2>/dev/null | /bin/grep -sq $HOSTNAME || exit
script=$(basename ${0%.sh}) # name of script without '.sh'
exec >>/ip/log/talend/${script}.log 2>&1  # write STDOUT and STDERR into log
date "+=== %Y/%m/%d %H:%M:%S %z Node $HOSTNAME ==="
su -s /bin/bash iprun -c "sh /ip/talend/jobs/${script}/${script}/${script}_run.sh"

The point is what's in the log of the child jobs, did they ever executed correctly?
Anyway, I am running out of ideas 😞