Tuning the array size of OCI stages

[michaelsarsnorum]

Hi

We tried some different values for the array size of a job included in our datawarehouse etl-jobs. What we found was that we were able to double the performance of the job by setting the arraysize to 20 (higher than this and it went back down).

What I'd like to know is if there are any formuales for using then setting array sizes. We have inherrited a large system from someone else, it contains a lot of jobs where the default array size of 1. We do not have the resources to go over and check every job and test it with different values to see what makes gives the best performance.

Any help is apreciated.

Michael

[kcbland]

It's a factor of the average number of characters in a typical row, packet sizes, etc. You can hit the help button on the ODBC/OCI stages and see the explanation more clearly. You're going to need to profile your data to figure out the average row size and how many rows will fit into the array optimally. That value is a production side tunable and relies on a certain level of stability in the data composition.

Your best option is not to go for the high water-mark, but go a little under the average. You'll probably find out its worth just a quick guess and up the number from the default of 1. You may also consider the bulk loader route for pure inserts, which usually yields more return by far over any array tweaking.