tOracleOutput Performance

Six Stars

tOracleOutput Performance

We have requirement to load more than 2 millions records from file to oracle table.We have used simple tFileInput and tOracleOutput components to read the file and load the data into database.This is a direct insertion and there is no transform logic involved.However tOracleOutput performance is not impressive.We tried to increase the Batch Size from 10000 to 100000 with 2 parallel process.However we are getting below exceptions.

Exception in thread "Thread-3" java.lang.OutOfMemoryError: Java heap space
at java.lang.reflect.Array.newArray(Native Method)
at java.lang.reflect.Array.newInstance(Unknown Source)
at oracle.jdbc.driver.BufferCache.get(BufferCache.java:226)
at oracle.jdbc.driver.PhysicalConnection.getCharBuffer(PhysicalConnection.java:7600)
at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:2949)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10199)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:216)
at artemis_dev.transt_stg_load_0_1.Transt_Stg_Load$1tAsyncIn_tOracleOutput_1_ParallelThread.run(Transt_Stg_Load.java:10938)

 

We are looking for suggestion to improve the performance of this simple graph.

Eight Stars

Re: tOracleOutput Performance

Hi @vivek_u,

Go to Advanced Settings of Run Tab. Enable use specific JVM Arguments. U will see 256 and 1024 under use specific JVM Arguments. Increase it according to your System RAM e.g (512,2048) or (768,3072). U can use Bulk Execution provided Your Oracle is not RDS. 

Thirteen Stars

Re: tOracleOutput Performance

do not increase it more than 10000 (20000) 

for bulk insert - use bulk components of Talend if it Your server, or check AWS recommendations for bulk insert if it RDS

-----------