Newsgroup: Serious memory leakage while using ADO recordset

• Problem **

[Quoted] [Quoted] We have discovered that Oracle's OLEDB provider leaks. Specifically when using ADO recordsets via a client-side cursor, where the select statement includes one or more columns of type CLOB.

• Environment **

Oracle Server: 9.2.0.3.0
Oracle Client: 9.2.0.3.0
OLEDB driver: 9.2.0.2
OS: Windows 2000 Professional, SP3

• To reproduce the problem **
  1. Create and populate two tables using the SQL below:

CREATE TABLE LeakTab
(

 FirstCol VARCHAR2 (31) NOT NULL,
 SecondCol CLOB,
 CONSTRAINT ePK_LeakTab
 PRIMARY KEY( FirstCol ))
/

CREATE TABLE NonLeakTab
(

 FirstCol VARCHAR2 (31) NOT NULL,
 SecondCol VARCHAR2 (31),
 CONSTRAINT ePK_NonLeakTab
 PRIMARY KEY( FirstCol ))
/

INSERT INTO LeakTab

    ( FirstCol, SecondCol )
VALUES
    ( 'Dummydata0001', NULL )
/

INSERT INTO NonLeakTab

    ( FirstCol, SecondCol )
VALUES
    ( 'Dummydata0001', NULL )
/

2. Create a standard VB .exe project. Add a button to the default form. Add the VB code below to the button click event:

    Dim cn As String
    Dim query As String
    Dim connection As New ADODB.connection     Dim rs As ADODB.Recordset
    Set rs = CreateObject("ADODB.Recordset")

    cn = "Provider=OraOLEDB.Oracle.1;Data Source=mydatasource;User Id=User;Password=password;FetchSize=100;CacheType=Memory;PLSQLRSet=1"

    query = "Select * FROM LeakTab where FirstCol = 'Dummydata0001'"

        rs.CursorLocation = adUseClient
        connection.Open cn
        For i = 1 To 10000
          rs.Open query, connection, adOpenStatic,
adLockBatchOptimistic
          rs.Close
        Next
        connection.Close

You will want to adjust the connection string (cn) to point to the database where the two tables were created.
(There is also a C++ version of this available).

3. Run the app and press the button. Follow the app's memory usage
(Private Bytes) with Performance Monitor. The leakage should be
readily apparent in a matter of minutes.

4. Having stopped the test, try the same thing again - but this time make the query do its select against the NonLeakTab table instead. You will see no such leakage.

• Further comments **
  • I have tried to locate where the leakage is occurring, so far without success. Using the CMallocSpy sample app, I was able to satisfy myself that the OLEDB provider is not leaking COM-allocated memory during recordset operations. (This did show a leak occurring per connection acquired, but the size of the leak is comparatively small).
  • The options used during the recordset Open can be amended, without altering the leakage. For instance I also tried:

    rs.Open query, connection, adOpenDynamic, adLockPessimistic

• Given that memory usage increases constantly over time (> hour) for the same SQL statement, it seems unlikely to relate to any form of caching.

Any help or suggestions about how to circumvent this problem gratefully received.

Ed Barrett
Metastorm Inc. Received on Tue Jun 17 2003 - 19:36:18 CEST