Tip

Tuning the database cache hit ratio


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 >

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.