The ability to compute the optimal size of the data buffers is a critical task for large databases. As databases grow to hundreds of billions of bytes, it becomes economically impractical to cache the entire database in RAM. So Oracle professionals must find the point of diminishing marginal returns for the addition of RAM resources. Being able to do this can save the company hundreds of thousands of dollars in RAM expenses.

Oracle9i introduces a new view, v$db_cache_advice, that can predict the benefit of additional data buffers in the data buffer cache. This view shows the estimated miss rates for twenty potential buffer cache sizes, ranging from 10 percent of the current size to 200 percent of the current size. This allows Oracle DBAs to accurately predict the optimal size for the RAM data buffer. Let’s look at some examples to illustrate this process.

How does it work?
With the data buffer set to a very small size, a small increase to the size of the RAM data buffers results in a large reduction in Disk I/O (Figure A).

Figure A
Reduction in disk I/O from an increase to RAM data buffer

However, the high reduction in Disk I/O does not continue ad infinitum. As the RAM size approaches the database size, the marginal reduction in Disk I/O is smaller (Figure B) because all databases have infrequently accessed data.

Figure B
The declining benefit of additional RAM data buffers

As a general rule, all available memory on the host should be tuned, and RAM resources should be given to db_cache_size up to a point of diminishing returns (Figure C). There is a point where the addition of buffer blocks will not significantly improve the buffer hit ratio, and this gives the Oracle DBA the ability to find the optimal amount of buffers.

Figure C
The optimal size of the RAM data buffer

This new DB cache advice feature is very similar to the Oracle7 utility that predicted the benefit from adding additional data buffers. This utility used a view called x$kcbrbh to track buffer hits and the x$kcbcbh to track buffer misses.

Just like the Oracle7 model, you must preallocate the RAM memory for the data buffers to use this functionality. The cache advice feature is enabled by setting the init.ora parameter, db_cache_advice, to the value on or ready. These values are set dynamically with the alter system command, so the DBA can turn on the predictive model while the database is running.

Since you must preallocate the additional RAM data buffers for the db_cache_size to use db_cache_advice, you may only want to use this utility once to determine an optimal size. Remember, you can also use the data buffer cache hit ratio to gather similar data.

Once the db_cache_advice is enabled and the database has run for a representative time period, the query in Listing A can be run to perform the prediction.

The output from Listing A is presented in Listing B. Note that the range of values is from 10 percent of the current size up to double the current size.

Here, you can see no peak in total Disk I/O and no marginal trends with the addition of more RAM buffers. This is very typical of data warehouse databases that read large tables with full-table scans. Consequently, there is no specific optimal setting for the db_cache_size parameter. In other words, Oracle has an insatiable appetite for data buffer RAM, and the more you give to db_cache_size, the less Disk I/O will occur.

The general rule for adding blocks to db_cache_size is simple: As long as marginal gains can be achieved from adding buffers and you have the memory to spare, you should increase the value of db_cache_size. Increases in buffer blocks increase the amount of required RAM memory for the database, and it is not always possible to hog all the memory on a processor for the database management system. So a DBA should carefully review the amount of available memory and determine the optimal amount of buffer blocks.

A word of warning: When the DBA sets dba_cache_advice=on, Oracle will steal RAM pages from the shared pool RAM area, often with disastrous result to the objects inside the library cache. For example, if the existing setting for db_cache_size is 500 MB, Oracle will steal a significant amount of RAM from the shared pool. To avoid this problem, the DBA should set db_cache_advice=ready in the init.ora file. When this is done, Oracle will preallocate the RAM memory at database startup time.

For more sophisticated Oracle9i databases, you can control not only the number of buffer blocks but also the block size for each data buffer. For example, you might want to make some of the buffer blocks very large so that you can minimize I/O contention. Remember, the cost for an I/O for a 32 KB block is not significantly more expensive than an I/O for 4 KB block. A database designer might choose to make specific data buffers large to minimize I/O if the application clusters records on a database block, while keeping other data blocks small.

Make a prediction
With the increasing flexibility and sophistication of Oracle9i, the database administrator is challenged to determine the optimal sizes of all SGA regions. Making these decisions properly can mean up to millions of dollars in savings of RAM resources. The DBA cache advice facility is just one more way that the DBA can leverage upon the intelligence of the Oracle9i database to predict the optimal size for the RAM data caches.