'Oracle OLEDB Memory Leak 12.2 and Greater (SSIS and C#)

I have found an issue with the Oracle OLEDB Driver “Oracle Provider for OLE DB” and MS SQL SSIS Packages.

This was first found in SQL SSIS packages that we use to transfer data from an Oracle database into a local MS SQL database. These SSIS Packages just have two Steps an OLEDB Source and an OLEDB Destination.

The SSIS Package I use a structure like this.

  1. One Data Flow Task
  2. This Data Flow Task Contains
    • OLEDB Source linked to the Oracle OLEDB Provider
    • OLEDB Destination linked to a MS SQL Destination
  3. All Standard Configuration settings have been kept.

This was working fine with the 12.1 Oracle drivers however when we upgraded to 12.2 we experienced memory issues linked to the quantity of data that is being transferred. On the 12.1 Driver the max memory used for each SSIS package was 100Meg how on 12.2 this could go up to as high as 7 Gig.

I then tested the same Package but this time using an ODBC data source. This then showed no memory issues.

I then tested if I could re-produce this problem with a simple C# .Net application. Using this simple code I have been able to reproduce similar memory issues. 

String sConnectionString = "Provider=OraOLEDB.Oracle.1;Password=XXXX;Persist Security Info=True;User ID=YYYY;Data Source=ZZZZ";
String mySelectQuery = "SELECT * FROM LargeTable";

OleDbConnection myConnection = new OleDbConnection(sConnectionString);
OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
int RecordCount = 0;
while (myReader.Read())
{
    ReaderCount = ReaderCount + 1
}

The above code executed once on a table with 130 Fields and 9 Million rows will use about 5 Gigs of memory. However if I run this same program using the 12.1 Oracle OLEDB driver it will only consume about 10K of memory.

I do not know if this issue is linked to the same issue I am having on SSIS?

All Testing has been done using the 64Bit Drivers and the ODAC installations that are on Oracle’s web site. I have also been able to reproduce this problem on multiple machines. SQL 2016 and SQL 2017 have also been tested.

Has anyone else experienced issues like this with either SSIS or C#?

Update

The only workaround to this issue we have found is to use a different version of the Oracle Client that does not have this issue. We found this also exists in some legacy versions of the Oracle Client. We then made the decision to stop using Oracle OLE DB connections.

We now use the Dot Net managed driver from Oracle in a custom import process we developed that does not use SSIS to import the base data. We managed to get similar performance using this in combination with the .Net SqlBulkCopy Class.

c++sharporaclessisoledb


Solution 1:[1]

The issue you saw could have been related to Oracle's rowset caching mechanism. By default, Oracle will cache a rowset in memory. I've not seen the memory issue with the native Oracle OLE DB provider but I have seen it using a custom OLE DB provider that utilizes the Oracle OLE DB provider. Changing the CacheType to "File" resolved the issue for us. See: https://docs.oracle.com/cd/B28359_01/win.111/b28431/using.htm#i1017121 (OraOLEDB-Specific Connection String Attributes for Rowsets).

Solution 2:[2]

Oracle confirmed a memory leak in the 12.2 drivers and released a patch:

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2579259_1.html

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 mcessna
Solution 2 Kendall Lister

Related Questions