Throughout Oracle Operations, reads and writes are made to the database buffer cache. If the information required is already stored in memory then any request for it results in a cache hit. If however the information is not, then a cache miss results and this means that the request will have to be satisfied by physical disk I/O. Data retrieval from memory is always preferable as it is quicker, but given the dynamic nature of data required at any particular time, cache misses are not likely to be absent during normal Oracle operations.

The init.ora parameter DB_BLOCK_BUFFERS is used to control the size of the buffer cache. Changes to this parameter will reflect directly on the size of the SGA. The key to tuning this area is to keep the cache hit rate as high as possible while not using too much physical memory.

To investigate the Buffer Hit Rate the following sql may be used:

select (sum(decode(name, 'consistent gets', value,0))

       + sum(decode(name, 'db block gets', value,0))

       - sum(decode(name, 'physical reads', value,0)))

       / (sum(decode(name, 'consistent gets', value,0))

       + sum(decode(name, 'db block gets', value,0))) * 100 " Buffer 

Hit Rate"

from v$sysstat;

A good figure to aim for in an interactive database is around 95%. This can be less for batch oriented databases, but for data warehousing you need to be approaching 100% consistently.