Re: Cache Hit Ratio definition?

In article <693ecs$7b9$1_at_postern.mbnet.mb.ca>,

   NO_SPAM.jizwang_at_cc.umanitoba.ca (Dr. J. Wang) wrote:
>
>HI all, I have been using Hit Ratio for a while and never paid extra
 attention
>to its formular until recently. I got confused by definitions from different
>sources for database Cache Hit Ratio (CHR).
>
>consistent gets 399366
>db block gets 3134
>physical reads 290511
>
>From Oracle course I took. It was defined by
>
>CHR=100*(db block gets + consistent reads) / (db block gets + consistent
 reads
> + physical reads)
>
>logical reads = (db block gets + consistent reads)
>
>then the CHR for my DSS = 58%
>
>on the other side, from a book titled "Advanced Oracle Tuning and
>Administration" by Aronoff, Loney and Sonawalla from Oracle Press. CHR was
>defined by
>
>CHR=100*(db block gets + consistent reads - physical reads) / (db block gets
 +
> consistent reads)
>
>then CHR = 27.8%
>
>Mathematically, those two formulea are NOT identical. What is really going on
>here or I am missing something? To me the first formular makes more sense: it
>measures proportion of logical reads over the total.

I think this is a wrong conclusion: the first formula measures the proportion of the total logical reads over the sum of logical and physical reads. It does not seem to make sense to add logical and physical reads since all physical reads are also logical reads.
(I hope this is clear)

>The second one measures
>proportions of actual reads from the database buffers over the total logical
>reads. When following tuning guideline of 80%, which one should be used?

Exactly. It measures the percentage of logical reads that do not require a physical read. This is what we call a cache hit ratio.

Lets take your figures as an example. Lets say you have 400.000 logical reads and 300.000 physical reads. This means that only 100.000 logical reads are read out of the cache. This is only 25% of the total logical reads, so your cache hit ratio is 25%.

Regards,

Stefan.

--
Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.