1.SHARE_POOL_SIZE
数据字典的命中率计算:
select ((1 - (Sum(GetMisses) / (Sum(Gets) + Sum(GetMisses)))) * 100) "Hit Rate"
from V$RowCache
where Gets + GetMisses <> 0;
数据字典的命中率低于95%可考虑提升SHARE_POOL_SIZE
LIBRARY CACHE(reload)的命中率计算:
select Sum(Pins) "Hits",Sum(Reloads) "Misses",((Sum(Reloads) / Sum(Pins)) * 100)"Reload %"
from V$LibraryCache;
LIBRARY CACHE的命中率计算:
select Sum(Pins) "Hits",Sum(Reloads) "Misses",Sum(Pins) / (Sum(Pins) + Sum(Reloads)) "Hit Ratio"
from V$LibraryCache;
LIBRARY CACHE(reload)的命中率高于1%或LIBRARY CACHE命中率低于95%可考虑增大SHARE_POOL_SIZE
2.db_cache_size
data cache hit ratio计算:
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,sum(decode(name,'consistent gets', value,0)) con_gets,
(1 - (sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0)) +
sum(decode(name,'consistent gets',value,0))))) * 100 hitratio
from v$sysstat;
一般可让命中率保持95%以上
可通过查看V$DB_CHCHE_ADVICE视图,确定要不要调整:
NAME SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR
-------------------- ----------------- ----------- -------------------------
DEFAULT 4 .1667 1 .8136
DEFAULT 8 .3333 1.0169
DEFAULT 12 .5 1.0085
DEFAULT 16 .6667 1
DEFAULT 20 .8333 1
DEFAULT 24 1 1
由上可看出ESTD_PHYSICAL_READ_FACTOR达到1时SIZE_FOR_ESTIMATE 为16,而当前的值为(SIZE_FACTOR)列24,因此可考虑适当减少DB_CACHE_SIZE.