Error When Array Size is greater than 1

[chowdary]

Hi all,

We are facing a strange problem. All our jobs are aborting when we put array size greater than 1. If we put Array size as 1 jobs are running fine but performance is really bad like 40-50 rows/sec. If we put Array size greater than job will start processing data like 300-400 rows/sec and aborts after some time with an error

ORA-03135: connection lost contact
ORA-03114: not connected to ORACLE
ORA-03113: End of File on Communication Channel.

Source is Oracle 10g
Target is Oracle 10g
We are using DRS stages for reading and loading the data.

Can any one help us on this problem.

Thanks in Advance.

[gateleys]

We are using DRS stages for reading and loading the data.

What happens when you use the OCI stage instead of DRS?

gateleys

[gateleys]

We are using DRS stages for reading and loading the data.

What happens when you use the OCI stage instead of DRS?

gateleys

[chulett]

How much greater than 1 are you using? And what is the Transaction Size you are using?

[chowdary]

We are using DRS stages for reading and loading the data.

What happens when you use the OCI stage instead of DRS?

gateleys

We are getting same error with OCI Stage

[chowdary]

How much greater than 1 are you using? And what is the Transaction Size you are using?

We tried different array size and Transction size, still we are getting same error.....Whatever Transaction size we put the array size has to be 1, if we put even 10 in the array size it fails.....One more thing i forgot to mention is Both the Source and Target Database are behind enclave ( Firewalls) and we have port's opened between Ascential Server and Source and Target Database Servers.

[chulett]

I don't think your firewalls would cause any of this. Another question - what Update Action are you using in the stage?

[chowdary]

I don't think your firewalls would cause any of this. Another question - what Update Action are you using in the stage?

The problem is not with one job, all jobs are having same problem.Some jobs are insert only, some are Update and Insert,some are clear and insert,some are update only.

[kduke]

I think it is a database issue. I think you need to use read uncommited or something that does not lock the source records. I think you have a database lock issue.

[chulett]

Weird... I've got an enormous number of jobs that use various mixtures of isolation, update actions, array and transaction sizes and have never seen an issue like this.

I'd suggest you open a ticket with your official Support provider, see if it's anything they've ever encountered before.

You might also want to have your DBA trace your sessions, see if that reveals anything about the problem...