huge memory leak with Objects

[jschmied]

I've been hit badly by a huge memory leak wiht the following code:

FOracleObject := TOracleObject.Create(FSession, FSchema + '.O_AXMLDATAIN', '');

SetComplexVariable('DATA', FOracleObject);

Execute;

FCount := FOracleObject.ElementCount;

// Read Object Attributes (1..n RefElement)

for i := 1 to FCount - 1 do begin
with FOracleObject.RefElements.Pin(poLatest, plNone) do begin
try
XMLString := LOBAttr('XMLDATA').AsString;
FNR := GetAttr('NODE');
finally
Free();
end;
end;
end;

// Write Object Attributes (0. RefElement)

with FOracleObject.RefElements[0].Pin(poAny, plExclusive) do begin
try
LOBAttr('XMLDATA').AsString := FResultString;
SetAttr('NODE_VERSION', FVersionsNr);
Flush();
finally
Free();
end;
end;

if assigned(FOracleObject) then FreeAndNil(FOracleObject);

-------------
SQL: Statement
:RET:=READJOBTIMEOUT,:TIMELEFT,

ATA,ARAMETER,:ERROR_TEXT);

-------------
PL/SQL Function:

FUNCTION ReadJob (
n_queue_timeout IN INTEGER,
n_product_time_left OUT INTEGER,
axmldata_in OUT o_aXmlDataIn,
parameter OUT CLOB,
s_error_text OUT VARCHAR2
)
RETURN INTEGER IS
...

SELECT xmldata_in INTO axmldata_in FROM cycle_data;

-------------
Types:
CREATE OR REPLACE TYPE o_aXmlDataIn AS VARRAY(32) OF REF o_XmlDataOut;

CREATE OR REPLACE TYPE o_XmlDataOut AS OBJECT
(
node VARCHAR2(32),
xmldata CLOB
...
);

per execution i loose about 10 KB of memory. Memproof shows no leaks
within the code but some not freed memory deep in th oracle layer called
by both pin methods. Stangely a simple logoff/logon frees the memory.

Any solutions????

Thanks

Juergen

 

[jschmied]

for i := 1 to FOracleObject.ElementCount- 1 do begin
with FOracleObject.RefElements.Pin(poLatest, plNone) do begin
try
... := LOBAttr('XMLDATA').AsString;
... := GetAttr('NODE');
finally
Free();
end;
end;
end;

OCIParamGet 10005F86 (oci.dll)
TOracleObject:

escribeRecursive 0049F750
TOracleObject:

escribe 0049F92F
TOracleReference:in 0049EACA

OCIAttrGet (oci.dll)
TObjectAttribute::SetAttrInfo 0049DBF2
TOracleObject:

escribeRecursive 0049F77B
TOracleObject:

escribe 0049F92F
TOracleReference:in

------------------

with FOracleObject.RefElements[0].Pin(poAny, plExclusive) do begin
try
LOBAttr('XMLDATA').AsString := FResultString;
SetAttr('NODE_VERSION', 12345);
Flush();
finally
Free();
end;
end;

OCIObjectPin 6000B2DA (oageneric8.dll)
OCIObjectPin 100072C8 (oci.dll)
TOracleReference:in 0049EA7F

 

[jschmied]

In the last message I posted some stacktraces of unfreed resources found with memproof.

Thanks

Juergen

 

[Marco Kalter]

What is your Net8 and Server version?

------------------
Marco Kalter
Allround Automations

 

[jschmied]

Both are 8.1.7.2.

 

[jschmied]

After some investigation it looks like the memory is used by the object cache. Since i work with many different objects the cache grows. If I do the same operations again und again with the same objects the memory usage is constant.

How do I free the objects from the cache? I found OCICacheFree and OCIObjectFree.
How long does DOA hold the objects in the cache? Does it call OCIObjectFree or only OCIObjectFlush?
Or can I set the cache size to a smaller value?

Thanks

Juergen

 

[Marco Kalter]

Direct Oracle Access holds the objects in the cache as long as a TOracleObject instance exists.

------------------
Marco Kalter
Allround Automations

 

[AndreyB]

We're using TOracleQuery which retrieves information from object view which has embedded objects.
When 'select value(v) from my_view v' is issued the memory usage does not grow, but if we issue something like that 'select v.* from my_view v' or 'select v.embedded_object from my_view' the all available memory is consumed by application.
What are we doing wrong and why DOA properly cleans up object instances upon Query.Next in the first case and not in the second.

 

[jschmied]

If I do ocasionally the following:

function OCICacheFree(envhp: Pointer; errhp: Pointer; svchp: pointer): DWORD; stdcall; external 'oci.dll';

procedure TForm1.Button2Click(Sender: TObject);
var
OCIError: OracleCI.OCIError;
POCIError: ^OracleCI.OCIError;
result: integer;
begin
POCIError := @OCIError;
result := OCICacheFree(OracleSession.ExternalENV, POCIError, OracleSession.ExternalSVC);
if result OracleCI.OCI_SUCCESS then ShowMessage('OCI');
end;

them memory is no longer growing.
The problem is I have to close all querys before so it's not a option for my application.

mkalter wrote: Direct Oracle Access holds the objects in the cache as long as a TOracleObject instance exists.

Why is the following loop the eating memory??

for j := 0 to 49 do begin
OracleQuery.SetVariable('ID', j);
OracleQuery.Execute();
OracleObject := OracleQuery.ObjField('DATAIN');

for i := 0 to OracleObject.ElementCount - 1 do begin
OracleObject1 := OracleObject.RefElements.Pin(poAny, plNone);
Label1.Caption := IntToStr(OracleObject1.GetAttr('ID'));
Update();

OracleObject1.Free();
end;
end;

The query is: "SELECT datain FROM cycledata WHERE id=:id"

The Table:
create table CYCLEDATA
(
ID NUMBER,
DATAIN ODATAIN
)

The types:
CREATE OR REPLACE TYPE oDataIn AS VARRAY(32) OF REF oDataOut

CREATE OR REPLACE TYPE oDataOut AS OBJECT
(
cycle_id NUMBER,
id NUMBER,
data CLOB
)

Thanks

J.Schmied

 

[AndreyB]

This is what I found in technet OCI forum
-----------------------
When you select a column object you are fetching the object by ‘value’. The lifetime of these objects in the cache is under the control of the application. The object cache has no way of knowing when the application is done using this object so the application has to explicitly free the object by calling OCIObjectFree() otherwise the cache will continue to grow. Note that by using OCICacheFree() you are freeing all ‘value’ instances currently in the cache.

The cache settings that you refer to control the objects which are loaded into the cache by pinning (i.e de-referencing) an object reference. Pinning of an object is achieved by calling OCIObjectPin() and passing the reference to the object you want loaded. A pinned object becomes a candidate for being aged out when it is unpinned (via OCIObjectUnpin()). Whenever memory is allocated in the cache a check is made to see if the maximum cache size has been reached. If yes, the cache automatically frees (ages out) least-recently used objects which have a pin count of zero (note this does not apply to value instances). The cache continues freeing such objects until memory usage in the cache reaches the optimal size, or until it runs out of objects eligible for freeing. The max cache size and optimal cache size are controlled by the OCI_ATTR_CACHE_MAX_SIZE and OCI_ATTR_CACHE_OPT_SIZE settings.

Hope this answers your question.
-----------------------

The question is why despite the fact that TOracleQuery frees object using OciObjectFree in .Next method, embedded objects are not freed.
'select v.* from my_view v'
but this one works just fine
'select value(v) from my_view v'
When 'select ref(v) from my_view v' and following Pin is used all described rules with Object Cache are applied and really work.