PLSQL Procedure 引起 ORA-04030

pxbibm發表於2014-08-28
SQL

您執行了一個 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。

 

 



2014.08.28 12:18
share you knowledge with the world.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12798004/viewspace-1259450/，如需轉載，請註明出處，否則將追究法律責任。

相關文章