Performance tuning Oracle ODAC driver with FetchSize option on OnPremises Data Gateway

[Syndicate_Admin]

Hello everyone,

We are using the Oracle ODAC 64bit driver in conjunction with the Microsoft OnPremises Data Gateway solution for retrieving data from a  "on prem" Oracle database and populating our Microsoft Power BI Cloud Service.  The Oracle ODAC 64bit driver has been installed with the standard configuration and users are dissatisfied with the retrieval speed of records through this solution.

Our understanding is that Oracle ODAC 64bit driver configuration can be tuned using a FetchSize setting. We are hoping to perform that configuration in the "Microsoft.Mashup.Container.NetFX45.exe.config" file to implement the change just for the OnPremises Data Gateway rather than implementing machine wide via the registry or machine.config file.

We would prefer avoid swapping to OLE DB due to a lack of query folding with OLE DB.

If anyone else has done this it would be much appreciated if setting changes made could be shared.

Thanks.

[Syndicate_Admin]

Should have mentioned that we are using ODAC 64 Bit 12.2.0.1.1

[Syndicate_Admin]

@MichaelT_QIC 

I lack experience with oracle connectors, but you can take a look at Query folding in Power Bi.

Best Regards
Paul

[Syndicate_Admin]

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...).

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".

[Syndicate_Admin]

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.

[Syndicate_Admin]

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. 

[Syndicate_Admin]

Hi guys,

I am going through the same issue where all my data that comes from Oracle is 6 times slower than what it could be doing on the network.

The gateway has alot of memory and 80% is free, plus CPU is also sleeping.

How is the configuration of the FetchSize done for the gateway datasources? Should they be point to a TNSNAMES entry or directly connecting to IP to use the fetchsize definition on the mashup file?

Any other considerations to make sure we optimize the load of large tables from Oracle through the gateway? On TNSNAMES I have also specified a high value for the TDU and MTU settings.

Thank you

[Syndicate_Admin]

As @MichaelT_QIC and I discussed before you should go to your Gateway server and add that section he posted to the Microsoft.Mashup.Container.NetFX45.exe.config. However, I also had to make a small change to machine.config and the registry on that server as specified by Oracle. You can find these detailed in a PDF from Oracle at: https://www.oracle.com/a/ocom/docs/database/microsoft-powerbi-connection-adw.pdf.

If you do not have an Oracle account you likely need to create one and log-in to download that .pdf. I rebooted my server after making these three changes, but it may not be necessary.

Good luck @djpirra 

[Syndicate_Admin]

I have tested that configuration but I always seem to see the Trace on PowerBI Premium to show an increase on the READDATA event by 10k rows at a time and not respecting what I have defined on the FetchSize.
Is that normal? How do I know if the fetchsize is being respected?

Thank you

Regards,

LS

[Syndicate_Admin]

Thanks @busyjames , I have seen some improvements but there are still doubts on what is the possible to achieve...

As for example, I am using oracle as a datasource to process a PowerBI dataset.. even though currently my FetchSize is at 32MB and the timings improved a bit even though quite irregularly... I still see that PowerBI dataset is loading 10K rows at a time. Not sure if this is a default behavior of SSAS or has to do with the throughput itself.

Will try to figure it out... but in your opinion there will be the possibility of setting a "too" high value for the fetchsize that will hurt more than do good?

I need to transfer around 19GB at maximum speed.

Thank you