1.問題現象
20年12月31日，資料庫應用人員反映2020-12-31 12:40:10存在告警，過了幾分鐘之後業務恢復正常。
表現的狀態：Connect to database time out, please check db status!
因為業務反饋的內容很有限，所以我們取了12月31日12:00-13:00的AWR進行分析。
可以看到AAS並不是很高，AAS=755.39/32.05=23.57
(備註：AAS是衡量快照時間內資料庫負載的重要指標）
通過AWR觀察
可以看到有大量的cursor:pin s wait on X和SGA:allocation forceing comonent growth等待事件。
2.問題分析
通過MOS因為ASMM和AMM使用自動調整記憶體管理方案。 啟用這些架構中的任何一種，都可以在SGA中的各個元件（例如緩衝區快取記憶體和共享池）之間自動移動記憶體，以便在其中一個元件中填充記憶體請求導致的。
SQL> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 206336M sga_target big integer 0 SQL> show parameter target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 206336M memory_target big integer 206336M parallel_servers_target integer 32 pga_aggregate_target big integer 0 sga_target big integer 0 SQL> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 0 SQL>
通過檢查發現資料庫使用的AMM的記憶體管理方式，自動記憶體管理automatic memory management（以下均稱AMM）是oracle 11g新推出的新特性，意在對例項中的PGA和SGA進行自動管理。AMM是自動共享記憶體管理automatic shared memory management（ASMM）的擴充
ORACLE 11g AMM 的引入， 組合出來有 5 種記憶體管理形式.
自動記憶體管理（AMM） : memory_target=非0，是自動記憶體管理，如果初始化引數 LOCK_SGA=TRUE，則 AMM 是不可用的。
自動共享記憶體管理(ASMM): 在memory_target=0 and sga_target為非0的情形下是自動記憶體管理
手工共享記憶體管理 : memory_target=0 and sga_target=0 指定 share_pool_size 、db_cache_size 等 sga 引數
自動 PGA 管理 : memory_target=0 and workarea_size_policy=auto and PGA_AGGREGATE_TARGET=值
手動 PGA 管理 : memory_target=0 and workarea_size_policy=manal 然後指定 SORT_AREA_SIZE 等 PGA 引數，一般不使用手動管理PGA。
但是11g推出了自動記憶體管理（AMM）新特性，該特性引入後，雖然減輕了DBA手動設定共享記憶體的負擔，shared pool和buffer cache及其它幾個component可以根據需要自動調整大小,避免ora-4031的錯誤，但經常出現在shared pool和buffer cache之間發生頻繁shrink/grow操作的現象，在一些高併發環境下，會刷出一批共享池物件，並間歇性持有shared pool latch,library cache lock等共享池latch，從而引發資料庫效能問題的風險，極端情況下，會導致資料庫效能短時間內極速下降。而且如果一旦刷出共享池物件，就會引起資料庫大量遊標失效，隨後的解析會導致大量library cache及cursor等待事件。這也是為什麼在AWR的前臺等待事件中伴隨著大量的cursor:pin s wait on X等待事件的原因。
SQL> set linesize 600 SQL> col component for a25 SQL> col oper_type for a15 SQL> col oper_mode for a10 SQL> col parameter for a25 SQL> col initial_size for 999999999999 SQL> col final_size for 99999999999 SQL> select component, 2 oper_type, 3 oper_mode, 4 parameter, 5 initial_size, 6 target_size, 7 final_size, 8 status, 9 start_time, 10 end_time as changed_time 11 from V$SGA_RESIZE_OPS 12 where to_char(end_time,'yyyy-mm-dd hh')='2020-12-31 12' 13 order by end_time; COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME CHANGED_TIME ------------------------- --------------- ---------- ------------------------- ------------- ----------- ------------ --------- ------------------- ------------------- shared pool SHRINK DEFERRED shared_pool_size 19327352832 1.8790E+10 18790481920 COMPLETE 2020-12-31 12:38:59 2020-12-31 12:40:42 DEFAULT buffer cache GROW DEFERRED db_cache_size 51002736640 5.1540E+10 51539607552 COMPLETE 2020-12-31 12:38:59 2020-12-31 12:40:42 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 51539607552 5.1003E+10 51002736640 COMPLETE 2020-12-31 12:40:42 2020-12-31 12:40:44 shared pool GROW IMMEDIATE shared_pool_size 18790481920 1.9327E+10 19327352832 COMPLETE 2020-12-31 12:40:42 2020-12-31 12:40:44 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 51002736640 5.0466E+10 50465865728 COMPLETE 2020-12-31 12:40:44 2020-12-31 12:40:47 shared pool GROW IMMEDIATE shared_pool_size 19327352832 1.9864E+10 19864223744 COMPLETE 2020-12-31 12:40:44 2020-12-31 12:40:47
可以看到在12:38-12:40出現了sharepool增長和buffer cache的shrink，buffer cache會刷出部分物件，會導致一些SQL語句被重新硬解析。
備註：buffercache的大小可以從v$sga_dynamic_components進行查詢
然後我們再觀察AWR的SGA元件明細
從AWR報告看到，KGH: NO ACCESS 型別記憶體佔用已經接近600M左右。記憶體引數僅僅配置了memory_target，沒有配置SHARED_POOL_SIZE, DB_CACHE_SIZE等。KGH: NO ACCESS 是記憶體在shared pool和db cache之間調節的一箇中間狀態，正常情況不超過100-200M，而且很快消失，記憶體調節過於頻繁導致卡死在KGH: NO ACCESS，進而可能導致可用shared pool不足，導致資料庫出現效能問題。
3.問題處理
通過以往的經驗看，SGA_TARGET的穩定性高於memory_target，可以考慮不使用memory_target，而是用SGA_TARGET和pga_aggregate_target的組合。
所以建議如下：
1.關閉自動記憶體擴充套件，採用 手工共享記憶體管理或者自動共享記憶體的方式，但是需要注意的是Disable AMM/ASMM也可以作為一個方法,但是缺點是: 碰到ora-4031的機率會比自動記憶體管理大.
2.設定SHARED_POOL_SIZE, DB_CACHE_SIZE，確保這些池有一個最小值，從而減少過度調節。
3.設定alter system set "_memory_broker_stat_interval"=999; 降低調節頻率,設定resize的頻率不能少於16分鐘
4.重啟例項，清空當前異常記憶體分配。
最後，我們採用的方式是，使用ASMM方式，將大頁啟用
SQL> show parameter target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 0 memory_target big integer 0 parallel_servers_target integer 32 pga_aggregate_target big integer 50G sga_target big integer 280G SQL> show parameter db_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 100G SQL> show parameter shared_pool_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_pool_size big integer 60G
大頁使用情況
[oracle@xsdbd31 ~]$ grep -i huge /proc/meminfo AnonHugePages: 1587200 kB HugePages_Total: 143380 HugePages_Free: 13567 HugePages_Rsvd: 13548 HugePages_Surp: 0 Hugepagesize: 2048 kB
參考連結：
https://blogs.oracle.com/database4cn/3-v3
https://cloud.tencent.com/developer/article/1424411
MOS：ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation ( Doc ID 1127833.1 )
太久沒有寫部落格了，前一陣子被催著來寫，接下來的時光，希望和部落格園的大佬的共享資料庫知識。