sqladvise job 遇到 ora-04030 错误

PLSQL Procedure Causing ORA-04030

2018年05月15日 16:31:17 TT的甜甜屋 阅读数：113 标签： oracle

运行了一个 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 "

----查看相关的隐含参数：

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ

  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y

  3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';

Enter value for par: realfree

----参数设置如下

_realfree_heap_pagesize_hint     65536

_use_realfree_heap     TRUE

手动改变disable  作业

begin
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/

解决方案

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

• 在 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。然后重启数据库。

_use_realfree_heap=TRUE

_realfree_heap_pagesize_hint = 262144

（alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile sid='*'; ---rac）

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

 

参考mos文档：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 (文档 ID 1325100.1)