适用于：
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
症状：
经过几小时的数据库操作，ORA-4031错误出现。
通过对ORA-4031 trace文件的检查发现一个称为KGH: NO ACCESS的内存分配方式占用了大量的内存，比如，下面这个例子中显示内存分配超过了500Mbytes。
Allocation Name Size
_________________________ __________
"free memory " 163766600
...
"KGH: NO ACCESS " 560713888
注意，我们会周期性地看到"KGH: NO ACCESS" 分配达到64M，这是正常的。当自动内存管理对SGA组件进行管理时，这一部分内存就是在SGA组件之间转换的内存。然而，看到持续的高分配量或者随时间增长这种分配方式的逐渐稳定是不正常的。当数据库需要做出大的变化，比如，高负载后改变内存大小，或者启用一个次佳的SGA设置，如SPFILE没有使用。
接下来的查询确认了内存分配"KGH: NO ACCESS"非常大。
select * from v$sgastat where pool = 'shared pool' and (name in ('free memory', 'sql area', 'library cache', 'miscellaneous', 'row cache', 'KGH: NO ACCESS') );
接下来的查询显示了针对“默认缓冲区缓存”和共享池的GROW和SHRINK操作。
ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH:MI:SS';
SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
SPOOL ASMM_RESIZE.TXT
select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE'
order by start_time, component;
SPOOL END
原因：
过于频繁的共享池和缓冲区缓存调整，导致过度的"KGH: NO ACCESS"内存分配，消耗了SGA内存。
针对此问题一些bug已经进行了记录，用于10gR2的几个版本。
Fixed 10.2.0.2
Unpublished Bug 4507532: SGA_TARGET DOESN'T WORK AS EXPECTED
Bug 5045507 ASMM - 频繁地调整共享池和缓冲区缓存的大小
Fixed 10.2.0.4
Unpublished Bug 6528336: APPSST 10G GSI: 大量的会话等待
CURSOR: PIN S WAIT ON X
Fixed 10.2.0.5
Unpublished Bug 7189722: APPSST GSI 10G: 非常频繁的GROW/SHRINK SGA调整大小操作
对于版本11.1.0.6 to 11.2.0.1，查阅Note 1127833.1 ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation
解决方案：
1 禁用ASSM
2 设置共享池和数据库缓冲区缓存的最小值
3 增大调整操作之间的时间间隔
4 应用补丁，根据您的版本升级或应用一次性补丁。
当过度的ASMM调整操作导致"KGH: NO ACCESS"内存分配消耗SGA的可用内存，执行修复操作时，以上解决方案是可行的。
禁用ASMM意味着内存不再在各池之间交换，但需要手动调整SGA参数。
在ASMM启用时将共享池和数据库缓冲区缓存设为最小值意味着ASMM仍处于工作状态，但是任何试图改变SGA组件大小使之小于最小值的操作都不会发生。这是唯一的不用执行数据库关闭/启动的操作。
增加调整操作的时间间隔意味着默认的30s将增大到一个更大的时间间隔。
最后，应用补丁或升级将会对代码进行修复。
解决方案1：禁用ASMM，手动设置SGA
为SGA参数DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 和STREAMS_POOL_SIZE设定合理的值。
禁用ASMM:
SQL> alter system set SGA_TARGET=0 scope=spfile;
手动设置SGA池的大小，使用上一步中所决定的值。
SQL> alter system set SHARED_POOL_SIZE=1G scope=spfile;
注意；并非所有的参数都需要设置，默认为0
关闭、启动数据库，从而关闭ASMM，新的SGA手动设置生效。
解决方案2：保持ASMM启用，但是设置共享池和缓冲区缓存的最小值
在一个典型的，繁忙的周期中，执行以下查询：
SET PAGESIZE 100
COL COMPONENT FORMAT A25
COL FINAL_SIZE FORMAT A15
select component, AVG(FINAL_SIZE) "AVG FINAL", MEDIAN(FINAL_SIZE) "MEDIAN FINAL", MAX(FINAL_SIZE) "MAX FINAL"
from v$sga_resize_ops
group by component;
对于行"DEFAULT buffer cache"，查看哪个值更大, "AVG FINAL" 还是 "MEDIAN FINAL"。将这个值记录为最小缓冲区缓存 n。
对于行"shared pool"，查看那个值更大"AVG FINAL" 还是"MEDIAN FINAL"，将这个值记录为最小共享池 m。
将以上两个值之和和目前的SGA_TARGET 或 SGA_MAX_SIZE相比较，如果和大于SGA_TARGET 或 SGA_MAX_SIZE，那么它们的值也需要增加，决定SGA_TARGET 或 SGA_MAX_SIZE的值能够为多大，然后实现增加。
SQL> ALTER SYSTEM SET SGA_TARGET=nnn SCOPE=BOTH;
设置DB_CACHE_SIZE的值为最小缓冲区缓存的大小。
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=n SCOPE=SPFILE;
设置SHARED_POOL_SIZE为最小共享池的大小。
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=m SCOPE=SPFILE;
重启数据库。
可选的是，您可以尝试执行内存变化而不重启数据库。然而您需要判断哪些动态设置优先，执行以下查询：
SQL> select component, current_size from v$sga_dynamic_components where component like '% pool' or component = 'DEFAULT buffer cache';
如果"shared pool" 和"DEFAULT buffer cache"都小于以上所设置的最小值，您可以在设置DB_CACHE_SIZE 和SHARED_POOL_SIZE时尝试使用SCOPE=BOTH
解决方案3：增加调整操作的时间间隔
针对您的数据库，决定一个调整操作的合理的时间间隔周期，默认为30s。
将参数"_memory_broker_stat_interval" 设置为上一步中所设定的值。
SQL> ALTER SYSTEM SET "_memory_broker_stat_interval"=n SCOPE=SPFILE;
关闭并启动数据库。
解决方案4：应用补丁
10.2.0.1
如果您正在使用v10.2.0.1，至少升级到v10.2.0.3。
10.2.0.2
如果您正在使用v10.2.0.2，没有bug记录了该版本，推荐升级。
10.2.0.3
下载阅读补丁6528336的readme和需求条件，截至2009年4月，支持的平台是：Linux x86, IBM AIX 64-bit, HP-UX Itanium。如果补丁不支持您的平台，请提交需求。
10.2.0.4
下载阅读补丁7189722的readme和需求条件，截至2009年4月，支持的平台是：Linux x86, Linux x86-64, IBM AIX 64-bit, HP-UX Itanium, Sun Solaris SPARC 64-bit。如果补丁不支持您的平台，请提交需求。
或者Oracle推荐升级到v10.2.0.5。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2012-6-4 16:25:34 |
赞(
操作
