Transaction Size and Array Size

[vimali balakrishnan]

Hi,

Can anyone clarify me about Transaction Size and Array size?

What will be the values given to these parameters to increase the performance?


Regards,
Vimali.

[ArndW]

The Transaction Size is the number of rows between database commits. If this is 0 then a commit is done only at the end of a run; this is the quickest choice. Unfortunately, the downside is that with large numbers of records you can or will overflow the rollback space within the database. The size of this can be configured.

The array size is the transfer buffer size between DS and the Oracle system. I had an exact description sometime ago but can no longer find it. I've found that it is worth playing with different values when reading from Oracle until the best performance for that particular query comes out, but unless you always have the same number of bytes per row it becomes difficult to really specify a fixed value. Perhaps someone with more knowledge here can describe the array size in more detail.

[talk2shaanc]

Can anyone clarify me about Transaction Size and Array size?

Details about them can be found in the pdf documents as well as datastage help. You should try to get this kind of simple details yourself and post a thread when you have some technical difficulties.

Transaction size: This is the number of rows written before the data is committed to the data table. The default value is 0, that is, all the rows are written before being committed to the data table.
Array Size: This is the number of rows written at a time. The default is 1, that is, each row is written in a separate operation. If the current setting of Parameter array size causes available storage space to be exceeded at run time, you will be informed when you compile the job.

Note: If the Parameter array size setting conflicts with the Rows
per transaction setting, the former takes precedence.

What will be the values given to these parameters to increase the performance?

There is no fixed values that should be set for these parameters, its only about analysis and getting optimum value.
By analyzing the length of the records and the average volume of the data, you should set a value and get the performance details.
Run the job several time with different values for each of the parameter (increase or decrease the value) in each run. Maintain a table with statistics for each run.

Finally decide on a optimum value, from the lists of run.

[arunverma]

HI ,

You can put Arraysize as 32767 which is the maximum limit , to put value of transaction size ( commit size ) , if you put 5000 the in oracle after 5000 it will commit in database , so to get optimum value pl concern your Oracle DBA .

Regards

Arun