详细讲解ORA-04030的产生原因及解决方法

现象：ORA-04030: 在尝试分配...字节 (hash-join subh,kllcqas:kllsltba) 时进程内存不足。

　　ORA-04030:out of process memory when trying to allocate string bytes

　　ORA-04030的出现原因及解决方法：

　　ORA-04030出现的基本都是过多的使用memory造成的

　　Oracle process使用的内存数量是有一定限制的：

　　A. 对于32 BIT系统，有SGA 1.7G限制

　　B. 某些OS系统本身也有一些内存参数限制

　　--运行 ulimit 看看

　　C. OS系统本身物理内存+Swap的限制

　　现在我们应该检查DB使用的SGA + PGA是否超过上面的限制。

　　SGA 包括 db_cache,shared_pool,large_pool,java_pool session的PGA包括sort_area_size/Hash_area_size/*_area_size 或者 pga_aggregate_target

　　还有执行的CODE以及一些data也会占用空间。

　　然后再根据情况降低里面的某些值了，比如db_cache，sort_area_size等等。

　　假如是OS系统的某Limited造成的，大家可以考虑放开限制man ulimit来观察如何放开限制……

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

错误如下：

GATHER_STATS_JOB encountered errors.  Check the trace file.
Sat Nov 27 06:00:15 2010
Errors in file /oradata/oracle/admin/app2/bdump/app2_j001_978984.trc:
ORA-04030: out of process memory when trying to allocate 8192 bytes (sort subheap,sort key)

查了一下metalink，相关解释如下：
    This error indicates that the oracleserverprocess 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 = <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.
    In10g, 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

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

ORA-04030: 在尝试分配 xxxx字节 (kxs-heap-c,temporary memory)时进程内存不足

最近遇到一个库出现了类似上面的错误，oracle莫名的down掉了，重启oracle又恢复了正常。网上找了一圈的原因，也没有肯定的答复，主要有以下的成因：

1.pga的设置不是很合理。“try to increase the size og PGA_AGGREGATE_TARGET,is too much users for be handled by XXXM”，可能是sql排序需要更大的memory。

 

2.也有说是10.2.0.1这个版本的bug，需要打patch。

 

3.由于是32 bit的windows在系统层面上利用内存有限制....（这个应该不成立，我的os版本是x64的）

 

4.还有建议把shared_pool调大的，其实在10g上就是增大sga_target

 

5.在看看官方文档的解释：

ORA-04030 out of process memory when trying to allocate stringbytes (string,string)

Cause: Operating system process private memory has beenexhausted.

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