USER SGA is growing by using ARRAY-DML.

[ljohn]

System : Windows NT SP5
Database-Version : Oracle 8.0.5.2.1
DOA : Version 3.3.2

I am using the Array-DML-Feature of DOA.

On startup of my application I do following only once:
...
with vMW do
begin
lfd := VarArrayCreate([0,0], varVariant);
Datum := VarArrayCreate([0,0], varVariant);
Sammler := VarArrayCreate([0,0], varVariant);
v_sps := VarArrayCreate([0,0], varVariant);
Element := VarArrayCreate([0,0], varVariant);
Attribut := VarArrayCreate([0,0], varVariant);
Wert := VarArrayCreate([0,0], varVariant);
v_Typ := VarArrayCreate([0,0], varVariant);
ga := VarArrayCreate([0,0], varVariant);
end;

with qryMW,vMW do
begin
VarArrayReDim(LFD , cMaxMWListCount);
VarArrayReDim(v_sps , cMaxMWListCount);
VarArrayReDim(Datum , cMaxMWListCount);
VarArrayReDim(Sammler , cMaxMWListCount);
VarArrayReDim(v_sps , cMaxMWListCount);
VarArrayReDim(Element , cMaxMWListCount);
VarArrayReDim(Attribut , cMaxMWListCount);
VarArrayReDim(Wert , cMaxMWListCount);
VarArrayReDim(v_Typ , cMaxMWListCount);
VarArrayReDim(ga , cMaxMWListCount);

DeclareVariable('LFD' , otInteger);
DeclareVariable('Datum' , otDate);
DeclareVariable('Sammler' , otInteger);
DeclareVariable('v_sps' , otInteger);
DeclareVariable('Element' , otInteger);
DeclareVariable('Attribut' , otInteger);
DeclareVariable('WERT' , otInteger);
DeclareVariable('V_TYP' , otInteger);
DeclareVariable('GA' , otInteger);
end;

Called by a timer following statements are executed:
....
with qryMW do
BEGIN
SetVariable ('LFD' , LFD);
SetVariable ('Datum' , Datum);
SetVariable ('Sammler' , Sammler);
SetVariable ('v_sps' , v_sps);
SetVariable ('Element' , Element);
SetVariable ('Attribut' , Attribut);
SetVariable ('WERT' , WERT);
SetVariable ('V_TYP' , V_TYP);
SetVariable ('GA' , GA);
eND;

with qryMW do
BEGIN
SQL.Text:= 'insert into MyTable'
+ ' ( LFD, Datum, Sammler, v_sps, element, attribut, wert, v_typ, ga)'
+ ' valuesLFD,

atum,:Sammler,:v_sps,:element,:Attribut,:wert,:v_typ,:ga)';

ExecuteArray(0,listcount); // listcount has values between 1 and 2000
x:=RowsProcessed;
END;
Commit;
--- ----------------------------------------

If the USER SGA ist steady growing , shared memory of Oracle will decrease and reusult in a
ORA-04031: unable to allocate xxxx bytes of shared memory.

The loss of USER SGA is increasing, when user the statement
alter session set SESSION_CACHED_CURSORS = 20

Any ideas what is going wrong ?

 

[Marco Kalter]

No real ideas yet, but what happens if you close the query after each ExecuteArray?

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

 

[ljohn]

Relating to your suggestion, I modified my application.
It seems that a