DBA

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;