Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Tips

Data Block Caching in the SGA

When an SQL statement makes a row request, Oracle first checks the internal memory to see if the data is already in a data buffer, thereby avoiding unnecessary disk I/O.  Now that very large SGAs are available with 64-bit versions of Oracle, small databases can be entirely cached, and one data RAM buffer can be defined for each database block. 


For databases that are too large to be stored in data buffers, Oracle has developed a Touch Count algorithm to retain the most popular RAM blocks, and the touch count algorithm is an approximation of the LRU algorithm. Certain types of uses, like the full table scan, do not add to the touch count, so that blocks keep loosing the touch count and the probability of them being replaced increases significantly.  Blocks maintain so called “touch count” and only blocks with touch count lower than prescribed by an undocumented parameter are eligible for replacement. While it is not exactly a queue structure, as was the case with the proper LRU method, latch contention is significantly reduced, as no LRU queue latches are needed.


When the data buffer does not have enough room to cache the whole database, Oracle utilizes a least recently used algorithm that selects pages to flush from memory.  Oracle assigns each block in the data buffer an in-memory control structure, and each incoming data block is placed in the middle of the data buffer.  Every time the block is requested, it moves to the front of the buffer list, shifting all other RAM blocks toward the age out area.  Data blocks referenced infrequently will eventually reach the end of the data buffer, where they will be erased thereby making room for new data blocks, as shown in Figure 14.1.


Figure 14.1:Aging data blocks from the RAM block buffer


Oracle 7 always placed incoming blocks at the most recently used end of the buffer.  Beginning with Oracle8, Oracle provided three separate pools of RAM, the KEEP, RECYCLE, and DEFAULT pools, in the db_cache_size region to hold incoming data blocks.  With Oracle8i, Oracle dramatically changed the way data blocks were handled within the buffers by inserting them into the midpoint of the block and dividing the block into HOT and COLD areas.


With Oracle10g, the highly efficient technique of prioritizing data blocks within the buffers has been combined with the additional flexibility of multiple block sizes.


To view the current database buffer parameters, SQL*Plus can be used to issue the show parameters buffer command.  A list of parameters from an Oracle8i database is shown below.


SQL> show parameters buffer


NAME                                TYPE    VALUE

----------------------------------- ------- -----------------------buffer_pool_keep                    string  500

buffer_pool_recycle                 string

db_block_buffers                    integer 6000

log_archive_buffer_size             integer 64

log_archive_buffers                 integer 4

log_buffer                          integer 2048000

sort_write_buffer_size              integer 32768 

sort_write_buffers                  integer 2      

use_indirect_data_buffers           boolean FALSE


This output shows the KEEP pool (buffer_pool_keep), the RECYCLE pool (buffer_pool_recycle) and the DEFAULT pool (db_cache_size).  The same listing for an Oracle10g database is shown below.  Note the re-naming of db_block_buffers to db_cache_size.


SQL> show parameters buffer


NAME                                 TYPE    VALUE

------------------------------------ ------- ------

buffer_pool_keep                     string

buffer_pool_recycle                  string

db_block_buffers                     integer 0

log_buffer                           integer 524288

use_indirect_data_buffers            boolean FALSE


Full Table Caching in Oracle10g

The large RAM region within Oracle8i made it possible to fully cache an entire database.  Before Oracle introduced 64-bit versions, the maximum size of the SGA was 1.7 gigabytes on many UNIX platforms. With the introduction of 64-bit addressing, there is no practical limitation on the size of an Oracle SGA, and there are enough data buffers for the DBA to cache the whole database.


The benefits of full data caching become clear when the savvy DBA recalls that retrieving data from RAM is an order of magnitude faster than reading it from disk.  Access time from disks is expressed in milliseconds, while RAM speed is expressed in nanoseconds.  In Oracle10g, RAM cache access is at least 100 times faster than disk access.


If the DBA intends to fully cache an Oracle database, there must be careful planning.  The multiple data buffer pools are not needed, and most DBAs cache all the data blocks in the DEFAULT pool. In general, any database that is less than 20 gigabytes is fully cached, while larger databases still require partial data buffer caches.  The DBA can issue the following simple command to calculate the number of allocated data blocks:


SQL> select

  2     sum(blocks)

  3  from

  4     dba_data_files;






SQL> select

  2     sum(blocks)

  3  from

  4*    dba_extents






As the database grows, the DBA must carefully monitor the buffers in order to increase the db_cache_size to match the database size. Another common approach is to use solid-state disks (RAM-SAN) and use a small data buffer.


This technique insures that all data blocks are cached for reads, but write activity still requires disk I/O.  With RAM becoming cheaper each year, the trend of fully caching smaller databases will continue.



This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.