Number of open oracle/db connections in a job

deepticr · Post by **deepticr** » Thu May 27, 2010 3:42 am

Hi,

We have designed jobs to extract high volume data from some sources. So, we wanted to configure them for >4nodes configuration.

Some of the jobs have 10 oracle stages. If I execute this job on a 4node configuration, then will I have 40 open database connections (4*10)?

Thanks
Deepti

ray.wurlod · Post by **ray.wurlod** » Thu May 27, 2010 4:57 am

Possibly.

Of course, your DBA may not have configured that many listeners, so your job (at least one of its nodes) may fail.

deepticr · Post by **deepticr** » Thu May 27, 2010 5:58 am

Will there be 40 open connection, even if the oracle stage are set in sequential execution mode?

srinivas.g · Post by **srinivas.g** » Thu May 27, 2010 7:06 am

deepticr · Post by **deepticr** » Fri May 28, 2010 2:53 am

Could anyone elaborate on how the database connections are established in a parallel job having an oracle stage running sequentially?

Does sequential mode of operation mean that data from the DB is handled by the conductor node and this node opens only a single connection to the oracle DB?

We have about 300 listners set up in the db end. But the extract job having 10 oracle stages is multi instanced and runs for 10 different sources. So, if the extract for all the 10 sources is triggered simultaneously then I'll run into problems with 400 (10*4*10) open connections.

Please advice on the approach to be taken to resolve this issue.

chulett · Post by **chulett** » Fri May 28, 2010 6:25 am

Yes, that is my (possibly incorrect) understanding: sequential = conductor and thus one per stage. One simple solution - don't "trigger simultaneously".

chulett · Post by **chulett** » Fri May 28, 2010 6:25 am

ps. You don't have "300 listeners". However, you do probably have one listener that is configured to allow a maximum of 300 concurrent connections.