SHARED POOL中KGH: NOACCESS佔用大量記憶體的問題分析

接上篇的ORA-04031錯誤，知道整個SHARED POOL的元件中，ASM extent pointer array佔用很大的記憶體是顯示的一個BUG後，繼續分析各個元件佔用的記憶體大小，結果發現KGH: NO ACCESS元件也佔用了400多M，這個又是個什麼鬼東西呢？還是看看METALINK上怎麼說的吧。

[@more@]

首先找到了下面一篇文章：

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環境中，只有其中一個節點有這樣的問題，這又是為啥呢？

還找到了另一篇文章：

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就好