Thursday, May 29, 2008

SGA (System Global Area) is the fundamental component of an Oracle Instance and is one of two memory areas of an Oracle Server. This memory area contains several memory structures each of which is designated to a specific task required by the Oracle DB during its execution. SGA is dynamic and the allocation is done in granuels. The maximum size of the SGA is stored in the parameter named SGA_MAX_SIZE. The primary memory structures contained by SGA are:-

Shared Pool - this memory structure is used to store most recently executed SQL or PL/SQL statements and most recently used data definitions. For storing these two different types of data, this memory structure is divided into two sub-structures which are Library Cache and Data Dictionary Cache. Library Cache is used to store the most recently executed SQL or PL/SQL statements whereas the Data Dictionary Cache is used to store the most recently executed data definitions. The maximum size of the Shared Pool is stored in the parameter named SHARED_POOL_SIZE.

Database Buffer Cache - this memory structure is used to store the blocks of data recently obtained from the datafiles and it improves the performace to a great extent while fetching or updating the recent data. The size of this cache is stored in the parameter named DB_BLOCK_SIZE.

Redo Log Buffer - this memory structure is used to store all the changes made to the database and it's primarily used for the data recovery purposes. The size of this memory structure is stored in the parameter named 'LOG_BUFFER'.

In addition, there are other memory structures as well including those which are used for lock and latch management, statistical data, etc.

SGA can support two other optional memory structures as well, but it needs to be configured for that. These additional memory structures are:-

Large Pool - This memory structure is used to reduce the burden of the Shared Pool memory structure. This memory structure can be used as the Session memory for the Shared Server and as the temporary storage for the I/O involved in the server processes. This memory structure can also be used for the backup and restore operations or RMAN. This memory structure doesn't use LRU (Least Recently Used) list. The size of this memory structure is stored in the parameter named 'LARGE_POOL_SIZE'.

Java Pool - As the name suggests, this optional memory structure is used Java is installed and used on the Oracle Server. Size of this memory structure is stored in the parameter named 'JAVA_POOL_SIZE'.