Hi Sayan,
"kolaslAssign" is related to LOB handling, which also makes sense based on your
custom function name func_collection_to_clob().
Components in library GENERIC:
--------------------------
LOB LOB (koll, kola) <<<<<<< here we go
LOB_Refcount LOB Refcount (kolr)
LOB_Default LOB Default (kole, kokl, koxs, kokla, koklm,
koklv)
We would need the call stack to drill down further more. What kind of OS are
you running on? I recently published a DTrace script to intercept the
memory allocation and dump the corresponding data: http://tinyurl.com/gov5cch
If you are running on Linux you can adapt the DTrace script to GDB and do the
same with it.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Sayan Malakshinov <xt.and.r@xxxxxxxxx> hat am 24. Februar 2016 um 21:20--
geschrieben:
Hi List,
Who knows what is it:
HEAP_NAME = kolaslAssign, Name=permanent memory, Category=Other?
We have a query like this:
select
...
, func_collection_to_clob((select cast(collect(distinct ...) as
t_collection) from ...)
...
from ...
Each subsequent execution becomes slower and slower and consumes more and
more memory without freeing memory.
v$process_memory_detail shows that almost all memory was consumed by
kolaslAssign:
SELECT * FROM v$process_memory_detail ORDER BY pid, bytes DESC;
PID SERIAL# CATEGORY NAME HEAP_NAME
BYTES ALLOCATION_COUNT HEAP_DESCRIPTOR PARENT_HEAP_DESC
CON_ID
---------- ---------- --------------- --------------------------
--------------- ---------- ---------------- ---------------- ----------------
----------
107 74 Other permanent memory
kolaslAssign 1598033680 579558 00000000D3030C88 0000000046140588
0
107 74 Other kolaslAssign koh dur
heap d 74182792 386372 0000000046140588 000000001E3AE648
0
107 74 Other kolraloc-1 kolr heap
ds i 15812968 193189 0000000018D58460 000000001E3AE648
0
107 74 Other kolrarfc:lobloc_kolrhte kolr heap
ds i 12385440 193186 0000000018D58460 000000001E3AE648
0
107 74 Other free memory koh dur
heap d 6971160 48327 0000000046140588 000000001E3AE648
0
107 74 Other free memory session
heap 6464728 25737 000000001E3AE648 00000000232831A0
0
107 74 Other kgmtimob: self koh-kghu
sessi 1160336 72 000000001E41F660 000000001E3AE648
0
107 74 Other permanent memory pga heap
314520 33 000000002327CFE0 00
0
107 74 Other kxsFrame4kPage session
heap 224208 54 000000001E3AE648 00000000232831A0
0
107 74 SQL permanent memory kxs-heap-w
201416 41 000000001E3E0418 000000001E3AE648
0
107 74 Other kolllupg koh-kghu
call 179608 11 000000001E3A8968 000000002327CFE0
0
...
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org