Re: ORA-04031 in spite of enough free memory

The v$shared_pool_reserved.REQUEST_MISSES & REQUEST_FAILURES show that there were cases where the suitable freelists were empty *and* even after flushing any unpinned recreatable chunks from the reserved area, a large enough chunk of contiguous free memory wasn't available.

_at_kghlu.sql RESERVED MISSES & FREE UNPIN UNSUCCESS confirm it too.

So, despite the heapdump showing plenty of free chunks, your session failed to allocate one. Some possible scenarios:

*1) *There was a spike of shared pool usage for Securefile space management "dispensers" - for example when updating a heavily partitioned securefile table (every segment needs its own dispenser). By the time you hit the error and heapdump kicked in, the usage had dropped (either due to your own call "rolling back" thanks to the error) or due to some other activity ending at the same time

*2) *There actually was enough suitable free space in shared pool, but due to some bug Oracle didn't get to it and bailed out (after flushing lots of small chunks)

As you're not running on a big NUMA system with multiple shared pool subpools, I think #2 is less likely.

This kind of an allocation (50kB+ that apparently isn't broken down to 4kB chunks like is the case with many library cache objects) if one remaining valid reason for increasing the shared_pool_reserved_size (or _shared_pool_reserved_pct) if you anticipate lots of partitions + securefile dispenser allocations to be used. The default is 5% of shared pool though, so if you just increase the shared pool to production size, the reserved area would go up as well (and if you don't have a ridiculous amount of concurrently modified securefile segments in partitioned tables, you should be good).

As a side-note, back in 9i days one would need to do this for session v$parameter allocations if anticipated lots of sessions - as each session allocated a ~28kB contiguous chunk from shared pool for its v$parameter values. Nowadays these parameter table allocations are split to ~2kB chunks, so there's no fragmentation issue. The same with most common library cache objects (cursors, plans, even PL/SQL objects now) - their allocations are split & "standardized" to max 4kB chunks. So much easier to find 10 x 4kB chunk of free memory from shared pool than 1 x 40kB chunk of contiguous memory.

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

On Tue, May 28, 2019 at 4:10 PM Noveljic Nenad <nenad.noveljic_at_vontobel.com>
wrote:



> Hi Tanel,


>

>

>

> I embedded my inputs below.

>

>

>

> “How many shared pool sub-pools do you have? The heapdump analyzer output

> indicates that just one, but confirming to be sure.”

>

> => Yes, it's only 1.

>

>

>

> “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" :-)”

>

> => The script might have been simple to implement, but the idea behind is

> brilliant. It's such a great tool - made by the super-geek for geeks.

>

>

>

> “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).”

>

> => I was, obviously, mistaken what "freeable" means - mea culpa!

>

>

>

> “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).”

>

> => That's what I thought as well.

>

>

>

> SQL> SELECT * FROM v$shared_pool_reserved

> ;

>

>

>

>

> FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE

>

> ---------- ------------- ---------- ------------- ---------- -------------

>

> USED_COUNT MAX_USED_SIZE   REQUESTS REQUEST_MISSES LAST_MISS_SIZE

> MAX_MISS_SIZE

>

> ---------- ------------- ---------- -------------- --------------

> -------------

>

> REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD

> ABORTED_REQUESTS

>

> ---------------- ----------------- -------------------------

> ----------------

>

> LAST_ABORTED_SIZE     CON_ID

>

> ----------------- ----------

>

>   52890512    91823.8056        140       2096960   20882224    36253.8611

>

>        436         52864     433428            116          52864

> 52864

>

>               49             52864

> 2147483647                0

>

>                 0          0

>

>

>


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