對於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記憶體。

在10gR2中不同的版本，這個問題的幾個bug 已有記錄 ;

• Fixed 10.2.0.2
  Unpublished Bug 4507532: SGA_TARGET DOESN'T WORK AS EXPECTED
   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. 下載補丁包  閱讀自述檔案和先決條件                        
   至2009年4月, 平臺有： Linux x86, IBM AIX 64-bit, HP-UX Itanium.
2. 如果沒有你平臺的補丁，找官方解決。                                    

10.2.0.4

1. 下載補丁包  
   至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

 - 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