Database Tuning (MOSC)

MOSC Banner

Buffer Cache hit ratio WRONG with V$BUFFER_POOL_STATISTICS on 10gR2

edited Aug 12, 2011 11:50PM in Database Tuning (MOSC) 7 commentsAnswered
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

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