对于ORA-4031在10gR2中常见原因，过大的“"KGH: NO ACCESS"”内存分配（801787.1）

  
  数据库运行几个小时之后, 数据库报出了ORA-4031 。 该ORA-4031的跟踪文件的检查会显示内存分配 ，所谓 "KGH: NO ACCESS" 正在消耗大量的内存。  
 

例如，下面显示了500 Mbytes 此分配。 
   
 

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') );

 
下面的查询可以显示"DEFAULT buffer cache" 和 "shared pool"的 增长 和收缩操作：                       

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内存。

• Fixed 10.2.0.2
  Unpublished Bug 4507532: SGA_TARGET DOESN'T WORK AS EXPECTED
  Bug 5045507 ASMM - FREQUENT RESIZING OF SHARED POOL & BUFFER CACHE
• Fixed 10.2.0.4
  Unpublished Bug 6528336: APPSST 10G GSI: LARGE NUMBER OF SESSIONS WAITING ON CURSOR: PIN S WAIT ON X
• Fixed 10.2.0.5
  Unpublished Bug 7189722: APPSST GSI 10G: VERY FREQUENT GROW/SHRINK SGA RESIZE OPERATION HAPPENING

如果你查看这个问题 在版本 11.1.0.6 to 11.2.0.1，可以查看 以下问题：Note 1127833.1 ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation                                                

 
1.禁用ASMM
或者
2.设置Shared Pool 和Database Buffer Cache的最小值。
或者
3.增加大小调整操作之间的时间
或者
4.升级 补丁，通过升级或应用一次性补丁，这取决于你的版本。

根据MOS说明，要解决所遇到的这种问题，其中过度ASMM调整操作导致“"KGH: NO ACCESS" 内存分配消耗提供给SGA的内存，上述解决方案都可以;。
Note 451960.1 How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled
Note 742599.1 High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity

禁用ASMM将意味着池之间的内存不再是交换，但它需要对SGA参数进行手动设置。

当启用ASMM，为Shared Pool 和  Buffer cache设置最小值时 意味着ASMM仍然在运行，但在低于最小值时，不会发生任何去改变SGA 组件大小的操作， 从而更少的内存需要经过 "KGH: NO ACCESS" 内存分配。这是唯一 不需要的重启 数据库 的操作。


增加调整大小操作之间的时间将意味着默认的30秒要增大到更大的时间间隔。

最后，在应用补丁或者升级将修复代码按照Oracle  Development 的建议。


解决方法1：禁用ASMM，并手动设定SGA

        1.确定SGA参数DB_CACHE_SIZE，SHARED_POOL_SIZE，LARGE_POOL_SIZE，JAVA_POOL_SIZE和STREAMS_POOL_SIZE（如apprioriate）的  合理值。如需进一步的帮助，请查看MOS说明    ：Note 1008866.6 How to determine SGA Size (7.x, 8.0.x, 8i, 9i, 10g)
        2.禁用ASMM:

SQL> alter system set SGA_TARGET=0 scope=spfile;

3.手动设置SGA池的大小，使用从第1步（上面）确定的值：
例如:

SQL> alter system set SHARED_POOL_SIZE=1G scope=spfile.

 
注意：不是所有的参数都需要设置，这些值会默认为0.                                                 

4.关闭 和启动数据库，以便使 ASMM被关闭，而新手工设置的SGA生效。

解决方法2：保持ASMM启用，但对于共享池和缓冲区高速缓存设置最小值

1. 在一个典型的，繁忙的时期在数据库上，运行以下查询：

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;

2.对于 "DEFAULT buffer cache", 确定 "AVG FINAL" 或"MEDIAN FINAL"中更大的一个值，将这个值作为 最小的Buffer Cache 。
3.对于 " Shared Pool ", 确定 "AVG FINAL" 或"MEDIAN FINAL"中更大的一个值，将这个值作为 最小的Shared Pool 。
4.将最小的 Buffer Cache 和最小Shared Pool相加，然后和当前的SGA_TAGET或SGA_MAX_SIZE 相比较。
5.如果总和大于 SGA_TARGET 或 SGA_MAX_SIZE, 则需相应增加SGA_TARGET 和 SGA_MAX_SIZE的大小， 确定好SGA_TARGET 和 SGA_MAX_SIZE的大小后便可实施，如：

SQL> alter system set sga_max_size=nnn scope=SPFILE;

SQL> ALTER SYSTEM SET SGA_TARGET=nnn SCOPE=BOTH;
6.设置参数DB_CACHE_SIZE到最小缓冲区高速缓存的值

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=n SCOPE=SPFILE;
7.设置参数SHARED_POOL_SIZE到最小共享池的价值

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=m SCOPE=SPFILE;

 8.Re-start the database.
9.或者，您可以尝试无需重新启动数据库执行内存更改，但是首先需要确定什么是动态设置，通过运行下面的查询：

SQL> select component, current_size from v$sga_dynamic_components where component like '% pool' or component = 'DEFAULT buffer cache';

如果同时"shared pool"和 "DEFAULT buffer cache" 小于在步骤2和3以上确定的最低值，那么设置DB_CACHE_SIZE和SHARED_POOL_SIZE时你可以尝试使用SCOPE = BOTH。  

解决方案3：增加大小调整操作之间的时间

1. 再次仔细看看 Note 742599.1 的解决方案, 特别是涉及到参数“_memory_broker_stat_interval”的解决方案。
2. 对于您的数据库，确定一个合理的时间期限调整大小操作之间的延时，目前 给出的默认值是30秒。
3. 设置参数 "_memory_broker_stat_interval" 为你确定的时间周期 :

SQL> ALTER SYSTEM SET "_memory_broker_stat_interval"=n SCOPE=SPFILE;

1. 重启数据库，使调整生效。

解决方法4：升级补丁      

10.2.0.1

如果您使用的是v10.2.0.1，升级到最低v10.2.0.3的（或见下文）。

10.2.0.2

如果您使用的是v10.2.0.2，这个版本没有这个错误已记录，所以建议您升级（见下文）。

10.2.0.3

1. 下载补丁包 Patch 6528336  阅读自述文件和先决条件                        
   至2009年4月, 平台有： Linux x86, IBM AIX 64-bit, HP-UX Itanium.
2. 如果没有你平台的补丁，找官方解决。                                    

10.2.0.4

1. 下载补丁包  Patch 7189722阅读自述文件和先决条件        
   至2009年4月, 平台有： Linux x86, Linux x86-64, IBM AIX 64-bit, HP-UX Itanium, Sun Solaris SPARC 64-bit.
2. 如果没有你平台的补丁，找官方解决。    

或者 如果在你的平台可用，Oracle建议您升级到v10.2.0.5

BUG:5045507 - ASMM - FREQUENT RESIZING OF SHARED POOL & BUFFER CACHE
NOTE:1008866.6 - How to determine SGA Size (7.x, 8.x, 9.x, 10g)
NOTE:1127833.1 - ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation
NOTE:451960.1 - How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled
NOTE:742599.1 - High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity