Oracle 11g 11.2.0.1 ORA 04031 的解法

某11g DB 一直發生ora 04031 的問題, 造成hang住, 這邊所謂的hang 指的是AP 在連接到DB時 會有ORA 04031的錯誤, 影響服務 , 懷疑又是autotuning 惹的禍 , 經調整後已經穩定一陣子了!


相關參數如下


_shared_pool_reserved_pct


可透過以下修改 緩和shared pool tuning 的速度 , 將shared_pool 管理的機制 模仿9i 的版本

alter system set "_shared_pool_reserved_pct"=10 scope=spfile SID='*';

alter system set "_shared_pool_reserved_pct"=10 scope=memory SID='*';

Verify:

select a.ksppinm "Parameter", 

b.ksppstvl "Session Value", 

c.ksppstvl "Instance Value" 

from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c 

where a.indx = b.indx and a.indx = c.indx 

and a.ksppinm in ('_shared_pool_reserved_pct');

心得:

即使有free shared pool , 也會keep 住 , 而不會被挪用到db buffer 去 (A duration will not encounter memory exhaustion while another duration has free memory.) , 相關的BUG 為 :

Bug (10204505) : SGA AUTOTUNE CAUSING LOTS OF ROW CACHE MISSES, LIBRARY CACHE RELOADS AND PARSING , 在 11.2.0.3 會被fix , 可透過以上參數調整來規避

Bug 10204505 - SGA autotune can cause row cache misses, library cache reloads and parsing [ID 10204505.8]

Modified 14-OCT-2011 Type PATCH Status PUBLISHED

Bug 10204505 SGA autotune can cause row cache misses, library cache reloads and parsing