Re: Interesting PGA/Memory puzzle (4030 error at 32GB used) - ulimit settings?

Tanel,

I was hoping you'd take a look - that's very helpful and I did consult with the developer earlier and indeed there is a CLOB object used.

I need to go find your oddc script - that looks useful :)

Thanks,
Chris

On Fri, May 18, 2018 at 12:34 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:

> Looks like you have a LOB handle leak or something, plenty of bugs around
> temporary LOB handles etc. A single process should normally not use that
> much memory anyway (if you're not holding some big PL/SQL collection or
> workarea in memory).
>
> SQL> _at_oddc koll
> ORADEBUG DOC COMPONENT | grep -i koll
>
> LOB LOB (koll, kola)
> LOB_Default LOB Default (koll, kole, kokl, koxs)
> LOB_DBLINK LOB DBLink (koll, kokl, kpolobr, kkxlr)
>
>
> It was possible to limit an Oracle process private memory size with ulimit
> in the old days when realfree memory allocation was not used (the regular
> "data segment" was used at OS level, not mmap() calls for memory
> allocation). But the "data seg size" ulimit does not limit mmap() based
> private memory allocation.
>
> I'd say you're hitting some internal heap or memory structure allocation
> limitation that is reported as ORA-4030 at top level. So it's not
> necessarily even an OS memory allocation call that fails, but could be some
> internal Oracle check that doesn't allow growing a memory structure further
> (likely the heap itself or some array pointing to all these leaked LOB
> structures). This heap has 2M x 16kB chunks (slightly under 2 * 1024 *
> 1024). There's a somewhat similar bug 14119856 : ORA-4030 OVER 16GB PGA IS
> ALLOCATED EVEN IF PGA_AGGREGATE_TARGET SET 20GB.
>
> Tanel.
>
> On Fri, May 18, 2018 at 11:53 AM, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> Env: 12.1.0.2 on Linux 7.4 64-bit
>>
>> So, I've been all over Oracle support and Google-ing this morning trying
>> to determine a root cause for this:
>>
>> Here's the error and its always 32GB (ulimit settings to follow). Oracle
>> doesn't have any support docs for KOLLRSZ.
>>
>> The reason I'm wondering about ulimits kicking in is because its always
>> at 32GB. I'm betting its a memory leak but I'd like to overcome this
>> seeming 32GB barrier.
>>
>> [TOC00001]
>> ORA-04030: out of process memory when trying to allocate 16328 bytes
>> (koh-kghu sessi,kollrsz)
>>
>> [TOC00001-END]
>> [TOC00002]
>> ========= Dump for incident 931836 (ORA 4030) ========
>> [TOC00003]
>> ----- Beginning of Customized Incident Dump(s) -----
>> =======================================
>> TOP 10 MEMORY USES FOR THIS PROCESS
>> ---------------------------------------
>>
>> *** 2018-05-18 12:33:30.917
>> *100% 32 GB, 2087947 chunks: "kollrsz "*
>> * koh-kghu sessi ds=0x7f8047e4f178 dsprt=0x7f804ae4cf60*
>> 0% 9688 KB, 77 chunks: "kllcqas:kllsltba " SQL
>> QERHJ hash-joi ds=0x7f8048f2dc40 dsprt=0x7f804a5ab710
>> 0% 4333 KB, 50 chunks: "permanent memory " SQL
>> kxs-heap-w ds=0x7f804a5ab710 dsprt=0x7f804ae4cf60
>> 0% 2503 KB, 18 chunks: "QERHJ list array " SQL
>> QERHJ hash-joi ds=0x7f804a61dc30 dsprt=0x7f804a5ab710
>> 0% 2432 KB, 7 chunks: "HT buckets " SQL
>> QERHJ hash-joi ds=0x7f804a61dc30 dsprt=0x7f804a5ab710
>> 0% 2056 KB, 2 chunks: "kllcqgf:kllsltba "
>> klcliti:kghds ds=0x7f80491e9e10 dsprt=0x7f804f9a09e0
>> 0% 1537 KB, 56 chunks: "QERHJ Bit vector " SQL
>> QERHJ hash-joi ds=0x7f8048f2dc40 dsprt=0x7f804a5ab710
>> 0% 1381 KB, 66 chunks: "free memory " SQL
>> QERHJ hash-joi ds=0x7f804a61dc30 dsprt=0x7f804a5ab710
>> 0% 1213 KB, 27 chunks: "free memory "
>> top call heap ds=0x7f804f9a1be0 dsprt=(nil)
>> 0% 1046 KB, 73 chunks: "free memory "
>> top uga heap ds=0x7f804f9a1e00 dsprt=(nil)
>>
>>
>>
>> I'm wondering if there's a parameter at the OS layer limiting the session
>> to 32GB
>>
>> ulimit -a
>> -----------------------------------------
>> core file size (blocks, -c) 0
>> data seg size (kbytes, -d) unlimited
>> scheduling priority (-e) 0
>> file size (blocks, -f) unlimited
>> pending signals (-i) 16506448
>> max locked memory (kbytes, -l) 64
>> max memory size (kbytes, -m) unlimited
>> open files (-n) 131072
>> pipe size (512 bytes, -p) 8
>> POSIX message queues (bytes, -q) 819200
>> real-time priority (-r) 0
>> stack size (kbytes, -s) 32768
>> cpu time (seconds, -t) unlimited
>> max user processes (-u) 131072
>> virtual memory (kbytes, -v) unlimited
>> file locks (-x) unlimited
>>
>> PGA Settings are as follows on this Data Warehouse:
>>
>> NAME TYPE VALUE
>> ------------------------------------ ----------- ------------
>> pga_aggregate_limit big integer 128G
>> pga_aggregate_target big integer 8G
>>
>>
>>
>

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