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.
@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@xxxxxxxxxxxx>
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
