Determining the hit ratio of Oracle databases

This script is useful for determining the hit ratio of your database. The hit ratio determines if Oracle is performing more physical reads to retrieve the data. If the data being queried is found in the buffer cache, the hit ratio will be higher than if the data is not cached a Oracle has to retrieve it from disk. The original script is by Salim Dawood (1995). Some modifications were added by me later. It has been tested on Oracle 7.3, 8i (8.1.7).

Note: A general rule of thumb for OLTP systmes is that if the hit ratio falls below 70%-80%, the DBA should investigate the cause.

prompt
prompt *********************************************************************
prompt * Hit Ratio = (1 - (physical reads/(db block gets+consistent gets))*100
prompt *********************************************************************
prompt

column log_reads  format 999,999,999,999
column phy_reads  format 999,999,999,999
column phy_writes format 999,999,999,999

select A.value + B.value Log_Reads, 
       C.value Phy_Reads,
       D.value Phy_Writes
from   v$sysstat A, v$sysstat B, v$sysstat C, v$sysstat D
where  A.name = 'db block gets'
and    B.name = 'consistent gets'
and    C.name = 'physical reads'
and    D.name = 'physical writes'
/

Reader Feedback

Paresh Y. writes: This is a pretty old theory and yes hit ratios can be useful if used properly. This caution is missing in the tip which I hope is by oversight and not due to lack of knowledge: e.g., I can have an inefficient query/procedure that is reading the data from the buffer cache again and again and driving the hit ratios to above the 90% range. This will hide the effects of other queries with poor effect on the hit ratio. The solution is if the hit ratio is low there is something definitely wrong (as the tip says correctly) but if the hit ratio is high, that doesn't mean it is time for the DBA to relax. You still need to investigate further if any particular query is driving this ratio to artificially high level.

Stephen W. writes: Interesting tip. You could also add this formula to calculate the percentage:

(C.value/(A.value + B.value))) * 100 Hit_ratio.

I would like to know how to determine the optimum size of buffer cache for our DB.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.