Home > Articles > Data > Oracle

  • Print
  • + Share This
From the author of

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,
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:

 ---------- --------------  -------------- -------------------------
 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.


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.

  • + Share This
  • 🔖 Save To Your Account