Forum Discussion
Performance tuning Oracle ODAC driver with FetchSize option on OnPremises Data Gateway
Hi all,
I found this format for the mashup file worked:
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2"/>
</startup>
<oracle.dataaccess.client>
<settings>
<add name="FetchSize" value="500000"/>
</settings>
</oracle.dataaccess.client>
</configuration>
Tweak the FetchSize as required.
The "default" value is 131072 bytes according to Oracle Documentation pages (https://docs.oracle.com/en/database/oracle/oracle-database/19/odpnt/InstallUnmanagedConfig.html#GUID-140DDC35-8380-4BF7-8855-273D317C610D).
For my test with a 330K records returned dataset - setting to 500000 reduced refresh runtime from 10 minutes (default install settings) to 3 minutes. Setting the value 1000000 reduced refresh runtime to 1 minute. Though not sure what the consequences to memory usage was.
I guess I should say "YMMV".
Thank you for posting this Michael. I definitely saw improvements when adjusting this locally. However, I do not see those gains on the gateway server. I adjusted this mashup config but did not see an improvement. Did you reboot the server or just the gateway service via the administrator tool? Thanks in advance.
Hi, would have been a gateway service restart while figuring out the best value. Server has also been reboot since then as part of server patching processes.
Hi MichaelT_QIC will this work for oracle odac version 12 i am troubled now need to pull 32million records
Hi MallikarjunaBan the Oracle database we are connecting to now is version 19c and I'm not sure when the DB was upgrade to that version, so not sure I can guarantee it would work with version 12.
We are also in the process of upgrading to the latest Oracle-Client-for-Microsoft-Tools.exe as part of an environment uplift to latest versions.
hows the perf now pls share the format required in mashup facing same issue
