Preferred value for 'Insert Array Size'

[new_dsuser]

Hello,

I'm working on some parallel jobs that load Oracle 11g tables.
I'm using the User Defined Update/Insert option.
I was wondering if there is any preferred value for the 'Insert Array Size' parameter. By default this has been set to 500. I see that if I increase this, the job runs faster because of the reduced number of DB hits. I'm thinking of setting this to 2000 at least.

Can anyone please shed some light on what's the best practice in terms of setting this value?

Thanks!

[ray.wurlod]

There are three considerations.

One is your commit interval. Array size should not be larger than commit interval, and rows/transaction should ideally be a whole multiple of array size.

Second is related. If you are using auto-commit, or if you require accurate reporting of row errors, you need to specify array size 1.

Third is what array size is meant to do. DataStage will send/receive arrays of values. These should fit nicely into network packets. So the "best" value will be a multiple of row size that fits neatly into one or a few network packets, so that not too much space is wasted in network transport.