oracle 10g 4031

標籤：wait   doc   rac   snap   pat   VID   收縮   dba   HERE   

oracle 10g慢慢退出了，但用的地方也還有。這裡再記一下案例：
資料庫16:58出現大量4031，導致資料庫無法使用，取對應時段的AWR

發現shared_pool一直在收縮；
查看日誌：
** 2018-07-26 16:58:09.868
ORA-00604: 遞迴 SQL 層級 1 出現錯誤
ORA-04031: 無法分配 32 位元組的共用記憶體 ("shared pool","select count() from sys.job...","sql area","tmp")

查看對應的trc:

LIBRARY CACHE STATISTICS:namespace           gets hit ratio      pins hit ratio    reloads   invalids-------------- --------- --------- --------- --------- ---------- ----------CRSR           889957320     0.245 2974322326     0.877    6674225    3570108

出現大量的cursor無法共用；
並伴隨出現：

  last wait for ‘SGA: allocation forcing component growth‘ blocking sess=0x0000000000000000 seq=62360 wait_time=7064 seconds since wait started=0          =0, =0, =0  Dumping Session Wait History   for ‘SGA: allocation forcing component growth‘ count=1 wait_time=7064          =0, =0, =0

現察subpool:

Memory Utilization of Subpool 2     Allocation Name          Size   _________________________  __________"free memory              "  -2020262480 

由於系統已重啟，只能檢查現在的resize情況：

set linesize 1000;SELECT start_time,        component,        oper_type,        oper_mode,        initial_size/1048576 "INITIAL MB",        final_size/1048576   "FINAL MB",        end_time FROM   v$sga_resize_ops WHERE  component IN ( ‘DEFAULT buffer cache‘, ‘shared pool‘ )        AND status = ‘COMPLETE‘ ORDER  BY start_time,           component; 

發現調整還是很頻繁；

檢查曆史的sga分配情況：發現故障時段大量的記憶體配置了kgh:no access
select * from DBA_HIST_SGASTAT where name in (‘buffer_cache‘,‘sql area‘,‘KGH: NO ACCESS‘) and snap_id>40630

解決辦法：
1 打補丁
Patch 7189722: APPSST GSI 10G : VERY FREQUENT GROW/SHRINK SGA RESIZE OPERATION HAPPENING

2 禁用ASMM功能；
參見MOS：
How To Prevent The Growth Of The Component ‘KGH: NO ACCESS‘ In The Shared Pool When ASMM Is Enabled (Doc ID 451960.1)
Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" Memory Allocation [Video] (Doc ID 801787.1)
3 依舊啟用ASMM，但設定buffer cache/shared pool的最小值.

4 調整_memory_broker_stat_interval的值，減少sga 的auto resize頻率；

oracle 10g 4031