Inside the Oracle SGA regions

Oracle tips by Burleson Consulting

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, please CT 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 byDb_block_buffersParameter in Oracle8i and below, andDb_cache_sizeIn Oracle9i and above.

• Keep buffer cache-Beginning with oracle8, a DBA can assign objects to a special cache that will retain those objects requested blocks in RAM for as long as the database is up.KeepCache's main function is to hold frequently referenced Lookup tables that shoshould always be kept in memory for quick access.Buffer_pool_keepParameter controls the size of this cache in oracle8, whileDb_keep_cache_sizeParameter handles the cache in Oracle9i and above.KeepPool is a sub-pool of the default buffer cache.

• Recycle buffer cache-Imagine the opposite ofKeepCache, and you haveRecycleCache. When large table scans occur, the data filling a memory cache is unlikely to be needed again, And shocould be quickly discarded from Ram. By placing this data intoRecycleCache, it will neither occupy valuable memory space nor prevent blocks that are needed from being placed in a buffer. However, shocould it be requested again, the discarded data is quickly available.Buffer_pool_recycleParameter controls the size of this cache in oracle8 and below, whileDb_recycle_cache_sizeParameter 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 2 K, 4 K, 8 K, 16 K, and 32 K caches. the configuration parameter names are in the patternDb_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. theShared_pool_sizeParameter 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.Large_pool_sizeParameter 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.Java_pool_sizeParameter 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.Log_bufferConfiguration 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.