- Introduction
- Performance Impact of the Database Cache
- Obtaining Database Cache Size Advice in Oracle9i
- Dynamic SGA Sizing in Oracle9i
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Obtaining Database Cache Size Advice in Oracle9i
Oracle9i provides a new facility that allows you to obtain advice about sizing your database cache. The DB_CACHE_ADVICE parameter can be set to enable the prediction of the physical reads for database buffer caches of various sizes. The ALTER SYSTEM command can be used to change this parameter dynamically. DB_CACHE_ADVICE can have the following values:
OFF. This setting disables the generation of advice. In addition, the memory for advisory is not allocated. This is the default value.
READY. This setting turns off the generation of advisory information. However, the memory allocated for the advisory is retained.
ON. This setting enables advisory information to be generated. Note that attempting to switch the parameter from the "off" state to the "on" state directly will result in an error. However, if the parameter is in the "ready" state and you set it to "on," the view V$DB_CACHE_ADVICE is reset and it starts gathering new statistics.
The V$DB_CACHE_ADVICE view contains rows that predict the number of physical reads for various buffer cache sizes. This can be used to determine the physical read factor, which is defined as follows:
Physical read factor = number_of_estimated_reads þ number_of_actual_reads
The following SQL queries the V$DB_CACHE_ADVICE view:
column size_for_estimate format 999,999,999,999 heading 'Cache Size (MB)'
column buffers_for_estimate format 999,999,999 heading 'Num Of Buffers'
set echo off feedback off pagesize 50 term off linesize 80
spool buff_cache_advice.txt
select block_size,
size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor
from V$DB_CACHE_ADVICE
where advice_status='ON'
and name='DEFAULT';
spool off
set feedback 6 pagesize 24 term on
clear columns
Sample output of running the above query is shown below:
BLOCK_SIZE CACHE SIZE (MB) NUM OF BUFFERS ESTD_PHYSICAL_READ_FACTOR ---------- -------------- -------------- ------------------------- 8192 6.2031 794 14.29 8192 12.4063 1588 12.36 8192 18.6094 2382 9.18 8192 24.8125 3176 7.91 8192 31.0156 3970 4.81 8192 37.2188 4764 2.48 8192 43.4219 5558 1.28 8192 49.6250 6352 1.21 8192 55.8281 7146 1.12 8192 62.0313 7940 1.00 8192 68.2344 8734 0.92 8192 74.4375 9528 0.82 8192 80.6406 10322 0.79 8192 86.8438 11116 0.78 8192 93.0469 11910 0.77 8192 99.25 12704 0.72 8192 105.4531 13498 0.69 8192 111.6563 14292 0.65 8192 117.8594 15086 0.63 8192 124.0625 15880 0.59
From the above output, it can be seen that the current cache size of 62MB results in a physical read factor of 1.00. Doubling the cache size to 124MB reduces the number of physical reads by approximately 40%; however, reducing the cache size to 10% of its current size (to 6MB) would result in a lot of physical reads (approx 14 times the current number of physical reads). It appears therefore that the cache size should probably be increased to 80MB (resulting in an approximate 20% reduction in physical reads), but definitely the cache size should not be reduced.
Hit Rate + Miss Rate != 100%
When a user issues a request for data, the data block buffer cache of the SGA stores the blocks of data (not always, though, as explained later). These data blocks are stored in memory so that future requests for these data blocks by any user don't necessitate the access of the physical data files. The buffer cache is limited in size and a "least recently used" (LRU) algorithm is used to manage the contents of the cache. When more space is needed in the cache, the LRU algorithm is used to remove the least-recently-used blocks and replace with the new blocks. A small buffer cache would result in a low hit rate—the percentage of time a block request is satisfied by data that is already in the cache and a physical read is avoided. A low hit rate therefore would indicate that the performance will suffer due to high physical read activity.
Cache hit rate is a derived statistic. The most commonly used formula for the buffer cache hit rate in Oracle7.x and 8.0.x is as follows:
Hit ratio = 100 x (1 - ((physical_reads) þ (consistent_gets + db_block_gets)))
However, from Oracle8i onward, the definition of physical reads is changed to include direct reads as well as reads to get the data block into the cache. As a result, the above formula only gives a lower bound. A better formula for Oracle8i/9i is as follows:
Hit ratio = 100 x (1-(((physical_reads)-(physical_reads_direct + physical_reads_direct(LOB))) þ (session_logical_reads)))
Alternatively, the first formula can be used in Oracle8i/9i as well by querying the V$BUFFER_POOL_STATISTICS view.
In general, for online transaction processing (OLTP) applications, the hit rate is expected to be high, say 98%, while for decision support systems (DSS) applications the hit rate is expected to be around 90%. The reason for the hit rate to be higher in OLTP systems compared to DSS systems is that index-based accesses are quite common in OLTP environments.
NOTE
You should not try to tune the database on the basis of hit rates unless the database has been working against standard load for at least four hours.
Oracle DBAs quite often get carried away with determining hit rates and try to get a hit rate of close to 100%. The logic is that a very high hit rate would indicate a properly sized buffer cache. This is true; however, a hit rate of 80% doesn't necessarily mean that you have a poorly performing system! What?? Doesn't it mean that the miss rate is therefore an unacceptable 20%? No. It's possible to have a hit rate of 80% and a miss rate of 0.1% because they don't necessarily add up to 100%. Often DBAs fail to remember this fact and desperately try to get hit rates close to 100%, and don't seem to achieve it in practice.
The reason that hit rate and miss rate for the buffer cache don't add up to 100% is that Oracle performs direct reads for certain operations such as parallel scans and reads from temporary tablespaces. In addition, in Oracle8.1.x the _serial_direct_reads parameter can result in direct reads even for serial scans. During direct reads, the database buffer cache is bypassed and blocks are directly read into private buffers in the program global area (PGA). The result is that blocks are not searched in the cache before being read and hence cache hits don't occur. Further, the blocks are discarded immediately after use and not cached; therefore, subsequent cache hits also don't occur. It's interesting to note that direct reads improve the cache hit rate because direct read blocks are generally infrequently accessed and the cache is not loaded with these blocks. Consequently, the cache contains blocks that have a good chance of being accessed again, resulting in improved hit rates.