RelationalDBDesign RelationalDBDesign

Data Buffer Cache   «Prev  Next»
Lesson 3Calculate the data buffer hit ratio
ObjectiveCalculate the buffer hit ratio.

Calculate the Data Buffer Hit Ratio

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.

Table Cache Directive
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.

Non Full Table Scan Data Block

Calculating the Buffer Cache Hit Ratio

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.
Table 4-3 Statistics for Calculating 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');