I'm currently investigating a 19.17 Standard Edition where memory usage keeps growing. I suspect temporary lobs that aren't freed properly.
PL/SQL web application using htp.p with ORDS as server. The PL/SQL extensively uses lobs, almost all the time with cache=true and default session duration (which I think they should use call duration instead.)
But I notice in v$temporary_lobs that these ORDS sessions typically have slowly increasing ABSTRACT_LOBS counts - up to maybe 120.
The code often creates a temp lob, then assigns to it a from an xmltype. I suspected this might make two lobs (one of them the abstract) and this might cause only one to be cleaned up.
I tried reproducing it:
set serveroutput on size unlimited;
declare
procedure lobout (t varchar2 default 'none') is
cl pls_integer;
al pls_integer;
begin
select cache_lobs, abstract_lobs into cl, al from v$temporary_lobs where sid = sys_context('USERENV','SID');
dbms_output.put_line(t||' : '||cl||' - '||al);
end;
procedure do (outc out clob) is
c2 clob;
begin
lobout('in do');
dbms_lob.createtemporary(c2,true);
lobout('in do');
select xmlserialize(content xmltype('<a>b2</a>') as clob) into c2 from dual;
lobout('in do');
outc := c2;
lobout('in do');
end;
begin
lobout;
declare
c clob;
begin
lobout('outer');
dbms_lob.createtemporary(c,true);
lobout('outer');
select xmltype('<a>b</a>').getclobval() into c from dual;
lobout('outer');
dbms_output.put_line(c);
lobout('outer');
do(c);
lobout('outer');
end;
lobout;
end;
/
I get this output (before running, my session had 15 cached lobs, 0 abstract lobs):
none : 15 - 0
outer : 15 - 0
outer : 16 - 0
outer : 15 - 1
<a>b</a>
outer : 15 - 1
in do : 15 - 1
in do : 16 - 1
in do : 15 - 2
in do : 15 - 2
outer : 15 - 1
none : 15 - 1
I had expected the last line to show "15 - 0", so I thought I'd caught the culprit, but after finishing the block, my session still has 15 cached lobs and 0 abstract lobs, so the abstract lob is cleaned after all.
So when are abstract lobs cleaned out? In what circumstances can they keep hanging around, so a pooled ORDS web session slowly keeps growing number of abstract lobs?
Thanks for any ideas that I can try investigating. (For case specific help I'll go to support.)