Oracle How To ...
Tuning the buffer cache hit ratio
A logical read occurs whenever a user requests data from the database.
It occurs whenever the data is in the buffer cache or whether the user process
must read it from disk. If the data must be read from disk then a physical
read occurs. Oracle keeps track of logical and physical reads in the
Use the following SQL statement to determine the values required for the hit radio calculation:
select name, value From v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');
The cache-hit ratio can be calculated as follows:
Hit ratio = 1 - (physical reads / (db block gets + consistent gets))
If the cache-hit ratio goes below 90% then:
- For Oracle 8 and earlier: increase the initialisation parameter
- For Oracle 9 onwards: increate the initialisation parameter
I am grateful to Kamil Jakubovic for provided me with a single statement for calculating the cache hit ratio:
100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
v$sysstat v1, v$sysstat v2, v$sysstat v3
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads'
These notes have been tested against Oracle 7.3.4, Oracle 8.0.5, Oracle 8.1.5 and Oracle 9.0.1 and may apply to other versions as well.
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.