RelationalDBDesign RelationalDBDesign

DB Creation   «Prev 

Monitoring the cache hit ratio

The key performance statistic related to the database buffer cache is the cache hit ratio.
This tells you how often Oracle has to read a data block from disk as opposed to finding it already in memory, in the database buffer cache. The greater the percentage of blocks found in memory, the better your performance will be.
The following SQL query will return the cache hit ratio for a running Oracle database:

SELECT 1- (c.value / (a.value + b.value))
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE = 'db block gets'
  AND = 'consistent gets'
  AND = 'physical reads';

You normally need to be logged in as SYS or SYSTEM in order to run this query. It is generally accepted that a value of 0.90 or higher represents a well-tuned system. If your cache hit ratio is below 0.90, and you have the memory to spare, you might try increasing the db_block_buffers parameter to see whether you get an improvement.