I am devoloping a job which reads from oracle via oci stage .When I tried to configure the OCI stage I found these two prroperties called array size and prefetch memory setting.Could you please help me to undestand the exact significance of these two properties? Array size is 1 by default.Should I set it to a higher value like 10,000?If so then how will it change the behaviour of oci stage?Also prefetch memory setting is 0 by default.What value should I use here to gain a performance advantage?
Click the Help button in the stage. I've never had a need to touch the prefetch and Array Size has too many variables to simply give anyone a number. It controls how many records are sent 'at a time' over the network to the database, so 1 would be the slowest but sometimes things only work when it is set to 1.
-craig
"You can never have too many knives" -- Logan Nine Fingers
The optimal array size is so that one fetch fits inside one block (which I think is a default of 2048, but that might be version/platform/install dependant). As Craig said, 1 is the slowest but 100 might be even slower, so testing various values will be your best bet to tuning this stage using the array size.
There is another property called Transaction size. I understand this number defines the Commit Interval and should not be too small .But does it have any correlation with array size like transaction size should be multiple of array size or something?
True. That 'optimal size' varies based on ARL, network packet size, phase of the moon, yada yada yada. So, as you noted, testing is required to find that 'sweet spot' for each stage / job / combination.
-craig
"You can never have too many knives" -- Logan Nine Fingers
</a>
