ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT MAX(XX) FROM...","SQLA","tmp")
under shared pool memory distribution of:
| Top 5 Areas Name | Subpool_1 | Subpool_2 | Subpool_3 | Subpool_4 | Subpool_5 | Subpool_6 | Subpool_7 | Sum |
| KKSSP | 617'693 | 563'340 | 590'296 | 590'798 | 577'532 | 643'504 | 577'237 | 4'160'404 |
| db_block_hash_buckets | 529'039 | 535'429 | 529'039 | 539'525 | 535'429 | 529'044 | 535'429 | 3'732'935 |
| KGLH0 | 554'532 | 485'312 | 464'006 | 353'634 | 450'528 | 346'045 | 332'455 | 2'986'514 |
| SQLA | 1'110 | 588'943 | 565'317 | 185'664 | 574'008 | 155'498 | 272'452 | 2'342'994 |
| free memory | 306'562 | 358'029 | 353'146 | 306'659 | 342'194 | 325'386 | 304'535 | 2'296'513 |
(see Blog: Shared Pool - Memory Allocation - unbalanced)
an intuitively skeptical question is:
Why do I get ORA-04031 even though there is plenty of free memory (> 10%) ?
One unsubstantial reply is mostly: memory fragmentation (or memory leak).
This Blog is trying to disperse such fashionable pretext.
A simple way to dispute it is to make a heapdump and you will see FREE LISTS starting with size=32 in Bucket 0:
FREE LISTS:
Bucket 0 size=32
so the minimum memory chunk size in shared pool is 32 Byte, any free memory chunk is at least 32 Byte.
But above error says:
unable to allocate 32 bytes
evidently there is truly no more free memory.
So where is the secrecy behind the controversial information ?
To understand it, firstly we will look at what free memory is in different aspect, then expose the memory allocation of certain popular areas (subheaps), and finally debate further on this theme.
1. Free Memory
Take a DB of shared_pool_size = 1408M with two Subpools, one direct way to get free memory statistics is:
select round(bytes/1024/1024) mb from v$sgastat where pool='shared pool' and name = 'free memory';
returns:
298
or on its internal x$ table:
select KSMDSIDX, round(ksmsslen/1024/1024) SGASTAT_MB from x$ksmss where ksmssnam ='free memory';
returns:
KSMDSIDX SGASTAT_MB
--------- ----------
0 208
1 42
2 48
A heapdump shows:
HEAP DUMP heap name="sga heap" desc=700000000000198
reserved granule count 13 (granule size 16777216)
RESERVED EXTENTS
HEAP DUMP heap name="sga heap(1,0)" desc=700000000052a48
FREE LISTS
Total free space = 9970088
RESERVED FREE LISTS
Total reserved free space = 29473232
HEAP DUMP heap name="sga heap(2,0)" desc=70000000005c310
FREE LISTS
Total free space = 12790056
RESERVED FREE LISTS
Total reserved free space = 31900768
ksmdsidx 0 is correlated well with heapdump, but not free memory in both subpools.
KSMDSIDX SGASTAT_MB HEAP_DUMP Delta
-------- ---------- ------------------------------------------ -----
0 208 round(16777216*13/1024/1024) = 208 0
1 42 round((9970088+29473232)/1024/1024) = 38 4
2 48 round((12790056+31900768)/1024/1024) = 43 5
So now the question is why v$sgastat (and respective x$ksmss) reports 9 MB (4+5) free memory than heapdump ?
Let's try to dig it out.
2. Subheaps: KGLH0, KGLS, PLDIA, SQLA, SQLK, SQLP
These subheaps are chosen since they are mostly allocated in a chunk size of 4096, and all together play a prevalent role in memory consumption (On the top of Page 157 of Book: Expert Oracle Database Architecture, there is one line:
The shared pool is characterized by lots of small (generally 4KB or less) chunks of memory).
At first select them from v$sgastat:
select name, round(bytes/1024/1024) mb
from v$sgastat
where pool='shared pool'
and name in ('KGLH0', 'KGLS', 'PLDIA', 'SQLA', 'SQLK', 'SQLP')
order by mb desc;
NAME MB
-----------
SQLA 219
KGLH0 125
KGLS 43
PLDIA 9
SQLP 5
SQLK 0
another query on x$ksmsp displays more details about memory usage:
select substr(ksmchcom, 1, 5) name
,round(sum(ksmchsiz)/1024/1024) mb
,count(ksmchsiz) cnt
,round(avg(ksmchsiz)) avg
,min(ksmchsiz) min
,max(ksmchsiz) max
from x$ksmsp v
where substr(ksmchcom, 1, 5) in ('KGLH0', 'KGLS^', 'PLDIA', 'SQLA^', 'SQLK^', 'SQLP^')
group by substr(ksmchcom, 1, 5)
order by mb desc;
NAME MB CNT AVG MIN MAX
---------------------------------
SQLA^ 220 56269 4099 4096 28664
KGLH0 126 32261 4096 4096 4640
KGLS^ 43 11005 4096 4096 4128
PLDIA 9 2195 4096 4096 4112
SQLP^ 5 1209 4168 4096 5888
SQLK^ 0 19 4096 4096 4096
Look again on the same heapdump, extract all lines containing one special comment, say,
KGLH0^d020e92f
Chunk 7000000a1cd3578 sz= 4096 recreate "KGLH0^d020e92f " latch=0
Chunk 7000000a1d9c4d8 sz= 4096 recreate "KGLH0^d020e92f " latch=0
Chunk 7000000a1efce68 sz= 4096 freeable "KGLH0^d020e92f " ds=7000000a1d9c450
Chunk 7000000a7b8f880 sz= 4096 freeable "KGLH0^d020e92f " ds=7000000a1d9c450
Chunk 7000000a7f07588 sz= 4096 freeable "KGLH0^d020e92f " ds=7000000a1d9c450
(see MOS: Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1) about Chunk types
Chunk types:
Normal (freeable) chunks - These chunks are allocated in such a way that the user can explicitly free the chunk once they have finished with the memory.
Free chunks - These chunks are free and available for reuse should a request come into the pool for this chunk size or smaller.
Recreatable chunks - This is a special form of "freeable" memory. These chunks are placed on an LRU list when they are unpinned. If memory is needed, we go to the LRU list and free "recreatable" memory that hasn't been used for a while.
Permanent chunks - These chunks can be allocated in different ways. Some chunks are allocated and will remain in use for the "life" of the instance. Some "permanent" chunks are allocated but can be used over and over again internally as they are available.)
and then make an addr dump of KGLH0^d020e92f:
Processing Oradebug command 'dump heapdump_addr 1 0X7000000A1D9C450'
HEAP DUMP heap name="KGLH0^d020e92f" desc=7000000a1d9c450
EXTENT 0 addr=7000000a1efce80
Chunk 7000000a1efce90 sz= 1384 perm "perm " alo=600
Chunk 7000000a1efd3f8 sz= 152 freeable "kgltbtab "
Chunk 7000000a1efd490 sz= 696 freeable "policy chain "
Chunk 7000000a1efd748 sz= 144 freeable "context chain "
...
EXTENT 1 addr=7000000a7f075a0
Chunk 7000000a7f075b0 sz= 376 free " "
...
EXTENT 2 addr=7000000a7b8f898
Chunk 7000000a7b8f8a8 sz= 1760 perm "perm " alo=1760
Chunk 7000000a7b8ff88 sz= 416 free " "
...
EXTENT 3 addr=7000000a1cd35a8
Chunk 7000000a1cd35b8 sz= 80 perm "perm " alo=80
Chunk 7000000a1cd3608 sz= 3296 perm "perm " alo=3296
Chunk 7000000a1cd42e8 sz= 48 free " "
...
Total heap size = 16200
FREE LISTS:
Bucket 0 size=0
Chunk 7000000a7b8ff88 sz= 416 free " "
Chunk 7000000a7f075b0 sz= 376 free " "
Chunk 7000000a1cd42e8 sz= 48 free " "
Chunk 7000000a1cd35d8 sz= 0 kghdsx
Total free space = 840
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
Chunk 7000000a1efce90 sz= 1384 perm "perm " alo=600
...
Permanent space = 6520
so total used memory is 16200 (including 840 free space, and 6520 Permanent space), however the allocated space is 5*4096 = 20480, so 20480 - 16200 = 4280 over allocation.
addr dump of "KGLH0^d020e92f" shows 3 free Chunks with size 416, 376, and 48.
FREE LISTS:
Bucket 0 size=0
Chunk 7000000a7b8ff88 sz= 416 free " "
Chunk 7000000a7f075b0 sz= 376 free " "
Chunk 7000000a1cd42e8 sz= 48 free " "
Chunk 7000000a1cd35d8 sz= 0 kghdsx
Total free space = 840
HEAP DUMP heap name="sga heap(1,0)" desc=700000000052a48
FREE LISTS:
Bucket 42 size=368
Bucket 43 size=376
Bucket 44 size=384
Bucket 45 size=392
Bucket 46 size=400
Bucket 47 size=408
Bucket 48 size=416
Bucket 49 size=424
HEAP DUMP heap name="sga heap(2,0)" desc=70000000005c310
FREE LISTS:
Bucket 42 size=368
Bucket 43 size=376
Bucket 44 size=384
Bucket 45 size=392
Bucket 46 size=400
Bucket 47 size=408
Chunk 7000000a3e8c3d8 sz= 408 free " "
Bucket 48 size=416
Bucket 49 size=424
so the FREE LISTS in addr dump of "KGLH0^d020e92f" are counted in v$sgastat, but not counted in free memory of heapdump, that probably explains why v$sgastat displays more free memory than heapdump. Since they are not listed in any FREE LISTS, they can not be allocated to any later memory demand till their bound parental chunks returned to LRU LIST (UNPINNED RECREATABLE CHUNKS). I guess that Oracle takes such an approach to reduce memory fragmentation (and coalescing) in hoping that each allocated chunk is fully used in these subheaps. In summary, free memory in v$sgastat probably displays more than allocable free memory since it includes un-allocable free memory inside the allocated chunks(chunk-intra free memory).
3. Discussion
3.1. session_cached_cursors: KGLH0 and SQLA
Each Oracle object, whatever SQL cursor(both parent and child), Table, PL/SQL, has one KGLH0 memory allocation of multiple 4K. When session_cached_cursors is set, repeated parse calls (more than 3 times) of the same SQL (including recursive SQL) or PL/SQL statement by any session connected to DB are candidate for addition to session cursor cache (Understanding Shared Pool Memory Structures ).
Cursor in this cache is "partially pinned", that means, KGLH0(heap 0) is pinned, but not SQLA (heap 6). So when session_cached_cursors is set high, there will be potentially huge KGLH0 pinned, but not SQLA.
As a certain rule of thumb, SQLA should be proportional to KGLH0, otherwise they are out of originally designed range.
In fact, the DB which throws ORA-04031 has set session_cached_cursors = 600 (Oracle Default=50).
(see Blog: Shared Pool - Memory Allocation - unbalanced) And the memory statistics shows that KGLH0 is the third top memory consumer in all subpools, and SQLA in subpool_1 is extremely problematic. With 6000 concurrently connected sessions and session_cached_cursors = 600, there will be 3'600'000 pinned KGLH0 in the extreme case (this pure math number will not appear since majority of cursors are the same stored in shared_pool).
Continuing with our experiment, a library_cache dump("level 16") confirms that there is always one KGLH0 for each cursor (parent and child), and occasionally one SQLA, so more KGLH0 subheaps than SQLA subheaps. Obviously this is already an optimization since normally SQLA requires more memory than its corresponding KGLH0.
Library_cache dump also shows certain information about memory usage, for example, a particular SQLA:
Block: #='0' name=KGLH0^580dee70 pins=0 Change=NONE
Heap=70000521f4f8300 Pointer=70000521ad6f918 Extent=70000521ad6f7f8 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=20.015625 Size=23.859375 LoadTime=28198292040
Block: #='6' name=SQLA^580dee70 pins=0 Change=NONE
Heap=7000000a0117ec8 Pointer=7000000a49c0040 Extent=7000000a49bf3e8 Flags=I/-/-/A/-/E
FreedLocation=0 Alloc=23.171875 Size=23.742188 LoadTime=0
dump heapdump_addr 1 0X7000000a0117ec8
Total heap size = 24312
Total free space = 504
Permanent space = 80
and then try to relate them together:
Total heap size = Size = 23.742188*1024 = 24312
Total free space + Permanent space = 23.742188*1024 - 23.171875*1024 = 584 = 504 + 80
In the dump file when memory of shard_pool being under pressure (ORA-04031), probably there are certain offending cursors with noticeable KGLH0 or SQLA memory Size, for example: KGLH0^ac7e9a16 consumes about 131'343'512 Bytes.
DataBlocks:
Block: #='0' name=KGLH0^ac7e9a16 pins=0 Change=NONE
Heap=700014eb0a10b30 Pointer=7000150fedd92e0 Extent=7000150fedd9170 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=128010.093750 Size=128265.148438 LoadTime=16455656254
3.2. v$sgastat(x$ksmss) vs. x$ksmsp
In v$sgastat 'shared pool', Bytes for "free memory" statistics is really allocable free memory (top RESERVED EXTENTS, and FREE LISTS, RESERVED FREE LISTS in each subpool) plus un-allocable free memory (overhead) in allocated chunks. And Bytes for each v$sgastat component is the effectively occupied memory (not including overhead). So total memory matches the configured shared_pool_size.
Whereas x$ksmsp reports allocable memory in free chuncks. If one chunk is allocated, it is not counted even it still has free memory. Additionally it reports more details, for example, KGLH0 for each cursor. It also outputs one special row for "permanent memor". But it enumerates less rows than v$sgastat.
In conclusion, this chunk-intra free memory probably irritates such a confusion:
I get ORA-04031 even though there is plenty of free memory (> 10%).
With following query, you can get a rough comparison of both aspects. By only running the subquery_factoring_clause, you can see that above mentioned subheaps:
KGLH0, KGLS, PLDIA, SQLA, SQLK, SQLP
are mostly allocated in 4K, and there are also more KGLH0 than SQLA due to session_cached_cursors.
with sq as
(select substr(ksmchcom, 1, decode((instr(ksmchcom, '^') - 1), -1,
length(ksmchcom), (instr(ksmchcom, '^') - 1))) name
,v.*
from sys.x_ksmsp v)
,ksmsp as
(select name ksmsp_name
,round(sum(ksmchsiz)/1024/1024) ksmsp_mb
,count(ksmchsiz) cnt
,round(avg(ksmchsiz)) avg
,min(ksmchsiz) min
,max(ksmchsiz) max
from sq group by name)
,ksmss as
(select ksmssnam ksmss_name
,round(sum(ksmsslen)/1024/1024) ksmss_mb
from sys.x_ksmss
where (ksmssnam, ksmdsidx) not in (('free memory', 0))
group by ksmssnam)
select ksmss_name
,ksmss_mb
,nvl(ksmss_mb, 0) - nvl(ksmsp.ksmsp_mb, 0) delta_mb
,ksmsp.*
from ksmss full outer join ksmsp
on lower(ksmss.ksmss_name) = lower(ksmsp.ksmsp_name)
where ksmss.ksmss_name in ('KKSSP', 'db_block_hash_buckets', 'KGLH0', 'SQLA', 'free memory')
order by abs(delta_mb) desc nulls last;
KSMSS_NAME KSMSS_MB DELTA_MB KSMSP_NAME KSMSP_MB CNT AVG MIN MAX
---------------------- --------- --------- ------------ --------- ------ ------ ----- -------
db_block_hash_buckets 22 22
free memory 90 15 free memory 75 3938 20034 48 2096960
SQLA 118 -1 SQLA 119 30336 4104 4096 33960
KGLH0 116 -1 KGLH0 117 29871 4111 4096 52560
KKSSP 3 0 KKSSP 3 860 4244 568 12352
