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' /
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 >
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 firstname.lastname@example.org 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