Wednesday, August 29, 2012

Hit Ratio (tested on Oracle 11.2)


Back in the days of Oracle 7, DBA's used to calculate a database’s hit ratio. It was supposed to be greater than 90%. If it was regularly less than 70%, they would increase the value of the DB_BLOCK_BUFFERS initialisation parameter. I don’t know if people do this any more but, if you are ever asked to calculate a database’s hit ratio, you can do it as follows:

SQL> SELECT TRUNC
  2  ((1-(physical_reads/(db_block_gets+consistent_gets)))*100,1)||'%'
  3  Hit_Ratio FROM
  4  (SELECT value physical_reads FROM v$sysstat
  5   WHERE name = 'physical reads'),
  6  (SELECT value db_block_gets FROM v$sysstat
  7   WHERE name = 'db block gets'),
  8  (SELECT value consistent_gets FROM v$sysstat
  9   WHERE name = 'consistent gets');
 
HIT_RATIO
-----------------------------------------
94.1%
 
SQL>

No comments: