Problem solve Get help with specific problems with your technologies, process and projects.

Tuning the database cache hit ratio

A few quick Oracle database cache hit ratio tuning tips.

You need to tune the database cache hit ratio if:

  • the gethitratio on the library cache is > 90%
  • the pinhitratio on the library cache is < 1%
  • the get hit ratio on the dictionary cache is < 15%

Use this simple script to measure the cache hit ratio:

prompt Measuring the Cache Hit Ratio
prompt Increasing the DB_BLOCK_BUFFER 
prompt if cache hit ratio < 90%
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value / (cur.value + con.value)))*100,2)  "% Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where = 'db block gets' and = 'consistent gets' and = 'physical reads' 

Reader Feedback

Tiercelin F. writes: I am no way experienced in Oracle Tuning, but your tip appears quite strange to me. "You need to tune the database cache hit ratio if: the gethitratio on the library cache is > 90%" I would rather say that you need to tune if gethitratio is < 90 % (your script seems to believe it too). As a Sybase ASE tuning engineer, I usually tune the data caches for sensitive data to more than 95%.

Rohit D. writes: There are number of books with much more detail on this subject. However, there should be three separate queries for three separate areas of SGA, namely:

1. For Dictionary / Rowcache hit ratio: (Ideal recommended > 99%)

select sum(gets), sum(getmisses), (1 - (sum(getmisses) / (sum(gets) ))) * 100 Row_Hit_Ratio
from v$rowcache;

2. For Library cache hit ratio: (Ideal recommended > 99%)

select sum(pins) as hits,
 sum(reloads) as misses,
 round((sum(pins-reloads)/sum(pins))*100,2) as Lib_Hit_Ratio
from v$librarycache;

3. For buffer cache hit ratio:

select (1-(sum(decode(name, 'physical reads',value,0)) / (sum(decode(name, 'db block gets', value, 0)) + sum(decode(name, 'consistent gets',value,0))))) * 100 Buf_Hit_Ratio
from v$sysstat;

The only other part of SGA is redo log buffer, which doesn't have any hit ratios to be calculated. It appears that the tip refers only to the last one.

For More Information

  • What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture