Buffer Hit Ratio :-
Buffer hit ratio notes:
- Consistent Gets - The number of blocks accesses made to the block buffer to retrive data in a consistent mode.
- DB Blk gets - The number of blocks accessed via single block gets.
- Physical Reads - The cumulative number of blocks read from disk.
- Logical reads are the sum of consisent gets and db block gets.
- the db block gets statstic value is incremented when a block is read for update and when segment header blocks are accessed.
- Hit ratio should be > 80% , else increase db_block_buffers in init.ora
sql>select sum(decode(name, 'consistent gets',value,0)) "consistent gets",
sum(decode(name,'db block gets',value,0)) "db block gets",
sum(decode(name,'physical reads',value,0)) " physical reads",
round((sum(decode(name,'consistent gets',value,0)) +
sum(decode(name,'db block gets',value,0)) - sum(decode(name,'physical reads',value,0))) /
(sum(decode(name,'consistent gets',value,0)) +
sum(decode(name,'db block gets',value,0))) * 100,2) "Hit Ratio" from v$sysstat;
Data Dict Hit Ratio :-
- Gets - Total number of requests for information on the data object.
- Cache Misses -- Number of data requests resulting in cache misses.
- Hit ratio should be > 90%,else increse Shared_pool_size in init.ora
Sql> Select sum(gets),sum(getmisses), round((1-(sum(getmisses)/sum(gets))) * 100,2) from v$rowcache;
Library Cache Miss Ratio :-
- Executions - The number of times a pin was requested for objects of this namespace.
- Cache misses - any pin of an object that is not the first pin performed since the object handle was created and which requires loading the object from disk.
- Hit ratio should be <>
Sql> Select sum(pins) executions,sum(reloads) cache_misses,sum(reloads) / sum(pins) miss_ratio from v$librarycache;
