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 cur.name = 'db block gets' and
      con.name = 'consistent gets' and
      phy.name = '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 tdichiara@techtarget.com 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.

This was first published in May 2002
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close