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

[MichaelT_QIC]

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.

[Adamboer]

To configure the FetchSize setting for the Oracle ODAC 64bit driver used with the Microsoft OnPremises Data Gateway, you can make the changes in the "Microsoft.Mashup.Container.NetFX45.exe.config" file as you mentioned.

To do this, follow these steps:

1. Locate the "Microsoft.Mashup.Container.NetFX45.exe.config" file on the server where the gateway is installed. This file should be in the installation directory of the gateway.

2. Open the file in a text editor.

3. Search for the Oracle connection string in the file. It should look something like this:

   <add name="Oracle" connectionString="Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;" providerName="System.Data.OracleClient" />

4. Add the FetchSize setting to the connection string by appending ";FetchSize=<size>" to the end of the connection string. Replace <size> with the desired fetch size value, which should be a positive integer. For example:

   <add name="Oracle" connectionString="Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;FetchSize=1000;" providerName="System.Data.OracleClient" />

5. Save the changes to the file and restart the OnPremises Data Gateway service to apply the changes.

Note that the fetch size value should be chosen based on the size of the result set and the available memory on the server. A larger fetch size can improve performance by reducing the number of round-trips to the database, but may also increase memory usage.

[Portmone]

That only did not try with acceleration of requests to Oracle.
There is one report for 370+ million rows, the initial data loading can take about 4-5 hours.
The server with the gateway and the server with Oracle are practically idle.
RAM 120GB
Processor for 64 cores.
Network 10 gigibit.

Actually loading all the same rests against something.
The fetch size is 16777216. Moreover, both in Microsoft.Mashup.Container.NetFX45.exe and in the registry itself for odac.

No one has found a way to speed up the selection and get a download of at least 200-300 megabits?

[MallikarjunaBan]

hi u mean it did not work after increasing fetch size please let me know for its 3hrs for 32million records

[busyjames]

I can not make sense of your message. We have moved on to the Oracle Client for Microsoft Tools. The install can be found at https://www.oracle.com/database/technologies/appdev/ocmt.html.

Good luck. 

[busyjames]

Frankly, Power BI is not as fast as a rival product in loading information. I will skip any typical poster's points of why would you need that much data / use a star schema / etc.

We are no longer using ODAC or instant client here to connect to Oracle's ADW from Power BI. We are using Oracle Client for Microsoft Tools (the latest Oracle-Client-for-Microsoft-Tools.exe) after Oracle's security changes in early March. I assume you are reaching an ADW but if not I still would expect this to be your best tools. We decided to scrap our gateway server and rebuild for this new setup. Registry key settings are still in effect with this different solution. I do not recall changing the machine.config manually and I no longer have access to that server to verify.

We run this setup on both the gateway server and on Power BI Desktop users machines.

Good luck!

Sources

https://www.oracle.com/sn/a/ocom/docs/database/microsoft-powerbi-service-gateway-adw.pdf

https://www.oracle.com/database/technologies/appdev/ocmt.html

[MallikarjunaBan]

'r u using oracle 12 version or 19.  this new driver works for 19 only not for old versions

[busyjames]

I am on Cloud. If you are using an older on-prem version of Oracle then you will need to go through the information at oracle.com to find the right installs. 

[djpirra]

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

[busyjames]

Keep in mind the FetchSize specifies bytes not rows. My row per trip went up significantly on all my (100+) reports, but of course the row count was different for each report. I suggest you concentrate on final load times. If you installed the ODAC Mike specified with ODP.NET installed machine wide and have seen an improvement that is a great sign FetchSize is respected. I suggest going through the Oracle documentation or forums to find the key points which you are searching. Good luck, maybe someone out there has something better or more to add than me.

[djpirra]

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

[busyjames]

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 

[djpirra]

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

[MichaelT_QIC]

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

[busyjames]

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.

[MallikarjunaBan]

hows the perf now pls share the format required in mashup facing same issue

[MichaelT_QIC]

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. 

[MallikarjunaBan]

Hi @MichaelT_QIC   will this work for oracle odac version 12 i am troubled now need to pull 32million records 

[MichaelT_QIC]

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.

[V-pazhen-msft]

@MichaelT_QIC 

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

Best Regards
Paul

[MichaelT_QIC]

Should have mentioned that we are using ODAC 64 Bit 12.2.0.1.1