星期二, 6月 05, 2012

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

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...