buffer cache hit ratio

[grace white]

Hi

I am rather new to this so appologies in advance.

What i need to now is how i calculate the buffer cache hit ratio with the following calculation.

Hit ratio = 1 - (physical reads / (db block gets + consistent gets))

the sql i ran is as followed :

Select name, value From v$sysstat
Where name in ('db block gets', 'consistent gets', 'physical reads');

O/P
NAME VALUE
---------------------- ----------
db block gets 245246
consistent gets 13754331
physical reads 3426485

i tried but can not see how when i run a sql script that gives me the answer i get 76% hit ratio


Can any one explain the calculation to me

[twang · Accepted solution]

A better formula in Oracle8i/9i is:

hit ratio =

1 - ( physical reads - (physical reads direct + physical reads direct (lob)) )
--------------------------------------------------------------------------
( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )

[Hari Kumar]

Hi ,
U have everything and query the same, i mean in a right sql query way to get hit ratio.

the query u have given ::
that just selected the values of the those parameters.
They are parameters and and their values.

Use this quesry To get The 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
"Read Hit Ratio"
from v$sysstat;

Thanks,

[twang]

Further take a look at SAG cache:

set pagesize 60
set heading off
set termout off

col lib_hit format 999.999 justify right
col dict_hit format 999.999 justify right
col db_hit format 999.999 justify right
col ss_share_mem format 999.99 justify right
col ss_persit_mem format 999.99 justify right
col ss_avg_users_cursor format 999.99 justify right
col ss_avg_stmt_exe format 999.99 justify right

col val2 new_val lib noprint
select 1-(sum(reloads)/sum(pins)) val2
from v$librarycache
/
col val2 new_val dict noprint
select 1-(sum(getmisses)/sum(gets)) val2
from v$rowcache
/
col val2 new_val phys_reads noprint
select value val2
from v$sysstat
where name = 'physical reads'
/
col val2 new_val log1_reads noprint
select value val2
from v$sysstat
where name = 'db block gets'
/
col val2 new_val log2_reads noprint
select value val2
from v$sysstat
where name = 'consistent gets'
/
col val2 new_val chr noprint
select 1-(&phys_reads / (&log1_reads + &log2_reads)) val2
from dual
/

col val2 new_val avg_users_cursor noprint
col val3 new_val avg_stmts_exe noprint
select sum(users_opening)/count(*) val2,
sum(executions)/count(*) val3
from v$sqlarea
/

set termout on
set heading off

select 'Data Block Buffer Hit Ratio : '||&chr db_hit_ratio,
'Shared SQL Pool ',
' Dictionary Hit Ratio : '||&dict dict_hit,
' Shared SQL Buffers (Library Cache) ',
' Cache Hit Ratio : '||&lib lib_hit,
' Avg. Users/Stmt : '||
&avg_users_cursor||' ',
' Avg. Executes/Stmt : '||
&avg_stmts_exe||' '
from dual
/

[Hari Kumar]

Thank you TWANG very good script

[Jean-Luc Oudart]

Check this link
http://www.ixora.com.au/tips/tuning/cache_miss.htm

Hope this help.

Rgds,
Jean-Luc

[grace white]

Hi
Thanks for getting back to me i am still struglling with this calculation.

1 - ( physical reads - (physical reads direct + physical reads direct (lob)) )
--------------------------------------------------------------------------
( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )


how do i put this formula to practice because i get a total diffrent output.

NAME VALUE
db block gets 419514
consistent gets 21641886
physical reads 5111896


the hit ratio should be 76.83%

can you please give me more details how this is achieved

[Graham Cameron_1]

Grace

As with many oracle-related queries, you will find useful information on AskTom.

The thread I am looking at is
http://asktom.oracle.com/pls/ask/f?p=4950:8:11225327007479099115::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:57412348055,
but if that doesn't work for you, go to
http://asktom.oracle.com/
and enter "buffer cache hit ratio" into the search box, and look at the first url.
I think the thread contains exactly what you want.

-- Graham

[Hein van den Heuvel]

What I'd like to know is WHY do you need to know how to calculate the buffer cache hit ratio.
Will you get a cookie when you figure this out?
And when you come up with a number, how is that going to be used in a meaningfull matter?

Please check out writings like http://www.hotsos.com/ "Are you still using cache hit ratios? - Cary Millsap's follow-up to his '99.9% paper' goes even further to explain why the buffer cache hit ratio is an illegitimate measure of SQL efficiency."

http://www.hotsos.com/dnloads/1.Millsap2001.02.26-CacheRatio.pdf
and
http://www.hotsos.com/dnloads/1.Millsap2001.11.14-LIO.pdf

teasingly yours,
Hein.