ORA-04030: 在尝试分配 16328 字节 (koh-kghu call ,kollrsz) 时进程内存不足
Posted by wukaiqiang; tagged with none
ORA-04030: 在尝试分配 16328 字节 (koh-kghu call ,kollrsz) 时进程内存不足
oerr查看报错信息，是process获取不到足够的内存，server process消耗的是PGA，而非SGA
[oracle@febdb ~]$ oerr ora 04030
04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"
// *Cause: Operating system process private memory was exhausted.
// *Action:
PGA不足，思路主要查看PGA、workarea的参数值和实例启动以来的统计值，可以分4步来处理，第一步查询参数值、第二步查询隐含参数值、第三步查询v$pgastat统计值、第四步分析前面三步的各项值得出结论
第一步
SQL> show parameter pga
NAME TYPE VALUE
pga_aggregate_target big integer 8G
SQL> show parameter area_size
NAME TYPE VALUE
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string AUTO
第二步
SQL> col NAME format a25
SQL> col VALUE format a20
SQL> col DESCRIPTION format a55
SQL> set linesize 110
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_pga_max%';
NAME VALUE DESCRIPTION
_pga_max_size 1717985280 Maximum size of the PGA memory for one process
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%smm_max%';
NAME VALUE DESCRIPTION
_smm_max_size_static 838860 static maximum work area size in auto mode (serial)
_smm_max_size 838860 maximum work area size in auto mode (serial)
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%smm_px%';
NAME VALUE DESCRIPTION
_smm_px_max_size_static 4194304 static maximum work area size in auto mode (global)
_smm_px_max_size 4194304 maximum work area size in auto mode (global)
一个进程最大的PGA值，本案例中是1717985280B（_pga_max_size的单位是B）
自动模式下，一个进程的PGA的最大work area值，本案例中是838860KB（_smm_max_size的单位是KB，此值一般是_pga_max_size的50%）
自动模式下，所有进程的PGA最大work area总量值，本案例中是4194304KB（_smm_px_max_size的单位是KB，此值一般是PGA_AGGREGATE_TARGET参数的50%）
第三步
SQL> col value format 9999999999999999
SQL> col name format a40
SQL> select * from v$pgastat where name in ('aggregate PGA target parameter','maximum PGA allocated','maximum PGA used for auto workareas','maximum PGA used for manual workareas','total PGA allocated','total PGA inuse','cache hit percentage') order by 1;
NAME VALUE UNIT
aggregate PGA target parameter 8589934592 bytes
cache hit percentage 97 percent
maximum PGA allocated 29975256064 bytes
maximum PGA used for auto workareas 3414564864 bytes
maximum PGA used for manual workareas 2713600 bytes
total PGA allocated 15749543936 bytes
total PGA inuse 12480521216 bytes
aggregate PGA target parameter
Current value of the PGA_AGGREGATE_TARGET initialization parameter
--当前PGA的参数值，本案例中为8589934592bytes，和参数pga_aggregate_target值一样
cache hit percentage
A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.
--小于100%表示排序等消耗work areas的操作不一定都是在PGA完成，而是work areas走了磁盘使用了临时表空间，本案例中为97%
maximum PGA allocated
Maximum number of bytes of PGA memory allocated at one time since instance startup
--PGA曾经达到的最大值，本案例中为29975256064bytes
maximum PGA used for auto workareas
Maximum amount of PGA memory consumed at one time by work areas running under the automatic memory management mode since instance startup
--自动模式下的work areas曾经达到的最大值，本案例中为3414564864bytes
maximum PGA used for manual workareas
Maximum amount of PGA memory consumed at one time by work areas running under the manual memory management mode since instance startup.
--手工模式下的work areas曾经达到的最大值，本案例中为2713600bytes
total PGA allocated
Current amount of PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value.
--当前PGA的真实值，本案例中为15749543936 bytes
Oracle数据库试图将这个数字保持在PGA_AGGREGATE_TARGET初始化参数的值以下。然而，PGA可以在短时间内以较小的百分比分配超过该值，当工作区域的工作负载增长非常快，或者当PGA_AGGREGATE_TARGET被设置为一个小的值时，这是可能的。
total PGA inuse
Indicates how much PGA memory is currently consumed by work areas
--当前work areas的真实值。本案例中为12480521216bytes
第四步
根据以上1、2、3的数据，分析是PGA不足还是workarea_size_policy设置为AUTO的问题。
如果PGA不足，重新设置参数pga_aggregate_target，使用更大值
如果PGA设置很大后，还是保报错，则设置参数值workarea_size_policy为MANUAL，再根据业务设置*area_size这些参数的值。
--本案例明显是因为PGA不足引发，设置PGA参数值为20G解决。