oracle stage array size

parvathi · Post by **parvathi** » Mon Dec 11, 2006 1:10 am

hi all,
In my oracle stage and under the arraysize what should be the normal opted length.

some say we have to put arraysize as 1
and some say it to be 1000.

Does it have any performance on the arraysize

BalageBaju · Post by **BalageBaju** » Mon Dec 11, 2006 1:25 am

Hi,

Array size determines the number of rows to be read from the database or number of rows write to the database at a single time.

For example, for reading data from database, if it is 1, then for each and every row it will read the data from the database.

If the array size is 10 for writing, then after every 10 rows it will write the data to the database.

Array size may increase the performance. But if the array size is too large, it may use the large amount of client memory and sometimes it will go for out of memory.

WoMaWil · Post by **WoMaWil** » Mon Dec 11, 2006 1:47 am

Balaji is right. It is not easy to find the right size. As he said a good rule of thumb is to balance between both size when reading and writing. The one has to be same or factor of the other. 5-10 or 10-5 is okay, 10-12 or 12-10 is suboptimal.

Same is true with commit size.

If there would be an optimal size for all constallations the datastage devellopers would have set it to that size.

parvathi · Post by **parvathi** » Mon Dec 11, 2006 3:20 am

thanks,
how can i check the performance?Does THE ARRAY SIZE GIVEN RELATED TO THE TIME TAKEN TO COMPLETE THE JOB

ray.wurlod · Post by **ray.wurlod** » Mon Dec 11, 2006 4:29 am

Only by trial and measurement. There are no magic formulae. Array size is not really about optimizing select/load; it's about optimizing traffic between DataStage and Oracle.