||Oracle Tips by Burleson
Oracle Data Buffer Monitoring
Determining the optimal size for
the data buffers is a critical task for large databases. It is
economically prohibitive to cache an entire database in RAM as
databases grow ever larger, perhaps reaching sizes in the hundreds of
billions of bytes. The difficulty Oracle professionals face is finding
the point of diminishing marginal returns as they allocate additional
RAM resources to the database. Successfully determining that point and
effectively optimizing RAM can save a company hundreds of thousands,
if not millions, of dollars in RAM expenses.
Among the new features that
Oracle provides to aid the administrator in this task is the
v$db_cache_advice view. This view can help predict the benefit of
adding buffers to the data buffer cache. It estimates the miss rate
for twenty potential buffer cache sizes, throughout a range of 10
percent of the current size to 200 percent of the current size. This
tool allows the Oracle DBA to accurately predict the optimal size for
each RAM data buffer. A few examples will help illustrate the process.
In order to use the new view, RAM
memory must be pre-allocated to the data buffers, just as it was in
the Oracle7 x$kcbcbh utility. Setting the init.ora
parameter db_cache_advice to the value of “on” or “ready”
enables the cache advice feature. The DBA can set these values while
the database is running with the alter system command, taking
advantage of the predictive feature dynamically.
However, since the additional RAM
buffers must be pre-allocated before the db_cache_size can use
v$db_cache_advice, the DBA may wish to use the utility only
once to determine the optimal size.
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
Burleson Consulting shares their personal arsenal of
Oracle data dictionary scripts in this comprehensive download. With
decades of experience using Oracle monitoring scripts and Oracle tuning
scripts, BC experts share their secrets for navigating the Oracle data
Packed with 681 ready-to-use Oracle scripts, this is
the definitive collection for every senior Oracle DBA.
It would take man-years to develop these scripts
from scratch, making this download the best value in the Oracle
priced this collection of 681 scripts at $79.95.
You can download them immediately at this link: