SHARED POOL中KGH: NOACCESS占用大量内存的问题分析

接上篇的ORA-04031错误，知道整个SHARED POOL的组件中，ASM extent pointer array占用很大的内存是显示的一个BUG后，继续分析各个组件占用的内存大小，结果发现KGH: NO ACCESS组件也占用了400多M，这个又是个什么鬼东西呢？还是看看METALINK上怎么说的吧。

[@more@]

首先找到了下面一篇文章：
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4157546628394209832::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,461160.1,1,0,1,helvetica

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.

Symptoms

Frequent ORA-4031 even after increasing SGA_TARGET, Shared_pool

Things observed:

1) ASMM

Sga_target is set

2) 'KGH: NO ACCESS' increasing in shared pool
SQL> select * from v$sgastat where pool = 'shared pool' and (name in ('free memory', 'sql area', 'library cache', 'miscellaneous', 'row cache', 'KGH: NO ACCESS') );

We can see High KGH: NOACCESS entries in the shared pool.
Cause

Bug 5045507 ASMM - FREQUENT RESIZING OF SHARED POOL & BUFFER CACHE

Symptoms:

1) The allocation between shared pool and default buffer cache is growing/shrinking regularly (every 1-5 minutes).

2) AWR report OR query on v$sgastat shows high value for " KGH: NOACCESS "

3) Disabling Automatic Shared Memory Management (ASMM) not causing the ORA-4031 errors
Solution

This is caused because of frequent allocation operation between shared pool and default buffer cache. Alllocation from buffer cache is being converted to "KGH: NOACCESS" in the shared pool.

This trashing from cache to shared pool was fixed in 10.2.0.2. Bug 4507532 (Unpublished) was used to fix it into 10.2.0.2

Patch for Bug 4507532 (Unpublished) cannot be provided on top of 10.2.0.1

So, the options to resolve this issue are

1) Upgrade to the latest patchset 10.2.0.3

OR

2) Disable ASMM as a workaround.

这篇文章上说的是从BUFFER CACHE收刮来的内存，被分配给SHARED POOL的时候，就是先被标识为KGH: NOACCESS，然后SHARED POOL的其他组件可以从这个KGH: NOACCESS组件中再来取自己需要的内存。那既然KGH: NOACCESS组件有500M，为啥还会报ORA-04031错呢，又往下看，发现我的现象跟这里说的一摸一样，就是BUFFER CACHE和SHARED POOL两个组件之间不停的在GROW和SHRINK，就那几十M的内存在两个池之间不停的搬过来搬过去，也不闲累，而且，在RAC环境中，只有其中一个节点有这样的问题，这又是为啥呢？

还找到了另一篇文章：
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4157546628394209832::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,451960.1,1,1,1,helvetica

Oracle Server - Enterprise Edition - Version: 10.1 to 10.2
Information in this document applies to any platform.
Goal
The value for the component 'KGH: NO ACCESS' in the Shared Pool is growing when ASMM is enabled by setting SGA_TARGET. What does this component signify? How to prevent the growth of this component?
Solution
'KGH: NO ACCESS' refers to granules that are in transit with ASMM i.e memory being reassigned from the Shared Pool to the Database Buffer Cache and vice-versa.

This memory component in the Shared Pool marked as 'KGH: NO ACCESS' is used by the Buffer Cache.

One could see the value for this component increasing when ASMM (Automatic Shared Memory Management) is enabled by setting the SGA_TARGET parameter.

When ASMM is enabled, Oracle will dynamically manage the memory allocations between the tunable pools based on the requirement.

Note that one can still specify a value for the each of the tunable parameters i.e Shared Pool, Database Buffer Cache, Large Pool, Java Pool and Streams Pool when ASMM is enabled. When minimum value for each of the tunable parameters is set, Oracle will make sure that the amount of memory being allocated for the corresponding pool will not shrink below the specified amount.

Thus by setting minimum value for the Shared Pool and Database Buffer Cache, we will be ensuring that the amount of memory available for reassignment between the two pools will be lesser thus preventing the growth of this 'KGH: NO ACCESS' component.

To prevent the growth of this component you can either

1> Disable ASMM

Or

2> Set the minimum values for the Shared Pool and the Database Buffer Cache

这上面又说SHARED POOL中的KGH: NO ACCESS是被BUFFER CACHE使用的，这个好像和前面的说法正好反过来了，是我E文太烂了还是ORACLE自己也搞糊涂了呢？

正过来反过去，反正这个东西就是游离在BUFFER CACHE和SHARED POOL之间的一块内存。另外一个问题就是，因为BUG的原因，使用ASMM会导致SHARED POOL和BUFFER CACHE会频繁的被扩大和缩小。解决的办法要么就不要使用ASSM，要不就升级。

这里暂时把SHARED POOL的最小值扩大了一些，让他怎么扩大缩小都不要导致ORA-04031就好

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

转载于:http://blog.itpub.net/25016/viewspace-1004637/