天天看點

oracle 10g 4031

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頻率;

轉載于:https://blog.51cto.com/snowhill/2153695