PLSQL Procedure 引起 ORA-04030

您运行了一个 PL/SQL package 或者 procedure，当进程使用了 4GB 的内存时总是会遇到 ORA-4030 异常。 _PGA_MAX_SIZE 和 PGA_AGGREGATE_TARGET 已经设置到一个大于 4GB 的值了，但是当 4GB 内存被使用时相同的错误仍然持续报出。

错误看起来类似于：

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)

- 或者 -

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

通过 trace 文件也能够确认进程被限制在 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:                                           
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 "

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


在 trace 文件中更深层的信息，我们看到 process map 遇到了 65536 行的限制

----- Process Map Dump -----
00400000-0954f000 r-xp 00000000 fd:09 3131376 /e00/oracle/prod/v11.2.0/bin/oracle 0974e000-0a2be000 rwxp 0914e000 fd:09 3131376 /e00/oracle/prod/v11.2.0/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 process map dump ***********

从 OS 角度来看，这些错误的出现通常是因为 map 条目耗尽造成的。
每个进程仅仅有 65536 个 memory map 条目。

在 trace 中内存的分配或者错误可能包括（但不仅限于）：

• "pmucalm coll"
• "pmuccst: adt/re" or "pmuccst: adt/record"
• "pl/sql vc2"
• "permanent memory " SQL
• "kkqgAllocEle.n "

解决方案

在 OS 或者在数据库层面来改变上限：

• 在 OS 层面来改变页数：
  
  more /proc/sys/vm/max_map_count
  sysctl -w vm.max_map_count=200000 (例子)
• 在数据库的初始化参数文件中设置下面的参数，来调整 realfree heap pagesize。然后重启数据库。
  
  _use_realfree_heap=TRUE
  _realfree_heap_pagesize_hint = 262144

缺省 realfree allocator pagesize 是 64KB（65536），这样 64K 的条目能接纳 4GB 的内存。使用 256kB (262144)，限制将会增大到 16GB。

 

 

pxboracle@live.com
2014.08.28 12:18
share you knowledge with the world.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1259450/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12798004/viewspace-1259450/