memory leak

[Jorgen Bosman]

Hi, we are using this wonderful package, but we are facing a memory leak problem that can easily be reproduced by the following code:

DECLARE
        ls_chunk    VARCHAR2(4000):= '{"a":{"b":"c"}}';
        lo_json     JSON;
BEGIN
   while(true) loop
       lo_json:=JSON(ls_chunk);
   END LOOP;
END;
/

Runs this and monitor the PGA usage, it keeps on rising rapidly until memory is exausted. I tried this on a 10.2.0.5 database and on a 11.2.0.3 database and both have the same result.

[James Sumners]

So an infinite loop uses infinite memory?

[Jorgen Bosman]

basically yes, could also be an Oracle bug :-S
When I try the same with  ls_chunk    VARCHAR2(4000):= '{"a":"b"}';, then there is no memory leak!

[Jonas Krogsboell]

You are correct that there are a memory leak - the problem is that I don't know how to fix it.
The issue can be reproduced without pl/json by the following code:

create or replace
type MemObject as Object (
  var varchar2(4000),
  tt  anydata,
  constructor function memobject(ll varchar2) return self as result,
  constructor function memobject return self as result
);
/
create or replace
TYPE BODY MEMOBJECT AS
  constructor function memobject return self as result as
  begin
    return;
  end;

  constructor function memobject(ll varchar2) return self as result as
    inner_obj MemObject := MemObject();
  begin
    self.var := ll;
    inner_obj.var := ll;
    self.tt := sys.anydata.convertobject(inner_obj);
    return;
  end;
END;
/
set serveroutput on;
DECLARE
  ls_chunk VARCHAR2(4000):= '{"a":{"b":"c"}}';
  lo_mem   MemObject;
  v_use    number;
  it       number := 0;
BEGIN
    while(it < 1000) loop
      --lo_json:=JSON(ls_chunk);
      lo_mem := MemObject(ls_chunk);
      if(it mod 100 = 0) then
        --DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
        select value into v_use from V$PGASTAT where name = 'total PGA inuse';
        dbms_output.put_line('it('||it||'): '||v_use);
      end if;
      it := it + 1;
    END LOOP;
END;
/

If it is a big problem in your application maybe DBMS_SESSION.FREE_UNUSED_USER_MEMORY will help.

It might be an issue with the PL/SQL garbage collector. Gary Meyers has written a little blog post about a similar issue: http://blog.sydoracle.com/2005/12/plsql-garbage-collection.html.

If anyone know how to fix this, then please speak up.

/Jonas

[Jorgen Bosman]

Thanks a lot for looking into this problem! Adding the DBMS_SESSION.FREE_UNUSED_USER_MEMORY does not help one bit…
Thanks to your little test case, I will log a SR with Oracle and we'll see what they say about this…

[Jorgen Bosman]

Oracle has reproduced the problem and has logged a bug for it :-)

[Jorgen Bosman]

Unfortunately, Oracle does not think this is a bug, but intended behaviour :-(

They have tested with 11.2.0.3.0 LINUX, which produced similar
result as those of previously reported test.

it(0): 95105024
it(10000): 95661056
it(20000): 96455680
it(30000): 97250304
it(40000): 98044928
it(50000): 98838528
…
it(950000): 170290176
it(960000): 171083776
it(970000): 171877376
it(980000): 172672000
it(990000): 173465600
it(1000000): 174259200

PL/SQL procedure successfully completed.

VALUE

94895104

Following the result of the tests:

As in the other test memory usage grows with each iteration.
Memory is freed once the PL/SQL routine completes.
Heap dump and heap.awk report aslo do not reflect any evidence of memory
leaks.

Based on the results of internal tests with the test case provided
there is no evidence of an Oracle bug related to memory leaks.

The memory growth is normal and expected behavior.

During program execution the test case repeatedly instantiates
an instance of an object type in a loop for N interations.
This result in PGA memory allocations for each new instance of
the object hence the continuous memory growth reflect in the
test case output.

Once the PL/SQL block completes the PGA memory is released and
a query of V$PGASTAT in the same session reflects the memory
has been released.

The customer's application as described in the customer response
to the question posed is of the same nature as that reflected in
the test case.

The problem application is initiated by a database job queue.
Once the application is started it executes an infinite loop
in which a webservice is called to get and an json object.
This results in continuous PGA allocation as new instances
of objects within the infinite loop. Since the application never
exits end of call is never reached. PGA memory for the object
instances is never released.

Memory allocations for object type and collections can have one
of 2 durations 1) call duration and 2) session duration. The
default duration for PL/SQL is call duration.

The resolution to the continuous PGA memory growth of the application
would require a modification to the application.

That modification be such that the portion of the application which runs in
an infinite loop calls the another procedure to process one instance of the
object payload and returns to calling procedure so the call duration
completes. This should result in any PGA allocations being released.

[James Sumners]

I hate to side with Oracle on anything, but that's the long version of what I initially said.

[RJ Kirkland]

A possible solution might be a .reset() method that clears the current entries from a given json object so that it can be reused as opposed to creating a new instance each iteration.
Fundamentally, the infinite loop is not the issue as you could crash a server under high load without the infinite loop. The issue is that the garbage collection is only run on the exit of a named procedure, so if the code is finished manipulating a given instance of an object but is not ready to return, perhaps object re-use would alleviate the concern.