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