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 dynamic performance view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.
The statistics in Table 7–1 are used to calculate the hit ratio.
Note: With Oracle, physical reads do not necessarily indicate disk reads; physical reads may well be satisfied from the file system cache.
Buffers
Phys I/O Ratio
~0.5
~0.1
Actual Intuitive
A
B
C
Configuring and Using the Buffer Cache
Example 7–1 has been simplified by using values selected directly from the
V$SYSSTAT table, rather than over an interval. It is best to calculate the delta of these statistics over an interval while your application is running, then use them to
determine the hit ratio.
Example 7–1 Calculating the Buffer Cache Hit Ratio SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
Using the values in the output of the query, calculate the hit ratio for the buffer cache with the following formula:
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')
Interpreting and Using the Buffer Cache Advisory Statistics
There are many factors to examine before considering whether to increase or decrease the buffer cache size. For example, you should examine V$DB_CACHE_ADVICE data and the buffer cache hit ratio.
A low cache hit ratio does not imply that increasing the size of the cache would be beneficial for performance. A good cache hit ratio could wrongly indicate that the cache is adequately sized for the workload.
To interpret the buffer cache hit ratio, you should consider the following:
■ Repeated scanning of the same large table or index can artificially inflate a poor cache hit ratio. Examine frequently executed SQL statements with a large number of buffer gets, to ensure that the execution plan for such SQL statements is optimal. If possible, avoid repeated scanning of frequently accessed data by performing all of the processing in a single pass or by optimizing the SQL statement.
■ If possible, avoid requerying the same data, by caching frequently accessed data in the client program or middle tier.
■ Oracle blocks accessed during a long full table scan are put on the tail end of the least recently used (LRU) list and not on the head of the list. Therefore, the blocks are aged out faster than blocks read when performing indexed lookups or small Table 7–1 Statistics for Calculating the Hit Ratio
Statistic Description
consistent gets from cache
Number of times a consistent read was requested for a block from the buffer cache.
db block gets from cache
Number of times a CURRENT block was requested from the buffer cache.
physical reads cache Total number of data blocks read from disk into buffer cache.
See Also: Chapter 6, "Automatic Performance Diagnostics" for more information on collecting statistics over an interval
See Also: Oracle Database Reference for information on the V$SYSSTAT view
Configuring and Using the Buffer Cache
table scans. When interpreting the buffer cache data, poor hit ratios when valid large full table scans are occurring should also be considered.
■ In any large database running OLTP applications in any given unit of time, most rows are accessed either one or zero times. On this basis, there might be little purpose in keeping the block in memory for very long following its use.
■ A common mistake is to continue increasing the buffer cache size. Such increases have no effect if you are doing full table scans or operations that do not use the buffer cache.
Increasing Memory Allocated to the Buffer Cache
As a general rule, investigate increasing the size of the cache if the cache hit ratio is low and your application has been tuned to avoid performing full table scans.
To increase cache size, first set the DB_CACHE_ADVICE initialization parameter to ON, and let the cache statistics stabilize. Examine the advisory data in the V$DB_CACHE_
ADVICE view to determine the next increment required to significantly decrease the amount of physical I/O performed. If it is possible to allocate the required extra memory to the buffer cache without causing the host operating system to page, then allocate this memory. To increase the amount of memory allocated to the buffer cache, increase the value of the DB_CACHE_SIZE initialization parameter.
If required, resize the buffer pools dynamically, rather than shutting down the instance to perform this change.
The DB_CACHE_SIZE parameter specifies the size of the default cache for the
database's standard block size. To create and use tablespaces with block sizes different than the database's standard block sizes (such as to support transportable tablespaces), you must configure a separate cache for each block size used. The DB_nK_CACHE_
SIZE parameter can be used to configure the nonstandard block size needed (where n is 2, 4, 8, 16 or 32 and n is not the standard block size).
Note: Short table scans are scans performed on tables under a certain size threshold. The definition of a small table is the maximum of 2% of the buffer cache and 20, whichever is bigger.
Note: When the cache is resized significantly (greater than 20 percent), the old cache advisory value is discarded and the cache advisory is set to the new size. Otherwise, the old cache advisory value is adjusted to the new size by the interpolation of existing values.
Note: The process of choosing a cache size is the same, regardless of whether the cache is the default standard block size cache, the KEEP or RECYCLE cache, or a nonstandard block size cache.
See Also: Oracle Database Reference and Oracle Database
Administrator's Guide for more information on using the DB_nK_
CACHE_SIZE parameters
Configuring and Using the Buffer Cache