Database Memory & 4030/4031 (MOSC)

MOSC Banner

what is the best query to get buffer cache hit ratio in 9i AS V$SYSTATS & V$BUFFER_POOL_STATISTICS B

edited Oct 8, 2009 2:38AM in Database Memory & 4030/4031 (MOSC) 2 commentsAnswered

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)';

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center