||Donald K. Burleson
The Oracle SGA Internals pools
Most DBAs know all about the Oracle System Global Area (SGA). The SGA is
Oracle's structural memory area that facilitates the transfer of data and
information between clients and the Oracle database. Long gone are the
days when only four main tunable components existed. If you are using
Oracle9i or above, expect to deal with the following memory regions:
• Default buffer cache – This is the default memory cache that stores data
blocks when they are read from the database. If the DBA does not
specifically place objects in another data cache (which will be covered
next), then any data requested by clients from the database will be placed
into this cache. This memory area is controlled by the db_block_buffers
parameter in Oracle8i and below, and db_cache_size in Oracle9i and above.
• Keep buffer cache - Beginning with Oracle8, a DBA can assign objects to
a special cache that will retain those object’s requested blocks in RAM
for as long as the database is up. The keep cache's main function is to
hold frequently referenced lookup tables that should always be kept in
memory for quick access. The buffer_pool_keep parameter controls the size
of this cache in Oracle8, while the db_keep_cache_size parameter handles
the cache in Oracle9i and above. The keep pool is a sub-pool of the
default buffer cache.
• Recycle buffer cache - Imagine the opposite of the keep cache, and you
have the recycle cache. When large table scans occur, the data filling a
memory cache is unlikely to be needed again, and should be quickly
discarded from RAM. By placing this data into the recycle cache, it will
neither occupy valuable memory space nor prevent blocks that are needed
from being placed in a buffer. However, should it be requested again, the
discarded data is quickly available. The buffer_pool_recycle parameter
controls the size of this cache in Oracle8 and below, while the
db_recycle_cache_size parameter handles the cache in Oracle9i and above.
• Specific block size caches - Beginning in Oracle9i, a DBA can create
tablespaces whose blocksize differs from the overall database blocksize.
When data is read into the SGA from these tablespaces, their data has to
be placed into memory regions that can accommodate their special
blocksize. Oracle9i and above has memory settings for 2K, 4K, 8K, 16K, and
32K caches. The configuration parameter names are in the pattern of
• Shared pool - This familiar area holds object structures and code
definitions, as well as other metadata. Setting the proper amount of
memory in the shared pool assists a great deal in improving overall
performance with respect to code execution and object references. The
shared_pool_size parameter controls this memory region.
• Large pool – Starting in Oracle8, a DBA can configure an optional,
specialized memory region called the large pool, that holds items for
shared server operations, backup and restore tasks, and other
miscellaneous things. The large_pool_size parameter controls this memory
region. The large pool is also used for sorting when the multi-threaded
server (MTS) is implemented.
• Java pool – This area handles the memory for Java methods, class
definitions, etc. The java_pool_size parameter controls the amount of
memory for this area.
• Redo log buffer - This area buffers modifications that are made to the
database before they are physically written to the redo log files. The
log_buffer configuration parameter controls this memory area.
Note that Oracle also maintains a fixed area in the SGA that contains a
number of atomic variables, pointers, and other miscellaneous structures
that reference areas of the SGA.
The above is an excerpt from
Oracle Performance Troubleshooting by Robin
It's only $19.95 and you can order it
and get instant access to the Oracle scripts here: