"KGH: NO ACCESS"内存分配过大，引起的ORA-4031故障

一、故障症状

某些时段发现大量ORA-04031报错
Errors in file /oracle/diag/rdbms/obie/obie1/trace/obie1_smon_18153542.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","FET$","KGLS^6c13497e","kglHeapInitialize:temp")



systemstate dump发现以下信息

#cat obie1_j000_37617704_bucket.trc
Process diagnostic dump for oracle@cnbidbp1 (J000), OS id=37617704,
pid: 57, proc_ser: 243, sid: 280, sess_ser: 54191 
-------------------------------------------------------------------------------
current sql:
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 37617704
machine: cnbidbp1 program: oracle@cnbidbp1 (J000)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: GATHER_STATS_JOB, hash value=930355498
Current Wait Stack:
0: waiting for 'SGA: allocation forcing component growth'
=0x0, =0x0, =0x0
wait_id=17065634 seq_num=54583 snap_id=99
wait times: snap=0.041714 sec, exc=5.091919 sec, total=5.435487 sec
wait times: max=infinite, heur=16.686881 sec
wait counts: calls=99 os=99
in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 268, ser: 1
Dumping final blocker:
inst: 1, sid: 268, ser: 1
Wait State:
fixed_waits=0 flags=0x23 boundary=0x0/-1

该时段的AWR发现KGH: NO ACCESS 占用大量内存, 而SQLA部分占用内存才265M


注意正常的周期性观察 "KGH: NO ACCESS" 的 分配一般仅 高达约400M。
这个内存是当自动内存管理是在调整SGA组件时的一个在 SGA组件之间的过渡，然而，看到持续的高分配或随着时间的推移一直稳步积聚这种分配类型是很不正常的 。唯一的例外是当数据库需要做出大的变化，比如，一个重负载之后改变内存时，或启动使用次优的SGA设置，如当不使用SPFILE时。

下面的查询可确定 内存分配大量的  "KGH: NO ACCESS"：  

1. select * from v$sgastat where pool = 'shared pool' and (name in ('free memory', 'sql area', 'library cache', 'miscellaneous', 'row cache', 'KGH: NO ACCESS') );
   
2. 
   
3. POOL NAME BYTES
   
4. ------------ -------------------------- ----------
   
5. shared pool KGH: NO ACCESS   402056288
   
6. shared pool free memory      2149600472
   
7. shared pool miscellaneous    1720
   
8. shared pool row cache        7593704

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

1. select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME,status
   
2. from v$sga_resize_ops
   
3. order by start_time, component;
   
4. 
   
5. START_TIME COMPONENT OPER_TYPE OPER_MODE INITIAL FINAL END_TIME STATUS
   
6. ------------------- ------------------------- ------------- --------- ---------- ---------- ------------------- ---------
   
7. 14/12/2015 17:05:01 DEFAULT buffer cache SHRINK IMMEDIATE 3520 3456 14/12/2015 17:05:01 COMPLETE
   
8. 14/12/2015 17:05:01 DEFAULT buffer cache SHRINK IMMEDIATE 3520 3520 14/12/2015 17:05:01 ERROR
   
9. 14/12/2015 17:05:01 shared pool GROW IMMEDIATE            4224 4288 14/12/2015 17:05:01 COMPLETE
   
10. 14/12/2015 17:05:01 shared pool GROW IMMEDIATE            4224 4224 14/12/2015 17:05:01 ERROR
    
11. 15/12/2015 21:06:22 DEFAULT buffer cache SHRINK IMMEDIATE 3392 3328 15/12/2015 21:06:23 COMPLETE
    
12. 15/12/2015 21:06:22 DEFAULT buffer cache SHRINK IMMEDIATE 3456 3392 15/12/2015 21:06:22 COMPLETE
    
13. 15/12/2015 21:06:22 large pool GROW IMMEDIATE             256  320  15/12/2015 21:06:23 COMPLETE
    
14. 15/12/2015 21:06:22 large pool GROW IMMEDIATE             192  256  15/12/2015 21:06:22 COMPLETE

二、故障原因

共享池和缓冲区高速缓存的过于频繁的调整大小 ，从而导致过量 "KGH: NO ACCESS" 内存分配，消耗SGA内存。

在10gR2中不同的版本，这个问题的几个bug 已有记录 ;

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:

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

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

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

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

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


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

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

1. SET PAGESIZE 100
   
2. COL COMPONENT FORMAT A25
   
3. COL FINAL_SIZE FORMAT A15
   
4. select component, AVG(FINAL_SIZE) "AVG FINAL", MEDIAN(FINAL_SIZE) "MEDIAN FINAL", MAX(FINAL_SIZE) "MAX FINAL"
   
5. from v$sga_resize_ops
   
6. 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的大小后便可实施，如：

1. SQL> alter system set sga_max_size=nnn scope=SPFILE;
   
2. 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.或者，您可以尝试无需重新启动数据库执行内存更改，但是首先需要确定什么是动态设置，通过运行下面的查询：

1. SQL> select component, current_size from v$sga_dynamic_components where component like '% pool' or component = 'DEFAULT buffer cache';
   
2. 
   
3. COMPONENT CURRENT_SIZE
   
4. ------------------------- ------------
   
5. shared pool          4496293888
   
6. large pool           335544320
   
7. java pool             67108864
   
8. streams pool          67108864
   
9. DEFAULT buffer cache 3489660928

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


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

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

1. 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，这个版本没有这个错误已记录，所以建议您升级（见下文）。
10.2.0.3
下载补丁包 Patch 6528336  阅读自述文件和先决条件                        
至2009年4月, 平台有： Linux x86, IBM AIX 64-bit, HP-UX Itanium.
如果没有你平台的补丁，找官方解决。                                    
10.2.0.4
下载补丁包  Patch 7189722阅读自述文件和先决条件        
至2009年4月, 平台有： Linux x86, Linux x86-64, IBM AIX 64-bit, HP-UX Itanium, Sun Solaris SPARC 64-bit.
如果没有你平台的补丁，找官方解决。    
或者 如果在你的平台可用，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


文章部分内容参考：  http://blog.itpub.net/29863023/viewspace-1815505/

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

转载于:http://blog.itpub.net/17086096/viewspace-1873403/