Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Using the V$DB_CACHE_ADVICE View to Tune the Caches

The V$DB_CACHE_ADVICE view is populated when the DB_CACHE_ADVICE initialization parameter is set to ON. The view shows the estimated miss rates for 20 potential buffer cache sizes, ranging from 10 percent to 200 percent of the current size. Each of the 20 projected cache sizes has its own row in this view, with the predicted physical I/O activity that would take place for that cache size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be enabled and disabled dynamically to allow you to collect advisory data for a specific workload. (If this sounds familiar to the old hands out there, it’s probably because you remember the X$KCBRBH and X$KCBCBH tables in days of yore.)

There are two minor overheads associated with this advisory process:

  • CPU load. When the advisory is on, there is a small increase in CPU usage, because additional bookkeeping is required.

  • Memory. The advisory requires memory to be allocated from the shared pool (on the order of 100 bytes per projected buffer). This memory is preallocated on instance startup if DB_CACHE_ADVICE is set to READY in anticipation of collecting advisory statistics, or if the parameter is set to ON. If the parameter is set to OFF (the default setting), on instance startup, then the memory is dynamically allocated from the shared pool at the time the parameter value is modified to a value other than OFF.

The parameter DB_CACHE_ADVICE should be set to ON, and a representative workload should then be run on the instance. Allow the workload to stabilize before querying the V$DB_CACHE_ADVICE view.

The SQL report in Source 13.13 returns the predicted I/O requirement for the default buffer pool for various cache sizes (based on a script taken from the Oracle Performance Guide and Reference, Release 1 (9.0.1), Part # 87503-02, Oracle Corporation, June 2001).

This is an excerpt by Mike Ault’s book “Oracle Administration & Management” .  If you want more current Oracle tips by Mike Ault, check out his new book “Mike Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s Oracle Scripts Download.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter