Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

buffer cache hit ratio

SOLVED
Go to solution
grace white
Advisor

buffer cache hit ratio

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
UNIX/ORACLE DBA
8 REPLIES
twang
Honored Contributor
Solution

Re: buffer cache hit ratio

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
Trusted Contributor

Re: buffer cache hit ratio

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,
Information is Wealth ; Knowledge is Power
twang
Honored Contributor

Re: buffer cache hit ratio

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
Trusted Contributor

Re: buffer cache hit ratio

Thank you TWANG very good script
Information is Wealth ; Knowledge is Power
Jean-Luc Oudart
Honored Contributor

Re: buffer cache hit ratio

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

Hope this help.

Rgds,
Jean-Luc
fiat lux
grace white
Advisor

Re: buffer cache hit ratio

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

UNIX/ORACLE DBA
Graham Cameron_1
Honored Contributor

Re: buffer cache hit ratio

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Hein van den Heuvel
Honored Contributor

Re: buffer cache hit ratio


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.