SGA (System Global Area)

Hey Folks,

This post is the continuation of my previous post " Oracle database architecture".  In This post We will know few facts about System Global Area (SGA).

SGA (SYSTEM GLOBAL AREA):

System Global Area is also known as Shared Global Area.

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Therefore, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA consist of several memory structure :

• Shared pool
• Database buffer cache
• Redo Log buffer
• Other Structures (For example, Lock and latch management , Statistical data) 

There are three additional memory structures that can configured within the SGA.

• Large Pool  (Optional)
• Java Pool
• Stream Pool

The SGA is also called the shared global area. It is used  to store database information that is shared by database processes. it contains data and control information for the oracle server and is allocated in the virtual memory of the computer where oracle resides.

The fallowing statement can be used to view SGA memory allocation. We use below this command to show SGA configuration of the  oracle instances.

SQL> show sga

Total System Global Area 1.0689E+10 bytes

Fixed Size                  2235904 bytes

Variable Size            4664066560 bytes

Database Buffers         6006243328 bytes

Redo Buffers               16928768 bytes

Dynamic SGA :

Beginning with Oracle 9i, A Dynamic SGA  implements an infrastructure  that allows the SGA configuration to change without shutting down the instance. This then allows the size of the Database Buffer Cache, Shared Pool, and Large Pool to be changed without shutting down the instance. 

So the Database Buffer Cache  and Shared Pool could be initially under configured and would grow and shrink depending upon their respective work loads, up to a maximum if  SGA_MAX_SIZE.

The SGA_MAX_SIZE Initialization Parameter:

The SGA includes a number of memory components, which are pools of memory used to ensure and fulfill the memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger SGAs. Some platform dependencies arise. For example, on 32-bit Windows, the granule size is 8 M for SGAs larger than 1 GB.

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter. If the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file (SPFILE) is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

Sizing the SGA:

The Size of the SGA is determined by several initialization parameters. The Parameters that most affect SGA size are :

• DB_CACHE_SIZE :  The size of the cache of standard blocks. The default is 48 MB on UNIX and 52 MB on NT (Windows).

• LOG_BUFFER :  The number of bytes allocated for the Redo Log Buffers that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file. 

       The log buffer size depends on the number of redo strands in the system. One redo strand is allocated for every 16 CPUs and has a default size of 2 MB. Oracle allocates a minimum of 2 redo strands per instance. When the log buffer size is not specified, any remaining memory in  the redo granules is given to the log buffer.

• SHARED_POOL_SIZE :  SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool The shared pool contains shared cursors, stored procedures, control structures, and other structures. 

If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution  message buffers from the shared pool. Larger values improve performance in multi user systems. Smaller values use less memory. 

• LARGE_POOL_SIZE : The large pool size allocation is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers.                      

          Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.   

   The Default value is Zero (Unless the the init.ora parameter     PARALLEL_AUTOMATIC_TUNING is set to TRUE, then       the default is Automatically  Calculated.

• JAVA_POOL_SIZE : JAVA_POOL_SIZE specifies (in bytes) the size of the Java pool, from which the Java memory manager allocates most Java state during run-time execution.

• STREAM_POOL_SIZE :  Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value.

If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the  STREAMS_POOL_SIZE parameter.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to  10% of the shared pool from the buffer cache to the Streams pool.

Therefore, the size of the SGA can not be exceed SGA_MAX_SIZE minus the value for DATABASE_BUFFER_CACHE,LOG_BUFFER,SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE and STREAM_POOL_SIZE

Hey Folks, If this post is informative then please fallow, like and Subscribe.

Thank You !!.