As database blocks are retrieved from disk into the database, they are stored in RAM memory in a buffer. The block remains in the buffer until it is overwritten by another database request. At read time, the database first checks to see if the data already resides in the buffer before incurring the overhead of going to the disk. The Slide Show below shows how datablocks that reside in the buffer are aged out after they are used.
Be aware the blocks read from a full-table scan are NOT loaded into the MRU end of the Oracle data buffer.
Instead, full-table scan blocks are loaded into spaces on the LRU end of the Oracle data buffer, where they will age-out separately from blocks that are retrieved by means of indices. The Slide Shows below we see that (FTS) full-table scan data blocks are placed into the LRU side of the data buffer, and age-out faster than other data blocks.
The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the V$SYSSTAT performance view.
Use the buffer cache hit ratio to verify the physical I/O as predicted by the V$DB_CACHE_ADVICE view.
Table 4-3 lists the statistics from the V$SYSSTAT view used to calculate the buffer cache hit ratio.
Example 4-3 shows a query of this view.
Example 4-3 Querying the V$SYSSTAT View
SELECT name, value
WHERE name IN ('db block gets from cache', 'consistent gets from cache',
'physical reads cache');