Buffer Cache hit ratio WRONG with V$BUFFER_POOL_STATISTICS on 10gR2
I am curious with Buffer Cache hit ratio:On 10gR2
I use:
SQL>
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 "Hit Ratio"
from v$sysstat
/SQL> 2 3 4 5
Hit Ratio
----------
93.2165069
SQL> SELECT name, (1-(physical_reads / (consistent_gets + db_block_gets ) )) * 100 "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS
WHERE (consistent_gets + db_block_gets ) !=0
/
2 3 4
NAME Hit Ratio
-------------------- ----------
DEFAULT 24.0705345
I should use V$BUFFER_POOL_STATISTICS, right?
Change to use:
'physical reads'-'physical reads direct'-'physical reads direct (lob)'
1 - ---------------------------------------------------------------------------------------
'consistent gets' + 'db block gets'
select
I use:
SQL>
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 "Hit Ratio"
from v$sysstat
/SQL> 2 3 4 5
Hit Ratio
----------
93.2165069
SQL> SELECT name, (1-(physical_reads / (consistent_gets + db_block_gets ) )) * 100 "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS
WHERE (consistent_gets + db_block_gets ) !=0
/
2 3 4
NAME Hit Ratio
-------------------- ----------
DEFAULT 24.0705345
I should use V$BUFFER_POOL_STATISTICS, right?
Change to use:
'physical reads'-'physical reads direct'-'physical reads direct (lob)'
1 - ---------------------------------------------------------------------------------------
'consistent gets' + 'db block gets'
select
0