ORA-04030: out of process memory

ORA-04030: out of process memory

ORA-04030: out of process memory

今天在搭建一个测试环境的时候，遇到了一个错误：
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
起初一看是认为imp失败，想重新导入，但是imp任务并没有停止，跳过error，继续导入后面的数据，我仔细一看,
原因是在CREATE INDEX的时候报的错，当create index的时候，是需要排序的,
pga_aggregate_target>0是，启用pga自动管理，sort也由pga来自动分配，所以当sort_area_size被分配的大小>os 剩余内存，那么oracle会报这个错误，
这是我个人的理解：
报错信息如下：
IMP-00017: following statement failed with ORACLE error 4030:
"CREATE INDEX "FCBILLNOTICEBAKINDEX" ON "FC_BILL_NOTICE_BAK" ("ID" , "SEND_STATE" , "TRANSID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 201"
"326592 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CPFINDX" LOGGING"
IMP-00003: ORACLE error 4030 encountered
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
IMP-00017: following statement failed with ORACLE error 20000:
"BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"FCBILLNOTICEBAKINDEX"',NULL,NULL,NULL,2915265,11355,2915265,1,1,223495,2,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: INDEX "SYJZ"."FCBILLNOTICEBAKINDEX" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2124
ORA-06512: at "SYS.DBMS_STATS", line 5473

查了一下metalink，相关解释如下：
This error indicates that the oracle server process is unable to allocate more memory from the operating system.
这个错误暗示了ORACLE服务器进程从OS中，不能分配更多的内存。
The ORA-4030 thus indicates the process needs more memory (stack UGA or PGA) to perform. its job.
ORA-4030暗示了进程需要更多的内存(UGA和PGA)去执行他的工作。
1. Is there still sufficient memory available
查看OS是否还有充足的可用内存
top ：usually displays physical memory and swapspace statistics.
swapon -s ：displays swapspace usage
vmstat ： displays free physical memory

2. Is there an operating system limit set?
查看OS上时候做了相关内存使用资源的限制
ulimit -a

3. Is there an oracle limit set?
查看ORACLE 有无限制
PGA parameter implemented which limits the total amount of PGA that can be allocated for an instance
select sum(value)/1024/1024 Mb
from v$sesstat s, v$statname n
where n.STATISTIC# = s.STATISTIC#
and name = 'session pga memory';

4. Which process is requesting too much memory
SQL> select sid,name,value
from v$statname n,v$sesstat s
where n.STATISTIC# = s.STATISTIC# and name like 'session%memory%' order by 3 asc;

5. How to collect information on what the process is actually doing
a.select sql_text
from v$sqlarea a, v$session s
where a.address = s.sql_address
and s.sid =
b. alter session set events '4030 trace name heapdump level 25'
This dump can be used by Oracle Support analysts to find the cause of the excessive memory allocation.

6. General suggestions on avoiding this error
a. some operations just require a lot of memory. For sort issues, decreasing SORT_AREA_SIZE can help.This means that lowering SORT_AREA_SIZE can have a performance impact on queries requiring huge sort operations.
b. WORKAREA_SIZE_POLICY = AUTO & PGA_AGGREGATE_TARGET >0
c. Make sure your operating system and oracle limits are set reasonably
d. Make sure there is enough memory available (physical memory and swapspace)

Starting with Oracle9i, an option is provided to completely automate the management of PGA memory. Administrators merely need to specify the maximum amount of PGA memory available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET.
Furthermore, Oracle9i and newer releases can adapt itself to changing workload thus utilizing resources efficiently regardless of the load on the system. The
alter system set WORKAREA_SIZE_POLICY = AUTO
and
alter system set PGA_AGGREGATE_TARGET = (>0 int)

In 9iR2, PGA_AGGREGATE_TARGET parameter controls the sizing of workareas for all dedicated server connections, but it has no effect on shared servers (aka MTS) connections and the *_AREA_SIZE parameters will take precedence in this case.
In 10g, PGA_AGGREGATE_TARGET controls workareas allocated by both dedicated and shared connections.
In 11g, Automatic Memory Management (AMM) expands to managing both SGA and PGA memory. Under memory pressure for PGA memory, SGA memory will be re-allocated for use by a process to accommodate workarea needs.
NOTE: With AMM, setting an explicit value for PGA_AGGREGATE_TARGET will act as a minimum setting that AMM will not shrink below.

参考文献：1 .Subject: Diagnosing and Resolving ORA-4030 errors
Doc ID: 233869.1 Type: TROUBLESHOOTING
Modified Date : 05-FEB-2009 Status: PUBLISHED

+++++++++++++++++++++++

Hi,

I did a check on the database and found the Ora-04030 error.

ORA-04030: out of process memory when trying to allocate 4032 bytes (ioc_make_sub2,ioc_allocate_pal)
ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError
ORA-04030: out of process memory when trying to allocate 16640 bytes (joxcx callheap,ioc_allocate free)

  Cause: Operating system process private memory has been exhausted.

    Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive  allocations  of process memory space.

I just flushed the shared pool . Do you encounter this error again ?

Connected.
SHARED MEMORY : 1200
FREE MEMORY : 1003
FREE MEMORY PERCENT : 84

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                            0 bytes
aggregate PGA auto target                                                 0 bytes
global memory bound                                                       0 bytes
total PGA inuse                                                  1.2912E+10 bytes
total PGA allocated                                              1.7236E+10 bytes
maximum PGA allocated                                            2.0533E+10 bytes
total freeable PGA memory                                                 0 bytes
PGA memory freed back to OS                                               0 bytes
total PGA used for auto workareas                                         0 bytes
maximum PGA used for auto workareas                                       0 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                                     0 bytes
over allocation count                                                     0
bytes processed                                                           0 bytes
extra bytes read/written                                                  0 bytes
cache hit percentage                                                      0 percent

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22578826/viewspace-714193/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22578826/viewspace-714193/