Buffer and Dictonary Hit ratio

— Buffer hit ratio
select (1-(sum(decode(name, ‘physical reads’, value, 0))/
(sum(decode(name, ‘db block gets’, value, 0)) +
(sum(decode(name, ‘consistent gets’, value, 0))))))*100 “Buffer Hit Ratio”
from v$sysstat;

Read Hit Ratio

Buffer hit ratio, ideal value would be 98%-100%. If you are not using automatic shared memory changing db_cache_size could increase performance, if using automatic shared memory changing sga_target and sga_max_size.

— Dictionary hit ratio
SQL> select (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 “Dict Hit Ratio”
from v$rowcache;

Dict Hit Ratio

Ideal percent recommended hit ratio is 95% or higher, increasing shared_pool_size could be increased to increase hit ratio.

Leave a Reply