1. Using Oracle Client for Microsoft Tools
  2. Connecting with Microsoft Tools
  3. Performance Tuning for Large Data Retrievals

3.3 Performance Tuning for Large Data Retrievals

Recommendations for improving Oracle database retrieval performance with Microsoft tools.

Typically, BI and ETL applications retrieve large amounts of data from a source database for further processing. The ODP.NET FetchSize setting determines the amount of data ODP.NET fetches into its internal cache upon each database round trip. You can improve performance by an order of magnitude by significantly increasing FetchSize when retrieving large result sets.

Parent topic: Connecting with Microsoft Tools

3.3.1 Performance Tuning for Unmanaged ODP.NET

Improve performance for unmanaged ODP.NET by increasing FetchSize in the Windows Registry.

This method works for tools that use unmanaged ODP.NET, including Microsoft Power BI Desktop, Microsoft Power BI Service, and Microsoft SQL Service Analysis Services.

To increase FetchSize in the Windows Registry:

  1. Launch the Windows Registry editor (regedit.exe).
  2. Go to the following Registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.122.19.1
  3. Add the String Value FetchSize and set it to a value larger than the default (131072), such as 4194304 (4 MB).
    FetchSize can be set as large as int.MaxValue.
  4. Restart the Microsoft tool and run your queries with the new setting.

Parent topic: Performance Tuning for Large Data Retrievals

3.3.2 Performance Tuning for Managed ODP.NET

Improve performance for managed ODP.NET by increasing the FetchSize in the .NET machine.config file.

This method works for tools that use managed ODP.NET, including Microsoft SQL Server Data Tools, Microsoft SQL Service Integration Services, and Microsoft SQL Server Reporting Services.

To increase FetchSize in the .NET machine.config file:

  1. Locate the .NET machine.config file. This file is generally located in one of two directories. The file you need to modify depends on whether you are running 32-bit or 64-bit processes.
    • For 32-bit, the location is C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config
    • For 64-bit, the location is C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config
    Modifying the machine.config file requires Windows Administrator privileges.
  2. Add an <oracle.manageddataaccess.client> section in the machine.config file for managed ODP.NET. This section should be placed within the <configuration> section and after <configSections></configSections>.
    This example sets FetchSize to 4 MB. New configuration additions are in bold.
    <configuration>
    <configSections>
    …
    </configSections>
    <oracle.manageddataaccess.client>
        <version number="4.122.19.1">
            <settings>
                <setting name="FetchSize" value="4194304" />
            </settings>
        </version>
    </oracle.manageddataaccess.client>
</configuration>
  3. Restart the Microsoft tool and run your queries with the new setting.

Parent topic: Performance Tuning for Large Data Retrievals