René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Hit Ratio

It is sometimes believed that a database is fast if the number of blocks that are read from memory is maximized. The term that is related to that idea is Hit Ratio and is measured in percents. Ideally, the Hit Ratio is 100% (which would mean that all data ever accessed from the database resides in memory).
However, this believ is usually wrong.
Although the hit ration should not be used for tuning, here's the formula for Hit Ratio is for completness' sake: 100% * (total blocks read from memory) / (total blocks read). This is the same as 100% * (1 - (blocks read from harddisk)/(total blocks read))
Oracle uses the term physical reads for blocks read from harddisk. total blocks read is made of two components: db blocks gets and consistent gets. So, the formula becomes: 100% * (1-(physical reads/(db block gets + consistent gets))).
According to that formula, we can query v$sysstat to get the Hit Ratio: 
select 100 * (1-(pr.value/(bg.value + cg.value))) || '%' as "Hit Ratio", 
   pr.value "Phys R", 
   bg.value "Bl. Gets", 
   cg.value "Cons Gets"
 from 
   v$sysstat pr, 
   v$sysstat bg, 
   v$sysstat cg 
 where 
   pr.name='physical reads' and 
   bg.name='db block gets'  and 
   cg.name ='consistent gets';