Hi Tom,
I 've read from Metalink that the Buffer Cache Hit Ratio formula stated in Oracle 8i Tuning Guide is wrong. The formula should be:

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

Is it correct?

I have really not understood that with the same values how can the hit ratio be different using the same formula because this is simple maths.
Or am I interpreting your explanation wrongly

I think I interpreted your reply wrongly. I just went through the explaination on the bug you mentioned on the metalink site. What I understood from the site is as the values are exorbitantly high one has to mask higher byte to get the right values. In this particular case if you convert the values to hexadecimal, hex value has leading 0xFFFFFFFF. Is the criteria to find out overflowing numbers as mentioned in metalink.

Hello Tom !

Consider this:

cr@STARR.LHR> show sga

Total System Global Area 214964252 bytes
Fixed Size 75804 bytes
Variable Size 107335680 bytes
Database Buffers 106496000 bytes
Redo Buffers 1056768 bytes

cr@STARR.LHR> SELECT phy.value "Physical Reads",
2 cur.value "DB Block Gets",
3 con.value "Consistent Gets",
4 1-((phy.value)/((cur.value)+(con.value)))*100 "Cache hit ratio"
5 FROM v$sysstat cur, v$sysstat con, v$sysstat phy
6 WHERE cur.name='db block gets'
7 AND con.name='consistent gets'
8 AND phy.name='physical reads'
9 /

Physical Reads DB Block Gets Consistent Gets Cache hit ratio
============== ============= =============== ===============
4372475 737385 17855646 -22.516741


1 SELECT phy.value "Physical Reads",
2 cur.value "DB Block Gets",
3 con.value "Consistent Gets",
4 1-((v(phy.value))/((v(cur.value))+(v(con.value))))*100 "Cache hit ratio"
5 FROM v$sysstat cur, v$sysstat con, v$sysstat phy
6 WHERE cur.name='db block gets'
7 AND con.name='consistent gets'
8* AND phy.name='physical reads'
9 /

Physical Reads DB Block Gets Consistent Gets Cache hit ratio
============== ============= =============== ===============
4421265 741421 18892425 -21.518589

My Question:

Why i am still getting cache hit ratio in -ve ? V is the name of function u suggested.

CREATE OR REPLACE FUNCTION "CR"."V" ( p_num in number )
return number
as
begin
return to_number(substr( to_char(p_num,'fm'||rpad('0',15,'0')||'x'), 9
), rpad('x',8,'x'));
end;


Regards

Tom,

Please answer only if relevent. Do you think the function that strips the high bytes in calculating the ratio work for all types of OS or version of OS irrespective of whether it is 32 or 64 bit machine?

should work regardless.  you'll know right away if it doesn't ;)

It just takes a number -- turns it into a 16 character hex string and then returns only the last 8 hex char's:

ops$tkyte@ORA920> select to_char(:x,'fm'||rpad('0',15,'0')||'x') from dual;

TO_CHAR(:X,'FM'||
-----------------
fffffff6d6fa3340

ops$tkyte@ORA920> select substr( to_char(:x,'fm'||rpad('0',15,'0')||'x'), 9 ) from dual;

SUBSTR(TO
---------
d6fa3340

ops$tkyte@ORA920> select to_number(substr( to_char(:x,'fm'||rpad('0',15,'0')||'x'), 9 ), rpad('x',8,'x')) result from dual;

    RESULT
----------
3606721344


 

Is it still right is 10 G that Buffer Cache Hit Ratio has no importance. You referred to "Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok" from hotsos.com