Oracle System Global Area (SGA) – Overview

SGA refers to a Shared Global Area. As the term global implies, this area is accessible to all Oracle processes and users. Each instance will have its own SGA. Oracle processes and users must share large amounts of data. If all of the processes had to get the data from the disk, the IO load would soon render response times that would be totally unacceptable. To prevent this, Oracle uses Global Memory areas, that is, CPU memory. This memory is dedicated to use for Oracle alone. The SGA contains data buffer areas, redo log buffers and the shared pool (context areas). Each area is important to the database overall performance. On Oracle8 an additional area the large pool is also configured.

The Shared pool context areas and Database buffers provide immediate access to data that has been pre-read from either the Data Dictionary tables or the data tables. The Oracle kernel process uses an LRU (Least Recently Used) algorithm to write data back to the disks. Data is never altered on the disks directly, but is altered in memory first.

The redo buffers contain row change information, transaction commit history and checkpoint history. This data is written into the Redo logs and eventually to the Archive logs. A commit will force a disk write as will the filling of a redo log buffer or the reaching of a pre-defined checkpoint.

The queue and request areas store data that is being transferred between processes such as servers and other Oracle processes. The shared SQL area stores all SQL statements in a parsed form. When a user or process issues a SQL command, the shared SQL area is checked to see if the command already exists in parsed form, if it does, this shared version is used. If the multi-threaded server option is utilized, some of the user process global area is also placed in the shared pool.

Large Pool

Oracle8 added an optional structure called the large pool. The large pool area is used for two main purposes:

• Contains the User Global Area (UGA) when sessions are connected using MTS
   
• Buffers for sequential file I/O

Before Oracle8, Oracle stored MTS user connection information in the UGA structure of the shared pool. As the number of users would increase, it was possible for the SGA to run out of space. This additional memory requirement imposed by the use of MTS would reduce the shared pool memory available for the data dictionary cache and the library cache. Oracle now uses the large pool for the session's UGA if the DBA has configured it. The large pool is protected by the shared pool latch and does not use any LRU (least recently used) algorithm to manage space. Therefore, memory must be allocated and freed by each session. An ORA-4031 message will occur if the large pool runs out of memory. You can see the space usage of the large pool by querying the V$SGASTAT view.

Another use of the large pool is as a memory buffering area for slaved DB writers. You use the writers to attempt to mimic asynchronous I/O on various systems that do not provide this feature. Check your system's specific documentation to determine if you might benefit from multiple DB writers.

To configure the large pool in the init.ora, use the LARGE_POOL_SIZE and LARGE_POOL_MIN_ALLOC parameters. The values can be expressed in kilobytes (KB) or megabytes (MB). Keep in mind that in allocating these structures, you are allocating additional memory to the SGA. Be careful that you do not allocate the large pool so that memory starts paging out to disk, because this can have serious performance impacts.

SGA Structure

The DC_ parameters are internally controlled and the DBA cannot control how the cache is partitioned. The cache size is determined empirically from the SHARED_POOL_SIZE parameter.

The Shared pool is a shared resource area that stores the cache information and the parsed images of SQL and PL/SQL procedures, packages and triggers. The cache area can be monitored and if poor hit ratio statistics are shown, increasing the init.ora SHARED_POOL_SIZE parameter will help reduce it. In addition the view V$LIBRARYCACHE helps to monitor the parsed images.

Multiple Buffer Pools

The way objects use blocks in the SGA depends on the nature of the object (table, index, and so forth) and the way the object is used (for example, an object may be a small, static, but constantly used lookup table, or a large, static, but rarely used data warehouse table). The different usage characteristics of these objects often demand different types of storage. Some objects should be kept in memory as long as possible and others should be cleared out of memory soon after use. To deal with these types of situations, Oracle8 provides a method of allowing more granular control over the SGA storage of database blocks and how long blocks are maintained in memory. Oracle8 provides three pools for database data block storage:

• Default buffer cache
   
• KEEP buffer pool
   
• RECYCLE buffer pool

Each of these pools allows you to assign specific database objects to them and to retain the blocks from those objects using different retention criteria. The KEEP buffer pool and the RECYCLE buffer pool segment memory from the default buffer pool, so be careful that you do not leave the default buffer pool short of memory. DBAs may use the V$BUFFER_POOL view to monitor the various buffer pool settings currently in use.

The Default Buffer Pool

The default buffer pool (still referred to as the database buffer cache) is always allocated in an Oracle database. To allocate memory to the default buffer pool, set the number of database buffer blocks you want the cache to be and do so by setting the DB_BLOCK_BUFFERS setting in the init.ora parameter file. The total size of this cache then is calculated as DB_BLOCK_BUFFERS * DB_BLOCK_SIZE. The LRU algorithm manages the data blocks in the default buffer pool.

The KEEP Buffer Pool

A CACHE option is available on database tables. This option causes database blocks that are read in from a table during a full table scan to be put onto the MRU (most recently used) end of the LRU (least recently used) list, as opposed to the LRU end. Thus, the data blocks associated with that table remain in the database buffer cache for a longer period of time before being aged out. The idea is to allow for as close to a 100 percent cache hit ratio as possible on these often-hit, smaller tables. The KEEP buffer pool gives you a dedicated segment of database buffer cache memory in which to load the blocks of these tables. Typically, these are smaller tables, such as lookup tables.

You can set the BUFFER_POOL_KEEP parameter in init.ora to reserve part of the database buffer cache for pinned data blocks. Once the KEEP buffer pool is created, you can assign objects to this pool, and if the pool is sized properly, the data blocks from these objects will remain in memory. You must exercise caution when setting up the KEEP buffer pool. Ensure that you allocate only enough memory to store the blocks you wish kept in the buffer pool, and yet not over-allocate to the pool memory that will go unused. You determine how much memory to allocate to the pool by adding up the sizes of all the objects you intend to assign to the pool. Keep in mind that as you allocate memory to the pool, you reduce the amount of memory available in the database buffer cache to the default pool. If you undersize the pool, objects will be aged out using the LRU algorithm, as with the default buffer pool. An example of assigning a table to the KEEP buffer pool is shown below.

CREATE TABLE small_table
   (pk_id        NUMBER    NOT NULL    PRIMARY KEY,
    table_info   VARCHAR2  NOT NULL)
STORAGE (INITIAL 1M, NEXT 1M, BUFFER_POOL KEEP)

The RECYCLE Buffer Pool

The RECYCLE buffer pool's purpose is to store memory blocks that are not likely to be reused again soon. In the case of very large objects, access to individual blocks may be very random and scattered. In these circumstances, you may wish to assign such objects to the RECYCLE buffer pool.

It is important not to size the RECYCLE buffer pool too small. Doing so may cause blocks to age out of the pool before an application or SQL statement uses them completely. To configure the RECYCLE pool, set the BUFFER_POOL_RECYCLE parameter in the init.ora file.

If the block is aged out before the transaction is done with it, it needs to be re-read, causing more I/O. You can determine if this is happening by using one of the trace methods (for example, turning on trace and using tkprof to format the output trace file). To do so, compare disk accesses that occur in the default pool to disk access occurring in the RECYCLE buffer pool. In both cases, disk accesses should be the same. If the statement that ran and that was using the RECYCLE buffer pool has more disk accesses, you may conclude that the pool is too small. Another method of monitoring the pool is to monitor the free buffer waits and log file sync statistics. If either of these starts to grow after you allocate the RECYCLE buffer pool, it may mean that you have not allocated enough memory to the pool.

The following is a guide for determining which objects should fit in the RECYCLE buffer pool:

• A good candidate for a segment to put into the RECYCLE buffer pool is one that is at least twice the size of the default buffer pool and has incurred at least a few percent of the total I/Os in the system. 
   
• A good candidate for a segment to put into the KEEP pool is one that is smaller than 10 percent of the size of the default buffer pool and has incurred at least 1 percent of the total I/Os in the system.
   
• Calculate the ratio of blocks for a segment of the object in question that is used frequently to those used rarely. For that object, count the number of blocks that exist in the hot half of the cache (the MRU end) to the number of blocks in the cold half (the LRU end). If the ratio for a segment is close to 1, the segment may be a good candidate for the RECYCLE cache. If the ratio is high (perhaps 3), the segment might be a good candidate for the KEEP cache.

An example of assigning a table to the RECYCLE buffer pool is shown below.

CREATE TABLE small_table
   (pk_id        NUMBER    NOT NULL  PRIMARY KEY,
    table_info   VARCHAR2  NOT NULL)
STORAGE (INITIAL 1M, NEXT 1M, BUFFER_POOL RECYCLE)

SGA Fixed and Variable Portions

The fixed portion of the SGA refers to the area unaffected by the INIT{sid}.ORA parameter values. The fixed portion may however vary between Oracle versions because of additional INIT{sid}.ORA parameters in the newer versions. This size can also vary between different Unix platforms due to different alignment Procedures. The fixed component of SGA is not tunable.

The variable portion of the SGA consists of arrays of variables that are allocated based upon the INIT.ORA parameters.

The list below states the impact that different INIT.ORA parameters have on the variable portion of the SGA:

• DB_FILES – The lower the value of this parameter, the less the space needed. If this is set to 10 the gain in the space is about 6240 Bytes.
   
• DB_FILE_MULTIBLOCK_READ_COUNT – The higher the value for this parameter, the less space needed.
   
• All Dictionary cache parameters ( i.e. all DC_XXX Paratamers ) – Space needed for additional values of these parameters is not too high.
   
• DDL_LOCKS – Space needed for additional value of 100 for this parameter is ~ 5.0k.
   
• DML_LOCKS – Space needed for additional value of 100 for this parameter is ~ 9.76K.
   
• ENQUEUE_RESOURCES – Space needed for additional value of 10 for this parameter is ~ 0.7k.
   
• PROCESSES – Space needed for additional value of 10 for this parameter is ~ 19.5k.
   
• ROW_CACHE_ENQUEUES – Space needed for additional value of 100 for this parameter is ~ 3.5K
   
• SEQUENCE_CACHE_ENTRIES – Space needed for additional value of 10 for this parameter is ~ 1.17k
   
• SEQUENCE_CACHE_HASH_BUCKETS – Space needed for additional value of 10 for this parameter is ~ 0.08k
   
• SESSIONS – Space needed for additional value of 10 for this parameter is ~ 5.3K
   
• TRANSACTIONS – Space needed for additional value of 10 for this parameter is ~ 0.85K.
   
• TRANSACTIONS_PER_ROLLBACK_SEGMENT – More space is needed for lower values of this parameter

Data Block Buffers

The data block buffers are a cache in the SGA used to hold the data blocks read from the data segments in the database (e.g., tables, indexes, and clusters). The size of the data block buffer cache is determined by the INIT.ORA parameter DB_BLOCK_BUFFERS.

The data block buffer cache is fixed in size and is usually smaller than the space used by database segments. This means it cannot hold all of the database's segments in memory at one time. The SGA retains the information stored in its cache buffers in accordance with the Least Recently Used (LRU) algorithm. The LRU algorithm determines what objects haven't been used for a given period of time and allows those objects to be over-written as needed to make way for additional data.

Dictionary Cache

The dictionary cache is used to store information about the database objects. This includes:

• User account data
   
• Datafile names
   
• Segment names
   
• Extent locations
   
• Table descriptions
   
• Privileges

When any of this information is needed by the database, that data dictionary tables are read and the data is returned and stored in the SGA (in the dictionary cache).

The dictionary cache is also managed using the LRU algorithm. The dictionary cache is part of the shared SQL Pool and is managed internally by the database. The size of the cache is set via the init.ora SHARED_POOL_SIZE parameter.

Care should be given not to define the dictionary cache too small. This will result in the database repeatedly querying the data dictionary tables for the information it requires. These are known as recursive hits and are much slower than queries against the dictionary cache in memory.

Redo Log Buffer

Redo log files contain entries that describe the changes that have been made to the database. These entries are used in roll-forward operations during a database recovery. Before the entries are written to the online redo log files, they are first cached in the SGA. This area of the SGA is known as the redo log buffer.

The size of the redo log buffer is set via the LOG_BUFFER parameter in the init.ora file.

Shared SQL Pool

The shared SQL pool is used to store the Dictionary Cache as well as information about SQL statements that are being run against the database. This shared SQL information consists of the execution plan and parse tree for the SQL statement. This enables subsequent (identical) SQL statements to reuse the parse information to accelerate its execution.

SGA Size

The size of the SGA is controlled by buffer sizes, and the buffer sizes are controlled by the database block size, which is specified at database creation and cannot be changed without rebuilding the database. This usually defaults to 2 KB. We usually suggest at least 4KB, and in most cases 8 KB works best. If in doubt, set the block size to the largest supported on your system.

The five major components of the SGA are the database buffers, log buffers, large pool, Java pool, and the shared pool The SGA also contains the redo log buffers. The ideal situation would be to size the SGA to hold the entire database in memory. For small systems, this may be a real situation; for most, it is not feasible. Therefore, you must decide how much to allocate. Many times, especially for development databases, this will be a rough guess. For systems already designed with detailed data storage estimates, it may be better defined. A general rule of thumb for a pure Oracle system (no other applications) is 50 percent to 60 percent of available RAM for your SGA. Note that for small databases this may be overkill. In general, we have found that sizing the SGA data block buffers (the product of DB_BLOCK_SIZE and DB_BLOCK_BUFFERS) to 1/50 to 1/100 of the total physical size of the database is a good starting point. Under Oracle8i, new default sizes in the supplied sample initialization file are more realistic, but in general will still be too small for most production databases, so use them with caution.

Oracle provides tools to analyze buffer performance. Unfortunately, they can only be used once a system is operating and running under a normal load.

If you have no idea whatsoever, make the buffer area at least 60 to 100 MB or so (you will usually outgrow the Oracle default rather quickly) for a database that is near 1 gigabyte in total physical size, and up to 400 MB for one that is around 20 gigabytes in size. For databases smaller than 1 gigabyte physical size, the Oracle defaults may be usable. Make the shared pool at least 20 to 40 MB (not the 9 MB it will default to).

If you overspecify the shared memory size on NT or Open-VMS you may get into a situation known as swapping. This is where all or part of your application is swapped out to disk because physical memory just isn't large enough to hold it all. Needless to say, this has a very negative impact on performance. Usually overspecification of the SGA on UNIX will lead to not being able to start the database.