Procedure Causing ORA"/>
PLSQL Procedure Causing ORA
运行了一个 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 ... ERROR IN ALERT: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 432 bytes (kxs-heap-c,kprbalo temp memory) Dump file trc 文件如下: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production Dumping Work Area Table (level=1) ===================================== Global SGA Info --------------- global target: 19457 MB auto target: 13839 MB max pga: 2048 MB pga limit: 4096 MB pga limit known: 0 pga limit errors: 0 ======================================= PRIVATE MEMORY SUMMARY FOR THIS PROCESS --------------------------------------- ****************************************************** PRIVATE HEAP SUMMARY DUMP 4097 MB total: 3080 MB commented, 658 KB permanent 1016 MB free (0 KB in empty extents), 4086 MB, 1 heap: "kxs-heap-c " 1016 MB free held ------------------------------------------------------ Summary of subheaps at depth 1 3072 MB total: 9434 KB commented, 3038 MB permanent 26 MB free (0 KB in empty extents), ========================================= 以上trc 可看到了进程遇到了 4GB 的限制 从 OS 角度来看,这些错误的出现通常是因为 map 条目耗尽造成的, 每个进程仅仅有 65536 个 memory map 条目。 在 trace 中内存的分配或者错误可能包括(但不仅限于):
- "pmucalm coll"
- "pmuccst: adt/re" or "pmuccst: adt/record"
- "pl/sql vc2"
- "permanent memory " SQL
- "kkqgAllocEle.n "
- 在 OS 层面来改变页数: more /proc/sys/vm/max_map_count sysctl -w vm.max_map_count=262144 以上修改只在memory中生效,若需重启生效需要修改 /etc/sysctl.conf
在 /etc/sysctl.conf文件最后添加一行
vm.max_map_count=262144
- 在数据库的初始化参数文件中设置下面的参数,来调整 realfree heap pagesize。然后重启数据库。
更多推荐
PLSQL Procedure Causing ORA
发布评论