In this advanced Oracle article, I will explore the internal
mechanisms of the Oracle data buffers, the RAM that Oracle
uses to prevent unnecessary rereads of data blocks from disk.
Understanding how Oracle's data buffers operate is an
important key to successfully using them in performance tuning
Prior to Oracle 8i, when a data block was fetched into
the data buffer from disk, it was automatically placed at the
front of the most recently used list. However, this behavior
changed starting in Oracle 8i, when a new data buffer
was placed in the middle of the buffer chain. When tuning a
database, your goal is to allocate as much RAM as possible to
the data buffers without causing the database server to page
in RAM. Whenever the hourly data buffer hit ratio falls below
90 percent, you should add buffers to the block buffers.
The life of a data block
After loading a data block, Oracle keeps track of the touch
count (i.e., the number of times the block is accessed by user
processes) of the data block. If a data block experiences
multiple touches, it is then moved to the head of the most
recently used chain, thereby ensuring that it stays in RAM for
a longer period of time. This new midpoint insertion technique
ensures that the most frequently accessed data blocks remain
at the head of the most recently used chain, because new
blocks move to the head of the chain only if they are
In sum, Oracle 8i data buffer pool management is more
efficient than earlier releases. By inserting new blocks into
the middle of the buffer and adjusting the link based on
access activity, each data buffer is partitioned into two
sections, a hot section that represents the most recently used
half of the data buffer and a cold section that represents the
least recently used half of the buffer. Only those data blocks
that are repeatedly requested will move into the hot areas of
each pool, making each of the data buffers more efficient at
caching frequently used data blocks.
The size of the hot regions is configured using the following
Oracle Corporation officially does not recommend changing
these hidden parameters. Only experienced folks who understand
the internal mechanisms and want to play with the behavior of
their data buffers should use these parameters.
Finding hot data blocks
Oracle 8i maintains an internal X$BH view that
shows the relative performance of the data buffer pools. The X$BH
view has the following columns:
Tim: The amount of time between touches, related
to the _db_aging_touch_time parameter
Tch: The touch count that relates directly to the
promotion of buffers from the cold region to the hot
region after being touched _db_aging_hot_criteria
Since the Tch column tracks the number of touches for a
specific data block, you can write a dictionary query that
displays the hot blocks—those with a touch count greater
than 10—within the buffer, like so:
tch > 10
This advanced query technique is especially useful for
tracking objects in the DEFAULT pool. Once you've located
the hot data blocks, you can move them from the DEFAULT pool
into the KEEP pool.
Fully cached databases
Starting with Oracle 8i and the advent of 64-bit
addressing, you can create a database that is fully cached in
the data buffers. Today, any database with less than 20
gigabytes is commonly fully cached, while larger databases
still require partial data buffer caches.
To appreciate the benefit of full data caching, remember the
time difference between retrieving a data block in RAM vs.
fetching a data block from disk. Access time on disks is
expressed in milliseconds, or thousandths of a second, while
RAM speed is expressed in nanoseconds, or billionths of a
second. In theory, RAM access is therefore faster by several orders of
magnitude, and for Oracle RAM can be thousands of time faster
When fully caching an Oracle database, you need to carefully
plan for the caching and increase the db_block_ buffers
parameter as needed. The multiple data buffer pools are no
longer needed when caching the entire database, so you can
cache all of the data blocks in the DEFAULT data pool.
To calculate the number of allocated data blocks, use the
The actual number of used blocks can be determined by looking
at DBMS_ROWID for actual block addresses, like this:
As the database expands, you must be mindful to increase the
parameter. At database startup time, you'll need to invoke a
script to load the buffers, generally something as simple as
issuing a select count(*) from xxx; because all of the
tables in the database will work. This ensures that all data
blocks are cached, giving read operations a performance boost.
Write activity will still require disk I/O, however. Because
RAM memory costs continue to drop, fully caching a smaller
database can significantly improve performance with little or
no hardware investments.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.