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


 

HTML Text

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