Re: ORA-04031 in spite of enough free memory

Hi Nenad,

Yep, interesting. As the failed allocation size was over 50kB, these allocations can (also) use shared pool reserved area. The heapdump analysis shows that there's plenty of large *R-free* chunks in sub-heap 1 sub-sub-heaps 0 and 3 (12.2 uses only 2 sub-sub-heaps/durations instead of 4).

Looks like the KTSL subheap allocations below are done from duration 0.

 Total_size #Chunks Chunk_size, From_heap, Chunk_type,  Alloc_reason

• ------- ------------ ----------------- -----------------

  ---

  ...
  • 73586688 1392 52864 , sga heap(1,0), freeable, KTSL subheap 20987008 397 52864 , sga heap(1,0), R-freeable, KTSL subheap * 19408560 11390 1704 , sga heap(1,0), freeable, kcbi io desc sl 16789760 20 839488 , sga heap(1,3), R-free,

    12581760 6 2096960 , sga heap(1,0), R-free,

    11036608 11 1003328 , sga heap(1,0), R-free,

How many shared pool sub-pools do you have? The heapdump analyzer output indicates that just one, but confirming to be sure.

Also, can you post the output of:

1. SELECT * FROM v$shared_pool_reserved
2. _at_kghlu.sql <https://github.com/tanelpoder/tpt-oracle/blob/master/kghlu.sql>
3. _at_ksmlru.sql <https://github.com/tanelpoder/tpt-oracle/blob/master/ksmlru.sql>

The above commands/scripts are safe to run (unlike x$ksmsp queries) as they don't hold shared pool latches for a long time. As long as you haven't restarted the instance since the problem happened, "current" output should be fine.

> A little side note: Thank you, Tanel, for providing such a useful script!
>

You're welcome! The "analyzer" word in heapdump_analyzer is actually a bit of an overkill as it's just a simple shell/AWK script that does a group by on the chunk data. But "analyzer" sounds fancier than "summarizer" :-)

> In particular, I'm interested to know what might have triggered the
> resizing decision when there was enough consecutive freeable memory to
> fulfil the request. Further, is there some possibility to trace the
> resizing decisions?
>

Not sure if you meant freeable as an Oracle term here or in general sense, like memory that is already free + memory chunks that can be discarded by others at will (recreatable).

Unlike recreatable memory chunks, Free*able* memory chunks can't be freed by just anyone at will. It's a bit of a simplification, but only the codepath that allocated the freeable chunk, can free it later on. In other words, freeing freeable chunks is a deliberate effort, like "as we don't need this freeable chunk at address 0x12345678 anymore, let's free exactly that chunk" ... as opposed to "let's free whatever is the first recreatable chunk in the LRU list".

But in your heapdump output there was plenty of already-*free* space in "sga heap(1,0)" reserved area anyway (R-free), so your 50kB allocations should have succeeded in theory (assuming that your _shared_pool_reserved_min_alloc is at its default value 4400).

--
Tanel Poder
https://blog.tanelpoder.com/seminar

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