DB_CACHE_SIZE指定的是基于主块大小(primary block size)的default缓冲池(buffer pool)的大小
2.该参数至少是4M*CPU个数*grunule大小。
采集统计数据用来预测不同cache size下的性能,用视图v$DB_CACHE_ADVICE查看。
SQL> select component,current_size,user_specified_size,granule_size
2 from v$sga_dynamic_components;
查看是否启用动态buffer cache advisory参数.
SQL> show parameter advice
1.如果statistics_level的值是typical或all,则db_cache_size为on
2.三个值:on、off、ready;
ready是advisory关闭,但是系统为其分配了内存,off->ready->on,正常开启顺序;
ready->off/off->on,报错ORA-4031(inability to allocate from the shared pool)!只有ready->on->off来关闭
2 from v$db_cache_advice;
NOTE: 在factor或reads变化不大的情况下,无需增加buffer cache因为不会带来significant benefit,根据视图
标准的查询语句应为:
select size_for_estimate,buffers_for_estimate,estd_physical_read_fact,estd_physical_reads
from v$db_cache_advice
where name='DEFAULT'
AND block_size=(select value from v$parameter where name='db_block_size')
AND advice_status='ON';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29638498/viewspace-1226295/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29638498/viewspace-1226295/