内核参数max_map_count 和 ORA-04030

PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030: (koh-kghu sessi,pmuccst: adt/record) or ORA-04030: (koh-kghucall ,pmucalm coll) Errors (Doc ID 1325100.1)

In this Document
Symptoms
Changes
Cause
Solution
References

APPLIES TO:
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database - Standard Edition - Version 11.1.0.6 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
You are running a PL/SQL package or procedure and are consistently encountering an ORA-4030 when the process uses 4GB. The values for the _PGA_MAX_SIZE and PGA_AGGREGATE_TARGET has been set to values even greater than the 4GB, but yet the same errors persist when 4GB is used.

Errors seen are similar to the following:

ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pmuccst: adt/record)

• OR -

ORA-06500: PL/SQL: storage error
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghucall ,pmucalm coll)
ORA-06512: at line ...
CHANGES
Upgrade to 11.2.0.1 or higher.

CAUSE
The trace file also confirms that the process is limiting at 4GB.

=======================================
TOP 10 MEMORY USES FOR THIS PROCESS

100% 4057 MB, 260558 chunks: "pmuccst: adt/record " PL/SQL
koh-kghu sessi ds=0x2aec85f2b810 dsprt=0x2aec85b66ac0
0% 1091 KB, 22 chunks: "free memory "
top call heap ds=0xa2c2a60 dsprt=(nil)
0% 546 KB, 102 chunks: "free memory "
callheap ds=0xa2c1c18 dsprt=0xa2c2a60
0% 425 KB, 39 chunks: "permanent memory " SQL
sort subheap ds=0x2aec861809c8 dsprt=0x2aec85f5f738
0% 263 KB, 3 chunks: "static frame of inst " PL/SQL
koh-kghu sessi ds=0x2aec85fd2088 dsprt=0x2aec85b66ac0
0% 191 KB, 21 chunks: "permanent memory "
pga heap ds=0xa2bd460 dsprt=(nil)
0% 137 KB, 3 chunks: "recursive addr reg file " PL/SQL
koh-kghu sessi ds=0x2aec85fd2088 dsprt=0x2aec85b66ac0
0% 83 KB, 8 chunks: "permanent memory " SQL
kxs-heap-w ds=0x2aec85fbf068 dsprt=0x2aec85b66ac0
0% 79 KB, 18 chunks: "permanent memory "
session heap ds=0x2aec85b66ac0 dsprt=0xa2c2c80
0% 62 KB, 32 chunks: "free memory "
session heap ds=0x2aec85b66ac0 dsprt=0xa2c2c80

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS

---

PRIVATE HEAP SUMMARY DUMP
4074 MB total: <----- here we see the 4G limit being hit
4072 MB commented, 192 KB permanent
1131 KB free (768 KB in empty extents),
4071 MB, 1 heap: "session heap "

Summary of subheaps at depth 1
4070 MB total:
4069 MB commented, 95 KB permanent
639 KB free (569 KB in empty extents),
4068 MB, 20 heaps: "koh-kghu sessi " 4 KB free held

Summary of subheaps at depth 2
4059 MB total:
4058 MB commented, 150 KB permanent
100 KB free (56 KB in empty extents),
4057 MB, 260558 chunks: "pmuccst: adt/record "

=========================================

Deeper in the trace file we see that the process map has hit a limit of 65536 lines

----- Process Map Dump -----
00400000-0954f000 r-xp 00000000 fd:09 3131376 /$ORACLE_BASE/bin/oracle <-Line 1
0974e000-0a2be000 rwxp 0914e000 fd:09 3131376 /$ORACLE_BASE/bin/oracle
0a2be000-0a304000 rwxp 0a2be000 00:00 0
0c8c9000-0c950000 rwxp 0c8c9000 00:00 0 [heap]
60000000-60001000 r-xs 00000000 00:13 9374560 /dev/shm/ora_DSSP_11698195_0
60001000-70000000 rwxs 00001000 00:13 9374560 /dev/shm/ora_DSSP_11698195_0
70000000-80000000 rwxs 00000000 00:13 9374561 /dev/shm/ora_DSSP_11698195_1
80000000-90000000 rwxs 00000000 00:13 9374565 /dev/shm/ora_DSSP_11730964_0
90000000-a0000000 rwxs 00000000 00:13 9374566 /dev/shm/ora_DSSP_11730964_1
a0000000-b0000000 rwxs 00000000 00:13 9374569 /dev/shm/ora_DSSP_11763733_0
b00

2aed84a4f000-2aed86e5f000 rwxp febe2000 00:11 29160 /dev/zero
7fffea3ca000-7fffea418000 rwxp 7ffffffb1000 00:00 0 [stack]
ffffffffff600000-ffffffffffe00000 ---p 00000000 00:00 0 [vdso] <---End of the map dump at (Line 65537 of the Process Map Dump)
******************* End of process map dump ***********

These errors usually show up because of running out of map entries from the OS.
There are only 65536 memory map entries per process.

Memory allocations shown in the tracefile or error can include (but are not limited to):

"pmucalm coll"
"pmuccst: adt/re" or "pmuccst: adt/record"
"pl/sql vc2"
"permanent memory " SQL
"kkqgAllocEle.n "
SOLUTION
Change the upper limit at either the OS or at the database level:

Change the page count at the OS level:
more /proc/sys/vm/max_map_count
sysctl -w vm.max_map_count=262144 (for example)
**Please note the above changes will revert upon host reboot unless the changes are permanently made in the /etc/sysctl.conf file **

Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database.
For versions 11.2.0.4 and lower:
_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144

For 12.1 and higher:
_use_realfree_heap=TRUE
_realfree_heap_pagesize = 262144

The default realfree allocator pagesize is 64 kB (65536), so 64K entries take up 4GB. With 256kB (262144) pages, the limit goes to 16GB.

With Multi-Tenant Databases, you can change the database parameters in spfile in root (CDB); the value of a particular parameter in the root (CDB) is inherited
by PDBs.