Oracle - ORA-3114

[Raftsman]

Could someone please provide some info,

Our shop is a using an Oracle 10g database. Our Datastage server consists of a dual core process, 4 node capability with 10gigs of RAM.

I created multiple jobs with multiple Oracle enterprise stages in each. My sequence executes multiple jobs that can access approx 10-15 Oracle Enterprise threads at the same time. Some of these jobs have 20-30 different stage in each. Even if I run on 1, 2, 3 or 4 nodes, the job aborts with a ORA-3114 could not connect error. I have a suspicion that I am hitting a maximum thread problem. Is there a way to determine this.

Secondly, I am still very confued about the 1,2,3 and 4 node processing. How and when do we take advantage of the Server. I understand that the parallel processing will split the stage up into the amount of nodes but it also uses up memory. Is there a way to figure out the optimal usage.

Thanks

[ArndW]

There is no accurate formula to compute how many nodes to use in aa good configuration file. In fact, each job usually has an optimal setting. The only way to really do this is to run the job with 1,2,3,etc. nodes and measure which one gives the best performance.
Throughput is such a complex issue in PX/EE jobs; not only is it affected by the number of processes running but on how memory and buffering is configured, what else the machine is doing at the same, database location (local or remote) and configuration.
If you run with a 1-node configuration and still get a 3114 error, try APT_DUMP_SCORE to see how many processes are actually being started.

[chulett]

I have a suspicion that I am hitting a maximum thread problem. Is there a way to determine this.

Work with your DBA, have them trace your sessions. They should be able to pretty easily determine if you are blowing out any connection limits.