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

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

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 hidden parameters:

• _db_percent_hot_default

• _db_percent_hot_keep

• _db_percent_hot_recycle

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 times

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:
 
SELECT
   obj      object,
   dbarfil  file#,
   dbablk   block#,
   tch      touches
FROM
   x$bh
WHERE
   tch > 10
ORDER BY
   tch desc;
 

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

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 following command:
 
select
   sum(blocks)
from
   dba_data_files;

SUM(BLOCKS)
-----------
     243260

select
   sum(blocks)
from
   dba_extents;

SUM(BLOCKS)
-----------
     321723

The actual number of used blocks can be determined by looking at DBMS_ROWID for actual block addresses, like this:
 
select
   sum(blocks)
from
   dba_data_files;
 

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 "Advanced Oracle 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 buy it for 30% off directly from the publisher.