RE: ORA-04031 in spite of enough free memory
Date: Fri, 31 May 2019 18:53:09 -0400
Message-ID: <055701d51803$9e38aaa0$daa9ffe0$_at_rsiz.com>
and (most assuredly not but) as operational avoidance if you have packages that 1) need a big contiguous chunk and 2) are used just a little too infrequently to always stay in, you can pull them in on instance warm start.
Whether you KEEP them or have a heartbeat routine to keep them in, you CAN avoid space management. The question is whether avoiding the space management and possible errors dynamically pulling something in is worth always sitting on the memory.
When memory was expensive and Ebusiness suite had some really big chunks for month close/month open, some shops did instance warmstarts with different packages loaded for a few days and then another to restart for regular business. One RAC that might be one instance restarted and application affinity used to run all the month end/month open (or year end/year open) stuff on that particular instance.
Good luck and enjoy the free gold Tanel gave you! Reminds me why I like oracle-l so much!
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Wednesday, May 29, 2019 2:31 PM
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:
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.
To: Noveljic Nenad
Cc: Tanel Poder; ORACLE-L (oracle-l_at_freelists.org)
Subject: Re: ORA-04031 in spite of enough free 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.”
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jun 01 2019 - 00:53:09 CEST
=> 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