RE: v$process_memory_detail: Category=Other, HEAP_NAME = kolaslAssign

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 24 Feb 2016 23:31:36 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282C4AC5_at_EXMBX01.thus.corp>

It looks like you're leaking both pointers and data. There's one place in your code that is the "obvious guess" for the problem:

res := substr(res,2)

Based on previous (old) experience I'd try introducing a second lob here and doing:

res2 := substr(res,2)
return res2;

I'd then do something similar with the two places where you've got

   res := res || tmp

Something like:

   res2 := res || tmp;
   res := res2;

Essentially avoid assigning the clob to itself.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Sayan Malakshinov [xt.and.r_at_gmail.com] Sent: 24 February 2016 21:28
To: Stefan Koehler
Cc: ORACLE-L
Subject: Re: v$process_memory_detail: Category=Other, HEAP_NAME = kolaslAssign

Hi Stefan,

Thanks for confirming my guess!
Unfortunately, this issue is on windows server. And I can't to create a test case that will have the same behaviour like buggy query, because my test with the same function shows that memory is freeing normally.

Source code is very simple:

create type t_objstrset as table of varchar2(4000);
/

create or replace function func_collection_to_clob (

   p_collection t_objstrset
  ,p_delim varchar2:=','
  )
return clob
as

   res clob;
   tmp varchar2(32767);
   elm varchar2(4000);
begin

   if p_collection is null then

      res:=null;
   else

      for i in 1..p_collection.count loop
         elm:=p_collection(i);
         if length(tmp)+length(elm)>=32767 then
            res:=res||tmp;
            tmp:=null;
         end if;
         tmp:=tmp||p_delim||elm;
      end loop;
      res:=res||tmp;

   end if;
   return substr(res,2);
end;
/

I've tried also to create lobs with dbms_lob.createtemporary(res ,true, dbms_lob.call); But without success:

create or replace function func_collection_to_clob (

   p_collection t_objstrset
  ,p_delim varchar2:=','
  )
return clob
as

   res clob;
   tmp varchar2(32767);
   elm varchar2(4000);
begin

   if p_collection is null then

      res:=null;
   else

      dbms_lob.createtemporary(res ,true, dbms_lob.call);

      for i in 1..p_collection.count loop
         elm:=p_collection(i);
         if length(tmp)+length(elm)>=32767 then
            res:=res||tmp;
            tmp:=null;
         end if;
         tmp:=tmp||p_delim||elm;
      end loop;
      res:=res||tmp;

   end if;
   res:=substr(res,2);
   return res;
end;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 25 2016 - 00:31:36 CET