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

 

 

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

转载于:http://blog.itpub.net/9252210/viewspace-611338/