oracle 日志40g,记一次ORA-4030错误的分析过程-解密Linux下Oracle单个进程申请内存限制...

这是一次日常巡检中发现的问题，一般遇到ORA-错误都要分析下原因，看看对数据库还是对业务运行是否有影响，告警日志报错如下

ORA-04030: out of process memory when

trying to allocate 169040 bytes (pga heap,kgh stack)

ORA-04030: out of process memory when

trying to allocate 127000 bytes (QERHN hash-joi,kllcqas:klls

从报错信息看是进程内存不足导致的异常，对业务数据有哪些影响还是不清楚，于是我使用oerr

ora 4030查了下错误含义

Cause: Operating system process private memory

was exhausted,

从Oracle提示以及oerr信息看是私有内存不足，也就是PGA大小不够了。也就是说某个用户执行SQL，这个SQL需要使用PGA内存，但是这个需要申请的内存大小超过了Oracle可以提供的大小，也就是Oracle在这里有资源限制。于是我自然想到查询PGA参数设置信息

SQL> show parameter pga;

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

_pga_max_sizebig

integer 60G

pga_aggregate_limitbig

integer 0

pga_aggregate_targetbig

integer 40G

如果细心你会发现有一个隐藏参数_pga_max_size，该参数的含义每个进程使用PGA的最大值。也就是用户在某个时间曾经修改过这个隐藏参数，但是目前看这个参数没有效果。这里我们其实可以猜测，很可能Oracle对每个进程申请的内存做了限制，其实可以反向思考，如果没有限制，如果某个SQL异常，多少内存都不够用，或许Oracle早就挂了，这样对整体系统的稳定性也有影响。我们进一步分析OraclePGA分配情况

SQL> select name,value/1024/1024/1024

"Gbytes" from v$pgastat;

NAMEGbytes

----------------------------------------

----------

aggregate PGA target parameter40

aggregate PGA auto target34.0423737

global memory bound8

total PGA inuse2.17521286<== current PGA usage

total PGA allocated2.8698864<== current PGA allocated

maximum PGA allocated95.2019644<== maximum over "life" of

instance

total freeable PGA memory.308898926

这里可以看出Oracle确实最大分配了过95G PGA

通过阅读几篇MOS文档，确实发现Oracle在Linux下数据库的单个进程分配内存受到隐藏参数设置，这个参数和操作系统参数配合使用可以调到数据库服务器进程分配的内存大小。

下面我们查询这个隐藏设置

select i.ksppinm name , v.ksppstvl cur_val,

v.ksppstdf default_val, v.ksppstvf

from x$ksppi i, x$ksppcv v

where i.indx = v.indx and i.ksppinm in ('_realfree_heap_pagesize',

'_use_realfree_heap')

NAMECUR_VALDEFAULT_VKSPPSTVF

------------------------------ ------------------------------

--------- ----------

_realfree_heap_pagesize65536TRUE

_use_realfree_heapTRUETRUE

65536说明数据库RDBMS最大4G限制，继续操作系统查询

more

/proc/sys/vm/max_map_count

262144

262144说明操作系统用户限制16g，二者不一致做取最小限制4g .也就是Oracle对单个进程申请内存收到4G大小的限制。为了验证这个问题，我们打开trc文件

REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS

-----------------------------------------

Dump of Real-Free Memory Allocator Heap

[0x7ffff29eb000]

mag=0xfefe0001 flg=0x5000007 fds=0x0

blksz=65536

blkdstbl=0x7ffff29eb010, iniblk=523264

maxblk=524288 numsegs=321

In-use num=128863 siz=4171431936, Freeable

num=6 siz=589824, Free num=26 siz=4325376

从trc文件中，我们可以确认限制了单个进程的内存大小524288个块(8k)= 4g .我们继续查询trc文件的内容，找到问题SQL看看他们对PGA申请的大小

-------------------------

Top 10 processes:

-------------------------

(percentage is of 46 GB total allocated

memory)

70% ospid 326054(): 31 GB used, 32 GB alloc, 576 KB freeable <= CURRENT PROC

23% ospid 372014(): 10 GB used, 10 GB alloc, 384 KB freeable

1%

ospid 391398(): 464 MB used, 464 MB alloc

0%

ospid 212162(RMS0): 19 MB used, 80

MB alloc, 60 MB freeable

0%

ospid 355027(): 50 MB used, 55 MB alloc, 896 KB freeable

0%

ospid 150316(): 43 MB used, 47 MB alloc

0%

ospid 213222(ARC0): 42 MB used, 45

MB alloc

0%

ospid 213226(ARC1): 42 MB used, 45

MB alloc

0%

ospid 213228(ARC2): 42 MB used, 45

MB alloc

0%

ospid 213230(ARC3): 42 MB used, 45

MB alloc

有两个进程申请PGA内存分别为32g和10G ,这两个进程都报错。显然他们申请的内存大小都超过了4G。

具体的SQL就不贴了，后面的问题就是分析SQL执行计划，看看哪里最耗费PGA资源，比如排序，hash等操作，看是否可以优化，那就是另一个层面的问题(提示原始SQL中都是dblink远程表关联，这种操作往往需要远程表先传递到本地PGA操作)。

这个问题两个解决方案：

1修改数据库和操作系统参数，提供更大的单个进程内存

2调优SQL减少PGA的使用，这个是优先要考虑的。

比如修改到32G

524288 <<<<<<< 32G

1修改操作系统参数

sysctl -w vm.max_map_count=524288

2修改数据库隐藏参数

alter system set

"_realfree_heap_pagesize"=524288