what is the best query to get buffer cache hit ratio in 9i AS V$SYSTATS & V$BUFFER_POOL_STATISTICS B
Hi
I am running these two queries to get buffer cache hit ratio and these is a big difference in the output for the two queries. I am not sure which one is giving the true picture:
SQL> SELECT Round((1-(phy.value - (phy1.value + phy2.value))/ (cur.value + con.value -(phy1.value +
phy2.value)))*100,2) "Cache Hit Ratio"
2 FROM v$sysstat cur, v$sysstat con, v$sysstat phy ,v$sysstat phy1,v$sysstat phy2
3 WHERE cur.name = 'db block gets'
4 AND con.name = 'consistent gets'
5 AND phy.name = 'physical reads'
6 and phy1.name ='physical reads direct'
7 and phy2.name = 'physical reads direct (lob)';