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;

SUM(BLOCKS)

-----------

     217360

SQL> select

  2     sum(blocks)

  3  from

  4*    dba_extents

SUM(BLOCKS)

-----------

     127723

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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm