Re: Newsgroup: Serious memory leakage while using ADO recordset

From: Roy Fine <rlfine_at_nomail.org>
Date: Tue, 17 Jun 2003 14:42:42 -0400
Message-ID: <usYb9AQNDHA.704_at_tk2msftngp13.phx.gbl>


Ed,

you should open an Oracle service ticket...

regards
roy fine

"Ed Barrett" <ebarrett_at_metastorm.com> wrote in message news:4026318.0306170936.28309859_at_posting.google.com...
> ** Problem **
>
> 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 - 20:42:42 CEST

Original text of this message