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 Oracleor 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 Oracleand 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 Oracleand 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 Oracleand
Specific block size caches - Beginning in Oracle, 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. Oracleand above has memory settings for
2K, 4K, 8K, 16K, and 32K caches. The configuration parameter names
are in the pattern of db_nk_cache_size .
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
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.