In a recent DB hanging caused by ORA-04031, we saw huge allocations of KKSSP, and session dump with:
ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","unknown object","KKSSP^9876","kgllk").
Even the smallest memory chunk of 32 bytes is no more procurable:
ORA-04031: unable to allocate 32 bytes of shared memory
The DB is configured with 20GB shared pool, 300GB buffer cache, running as dedicated server with 6000 concurrent login sessions in Oracle 11.2.0.3.0.
Additionally, Automatic Shared Memory Management(ASMM) disabled, cursor_sharing being exact
("_memory_imm_mode_without_autosga" set as FALSE, that is, disallow immediate mode without sga/memory target.
See Oracle Doc ID 1269139.1: SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET/SGA_TARGET=0)).
The whole KKSSP consumption amounts to 4GB just before crash, so average per session 700KB.
A heapdump of shared pool shows that all KKSSP are listed with comment like:
Chunk 70000009fad14d0 sz= 2136 freeable "KKSSP^2566 " ds=7000000A5DA1798
where 2566 is SID of login session. So KKSSP is per session allocated and is a session specific area (subheap) in shared pool.
This contradicts a common believe of shared pool being shared (at least in majority) across all sessions because they are not negligible memory consummers.
Pick the ds marked address(descriptor), and dig further by a KKSSP address dump:
ORADEBUG DUMP HEAPDUMP_ADDR 2 0x7000000A5DA1798
then aggregate by Tanel's Heapdump Analyzer:
Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason
---------- ------- ------------ ----------------- --------------- -----------------
188160 735 256 , KKSSP^2566, freeable, kgllk
181504 709 256 , KKSSP^2566, freeable, kglpn
56320 220 256 , KKSSP^2566, freeable, KQR ENQ
28896 516 56 , KKSSP^2566, freeable, kglseshtSegs
12312 1 12312 , KKSSP^2566, freeable, kglseshtTable
The above table shows that the top 3 memory consumers are kgllk, kglpn, KQR ENQ. Each single one of them needs 256 Bytes. More than half of memory is allocated to kgllk and kglpn since the application is coded in PL/SQL packages and types, which requires kgllk and kglpn during each call to keep them stateful.
To inspect kgllk and kglpn touched objects, following query will list them:
select s.sid, username, logon_time
,(select kglnaobj||'('||kglobtyd||')' from x$kglob v
where kglhdadr = v.object_handle and rownum=1) kobj_name
,v.*
from v$libcache_locks v, v$session s
where holding_session = s.saddr
and s.sid = 2566;
The above query can also help to debug: library cache pin and library cache lock.
(select * from x$kglob where kglhdadr in
(select p1raw from v$session where sid = :blocked_session);
Instead of Heapdump, a direct way to get the memory consumption for a session is:
select count(*), sum(ksmchsiz) from x$ksmsp where ksmchcom='KKSSP^2566';
Following select can also give the address for KKSSP address Heapdump:
select ksmchpar from x$ksmsp where ksmchcom='KKSSP^2566' and ksmchcls = 'recr';
(The output looks like 07000000A5DA1798, replace first 0 with 0X to dump:
oradebug dump heapdump_addr 2 0X7000000A5DA1798)
By the way, the above dump probably finds the lost "KQR ENQ" discussed in Page169 of Book:
Oracle Core: Essential Internals for DBAs and Developers
... when I ran the same query against an instance of 11.2.0.2 there was no entry for KQR ENQ ...
We can see "KQR ENQ" now moved to KKSSP in Oracle 11.2.0.3.0.
MOS: ORA-4031 Or Excessive Memory Consumption On KKSSP Due To Parse Failures (Doc ID 2369127.1)
said:
KKSSP is just a type of internal memory allocation related to child cursors.
I also noticed that high usage of kgllk and kglpin was allied with heavy contention on KOKC latch(kokc descriptor allocation latch), which is responsible for pinning, unpinning and freeing objects. KOKC is a single latch without children, thus a single point of contention.
