We are using tOracleConnection component with shared DB connection enabled. Once we register this connection there are multiple subjobs running in parallel using tParallelize and tFlowtoIterate to fetch data using select queries to write to a file. We are observing slowness in all the subjobs, can this be issue with shared connection ? How does shared connection with multiple subjobs executing in parallel works ?
It depends on the driver. Some drivers create a connection that makes all the parallel queries to single threaded. Some drivers allow the use of connection pool so a single connection can be used from multiple thread.
Sometimes this pooling is controlled by parameters.
I personally would run some tests (a simple DBMS_SESSION.SLEEP could help) and see how the runtime changes.
If you want to get an ide what happens inside the JDBC driver you can do that via visualVM or JFR recoridng as described here: https://community.talend.com/s/article/Using-VisualVM-to-record-Java-Issues?language=en_US
