一、故障症狀

某些時段發現大量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"：

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

下面的查詢可以顯示"DEFAULT buffer cache" 和 "shared pool"的增長和收縮操作：

select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME,status
from v$sga_resize_ops
order by start_time, component;
START_TIME COMPONENT OPER_TYPE OPER_MODE INITIAL FINAL END_TIME STATUS
------------------- ------------------------- ------------- --------- ---------- ---------- ------------------- ---------
14/12/2015 17:05:01 DEFAULT buffer cache SHRINK IMMEDIATE 3520 3456 14/12/2015 17:05:01 COMPLETE
14/12/2015 17:05:01 DEFAULT buffer cache SHRINK IMMEDIATE 3520 3520 14/12/2015 17:05:01 ERROR
14/12/2015 17:05:01 shared pool GROW IMMEDIATE 4224 4288 14/12/2015 17:05:01 COMPLETE
14/12/2015 17:05:01 shared pool GROW IMMEDIATE 4224 4224 14/12/2015 17:05:01 ERROR
15/12/2015 21:06:22 DEFAULT buffer cache SHRINK IMMEDIATE 3392 3328 15/12/2015 21:06:23 COMPLETE
15/12/2015 21:06:22 DEFAULT buffer cache SHRINK IMMEDIATE 3456 3392 15/12/2015 21:06:22 COMPLETE
15/12/2015 21:06:22 large pool GROW IMMEDIATE 256 320 15/12/2015 21:06:23 COMPLETE
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:

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';
COMPONENT CURRENT_SIZE
------------------------- ------------
shared pool 4496293888
large pool 335544320
java pool 67108864
streams pool 67108864
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" 為你確定的時間週期 :

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/

"KGH: NO ACCESS"記憶體分配過大，引起的ORA-4031故障

相關文章