Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Tweak Oracle data buffer parameters to cache entire databases
April 8th, 2015 - Donald Burleson


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:
   obj      object,
   dbarfil  file#,
   dbablk   block#,
   tch      touches
   tch > 10
   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:




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





Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational