Re: Newsgroup: Serious memory leakage while using ADO recordset
From: Victor Koch <victor_at_correo.waldbott.com.ar>
Date: Wed, 18 Jun 2003 18:04:58 -0300
Message-ID: <uBOPRxdNDHA.2268_at_TK2MSFTNGP12.phx.gbl>
Date: Wed, 18 Jun 2003 18:04:58 -0300
Message-ID: <uBOPRxdNDHA.2268_at_TK2MSFTNGP12.phx.gbl>
[Quoted] Hi Ed, try this
Dim cn As String
Dim query As String
Dim connection As New ADODB.connection
Dim rs As ADODB.Recordset
Set rs = New 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'"
connection.Open cn
For i = 1 To 10000
rs.ActiveConnection = connection
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockBatchOptimistic
rs.Open query , , , , adCmdText
rs.Close
Next
connection.Close
-- Víctor Koch From Argentina. "Ed Barrett" <ebarrett_at_metastorm.com> escribió en el mensaje news:4026318.0306170936.28309859_at_posting.google.com...Received on Wed Jun 18 2003 - 23:04:58 CEST
> ** 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.