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
————–
99.1751978

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
————–
91.8405306

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s