Donald K. Burleson Oracle Tips

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 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 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 Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

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