Today let me brief one of the Oracle9i new features, which helped me in many projects to determine optimal size of the data buffers in large databases. The new view, v$db_cache_advice, that can predict the benefit of additional data buffers in the data buffer cache.

This v$db_cache_advice 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 and tune buffer cache accordingly.

During the load testing face of implementation, I used to enable the v$db_cache_advice with the db_cache_advice parameter. The values are ready, on and off, and they can be set in the init.ora file, or dynamically enabled with an “alter system set db_cache_advice” command.

Metalink  Note:148511.1 says that the v$db_cache_advice view contains these columns, and the the main predictor (estimated physical read factor), is the ratio of estimated disk reads for each of the listed cache sizes:

  • id – This is the ID number for the buffer pool, (values from 1 to 8).
  • name – Oracle allows for multiple data buffer pools as set by (db_cache_size, db_keep_cache_size, db_recycle_cache_size, and the instantiated blocksize buffers db_2k_cache_size, db_4k_cache_size, db_8k_cache_size, db_16k_cache_size and on non-Windows platforms, db_32k_cache_size).
  • block_size – The blocksize for the data buffer (2k,  4k, 8k, 16k and 32k).
  • advice_status – On of Off.
  • size_for_estimate – This is the baseline cache size for the prediction. (e.g. 100m)
  • buffers for estimate – The number of data buffers (e.g. 512).
  • estd_physical_read_factor – The ratio of the number of estimated physical reads to the number of reads in the real cache.
  • estd_physical_reads – This is the guess about the number of disk reads for each listed cache size (from 0.1 to 2.0).

Also there is a downside, Metalink  Note:148511.1 says that the there will be additional CPU overhead when running the data buffer cache advisor, and that the largest resource overhead would be the 100 bytes of additional RAM overhead that is applied to each buffer whenever the utility is invoked (or when you have set  db_cache_advice=on or db_cache_advice= ready)

Reference: Note:148511.1


Viewers of this post also viewed...