Array Size in OCI Stage, Performance Tuning

[muruganr117]

Hi,

With regard to tuning, i saw in forums that we can increase the Array size of OCI stage for better performance.

Scenario is as mentioned. I have a job in which am retrieving data from many individual tables and writnig into hash files for further processing as per the business rules.

Number of such OCI stages is 25, since each SQL has its own business logic involved.Among these around 10 to 15 retrieve bulk volume of data.

So my question is

1. Is which scenario does increasing the array size be helpful for better performance
a) Bulk Volume of data handling
b) In general irrespective of volumes
2. By Default the array size is 1, what is the maximum size which can be set
3. If i set say 500 across 10 OCI stages which retrieve bulk volume of data, will it adversely affect the overall performance
4. Is the mentioned array size BYTE or KB

Thank you in advance for help

regards
Murugan Ramachadran

[throbinson]

You've got a single job with 25 OCI stages all retrieving data? Hunh.
RE#4.
Arraysize. I did something really strange, I looked it up!
Too high and you'll blow the buffer and the job will fail. This is dependent on the number of columns and their datatype sizes.

Putting aside your fundamental question of "How long is a piece of string" I'll boldly answer that your problem is a combination of arraysize and bad hash file definitions. That is if we can get past the 25 OCI stages in a single Server job. You might be well served to break out the long running or high volume OCI Stages into separate jobs and run them concurrently, if possible.

From DataStage Help...

The number of rows written to or read from the database at a time.

Input. The number of rows to be transferred in one call between DataStage and Oracle before they are written. Enter a positive integer to indicate how often Oracle performs writes at a time to the database. The default value is 1, that is, each row is written in a separate statement.

Output. The number of rows read from the database at a time. Enter a positive integer to indicate the number of rows to prefetch in one call. This value is used both for prefetching rows and for array fetch.

Larger numbers use more memory on the client to cache the rows. This minimizes server round trips and maximizes performance by executing fewer statements. If this number is too large, the client may run out of memory.

[muruganr117]

Hello Robin,

Thank you for details. Actually i have not faced any problems,
Since there isnt any bad hash file definition or coz of arraysize.
My question was a general one to understand whether there will be impact, well ahead before i set the array size for the each OCI stage.

Anyways i got info, Thanks again.

regards

You've got a single job with 25 OCI stages all retrieving data? Hunh.
RE#4.
Arraysize. I did something really strange, I looked it up!
Too high and you'll blow the buffer and the job will fail. This is dependent on the number of columns and their datatype sizes.

Putting aside your fundamental question of "How long is a piece of string" I'll boldly answer that your problem is a combination of arraysize and bad hash file definitions. That is if we can get past the 25 OCI stages in a single Server job. You might be well served to break out the long running or high volume OCI Stages into separate jobs and run them concurrently, if possible.

From DataStage Help...

The number of rows written to or read from the database at a time.

Input. The number of rows to be transferred in one call between DataStage and Oracle before they are written. Enter a positive integer to indicate how often Oracle performs writes at a time to the database. The default value is 1, that is, each row is written in a separate statement.

Output. The number of rows read from the database at a time. Enter a positive integer to indicate the number of rows to prefetch in one call. This value is used both for prefetching rows and for array fetch.

Larger numbers use more memory on the client to cache the rows. This minimizes server round trips and maximizes performance by executing fewer statements. If this number is too large, the client may run out of memory.