|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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';