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.
