【不错】oracle内存管理之PGA之案例分析：ora

一：错误总述 1． ORA-04031 基本上，ORA-04031出现的问题有几个可能性 A. 没有绑定编量造成 shared_pool 碎片过多，同时 shared_pool_size 太小 . --这个应该是比较常见的，也是Oracle提的最多的。 --这个通常会建议使用绑定变量，或者简单的加大shared_poo

一：错误总述

1．              ORA-04031
基本上，ORA-04031出现的问题有几个可能性
A. 没有绑定编量造成shared_pool碎片过多，同时shared_pool_size太小.

--这个应该是比较常见的，也是Oracle提的最多的。
--这个通常会建议使用绑定变量，或者简单的加大shared_pool.或者临时解决方法就是alter system flush shared_pool.

B. Large_pool,Java_pool太小造成的

--这个通过错误信息的提示很容易判断(Ora-04031 cannot allocate .. memeory in [large_pool])
--解决方法就是简单的加大 Large_pool or Java_pool

C. 过度的开CURSOR而不关闭。

--这个问题发生的越来越多，特别是在JAVA运行环境中，频频出现。加大Shared_pool或者flush shared_pool往往只能延迟问题出现的时间，而没法避免。
--判断方法：
select count(*) from v$open_cursor ;
select * from v$sysstat
where name = 'opened cursors current';
如果出来的值特大（以万为单位）时，基本就可以确定是这个原因了
--解决这个问题的方法就是检查程序，看是否没有正常的关闭cursor(对于JAVA来说，就是没有关闭Statement)。或者select sql_text from v$open_cursor,看看都是哪些cursor没关闭，再去检查车程序。
--也有的程序使用了保持一定量的cursor一直open,从而避免cursor过多次的开启，来提高性能。对于这种情况，则应该选择适当的shared_pool_size和控制keep_opening的cursor的量。
--也有可能Oracle参数session_cached_cursors太大，解决方法就是把它降低到适当的值

楼主的问题似乎有点象 session_cached_cursors 的问题，但是根据 opened cursors current判断，每个session开的cursor超过1000，已经超过session_cached_cursors，应该检查程序看看。

D. 当然，有时候一些BUG也可能引发ORA-04031,但是在高版本中已经很少出现(>=8174)


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

 

ORA-04030的问题一般是PGA过度分配造成的（对应的操作是sort/hash_join）。在Oracle9i中pga_aggregate_target指定了所有session总共使用的最大PGA上限，如果该值被设定了则默认的workarea_size_policy=auto, sort_area_size/sort_area_retained_size将被忽略。那么直接减小pga_aggregate_target就能解决一部分ORA-04030问题。

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 等


对于楼主来说，应该是sort_area_size（200M）/Hash_area_size(400M) 太大造成的，降成几十M或者几M 就可以了。

 

二：诊断并解决ORA-04031 错误

当我们在共享池中试图分配大片的连续内存失败的时候，Oracle首先清除池中当前没使用的所有对象，使空闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031错误。

当这个错误出现的时候你得到的错误解释信息类似如下:

04031, 00000, "unable to allocate %s bytes of shared memory (/"%s/",/"%s/",/"%s/",/"%s/")"

// *Cause: More shared memory is needed than was allocated in the shared

// pool.

// *Action: If the shared pool is out of memory, either use the

// dbms_shared_pool package to pin large packages,

// reduce your use of shared memory, or increase the amount of

// available shared memory by increasing the value of the

// INIT.ORA parameters "shared_pool_reserved_size" and

// "shared_pool_size".

// If the large pool is out of memory, increase the INIT.ORA

// parameter "large_pool_size".

 

1.共享池相关的实例参数

在继续之前，有必要理解下面的实例参数:

 

SHARED_POOL_SIZE
这个参数指定了共享池的大小，单位是字节。可以接受数字值或者数字后面跟上后缀"K"或 "M"。"K"代表千字节, "M"代表兆字节。

SHARED_POOL_RESERVED_SIZE
指定了为共享池内存保留的用于大的连续请求的共享池空间。当共享池碎片强制使 Oracle查找并释放大块未使用的池来满足当前的请求的时候，这个参数和SHARED_POOL_RESERVED_MIN_ALLOC参数一起可以用来避免性能下降。

这个参数理想的值应该大到足以满足任何对保留列表中内存的请求扫描而无需从共享池中刷新对象。既然操作系统内存可以限制共享池的大小，一般来说，你应该设定这个参数为 SHARED_POOL_SIZE参数的 10%大小。

SHARED_POOL_RESERVED_MIN_ALLOC 这个参数的值控制保留内存的分配。如果一个足够尺寸的大块内存在共享池空闲列表中没能找到，内存就从保留列表中分配一块比这个值大的空间。默认的值对于大多数系统来说都足够了。如果你加大这个值，那么Oracle服务器将允许从这个保留列表中更少的分配并且将从共享池列表中请求更多的内存。这个参数在Oracle 8i和更高的版本中是隐藏的。提交如下的语句查找这个参数值:

SELECT   nam.ksppinm NAME, val.ksppstvl VALUE

    FROM x$ksppi nam, x$ksppsv val

   WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'

ORDER BY 1;

10g注释：Oracle 10g的一个新特性叫做 "自动内存管理"允许DBA保留一个共享内存池来分shared pool,buffer cache, java pool和large pool。一般来说，当数据库需要分配一个大的对象到共享池中并且不能找到连续的可用空间，将自动使用其他SGA结构的空闲空间来增加共享池的大小。既然空间分配是Oracle自动管理的，ora-4031出错的可能性将大大降低。自动内存管理在初始化参数SGA_TARGET大于0的时候被激活。当前设定可以通过查询v$sga_dynamic_components视图获得。请参考10g管理手册以得到更多内容。

2.诊断ORA-04031错误

注：大多数的常见的 ORA-4031的产生都和 SHARED POOL SIZE有关，这篇文章中的诊断步骤大多都是关于共享池的。　对于其它方面如Large_pool或是Java_pool，内存分配算法都是相似的，一般来说都是因为结构不够大造成。

ORA-04031 可能是因为 SHARED POOL不够大，或是因为碎片问题导致数据库不能找到足够大的内存块。

ORA-04031 错误通常是因为库高速缓冲中或共享池保留空间中的碎片。在加大共享池大小的时候考虑调整应用,使用共享的SQL并且调整如下的参数：

SHARED_POOL_SIZE,

SHARED_POOL_RESERVED_SIZE,

SHARED_POOL_RESERVED_MIN_ALLOC.

首先判定是否ORA-04031错误是由共享池保留空间中的库高速缓冲的碎片产生的。提交下的查询：

SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures,

       last_failure_size

  FROM v$shared_pool_reserved;

如果:

REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

那么ORA-04031错误就是因为共享池保留空间缺少连续空间所致。要解决这个问题,可以考虑加大SHARED_POOL_RESERVED_MIN_ALLOC来降低缓冲进共享池保留空间的对象数目，并增大 SHARED_POOL_RESERVED_SIZE和 SHARED_POOL_SIZE来加大共享池保留空间的可用内存。

如果：

REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE

或者

REQUEST_FAILURES 等于0并且 LAST_FAILURE_SIZE

那么是因为在库高速缓冲缺少连续空间导致ORA-04031错误。

第一步应该考虑降低SHARED_POOL_RESERVED_MIN_ALLOC以放入更多的对象到共享池保留空间中并且加大SHARED_POOL_SIZE。

3.解决ORA-04031错误

ORACLE BUG

Oracle推荐对你的系统打上最新的PatchSet。大多数的ORA-04031错误都和BUG相关，可以通过使用这些补丁来避免。

下面表中总结和和这个错误相关的最常见的BUG、可能的环境和修补这个问题的补丁。

BUG	描述	Workaround	Fixed
<1397603>
<1640583>
<1318267>
<1193003>
<2104071>
<263791.1>

 

        

值值<2736601>

        

         

<1012046.6>

        

        

        

        

<61623.1>

        

        

        

<62143.1>

        

  

        

   

  

 

值

        

值

        似

  

        

        

   

  

  

        

        

   

  

  

        

        

   

  

  

        

        

   

  

  ++

        

        

   

  

        

        

        

似

        

              

        

 

值

        

        似