System Global Area (SGA)

Published   Read time 2 min(s) 24 sec(s)   (2991 views).  Database  

The SGA is the Oracle structure that is located in shared memory. It contains static data structures, locks, and data buffers. Sufficient shared memory must be available to each Oracle process to address the entire SGA.

The maximum size of a single shared memory segment is specified by the shmmax (shm_max on Tru64 UNIX) kernel parameter.

The following table shows the recommended value for this parameter, depending on your platform.

PlatformRecommended value
Solaris 4294967295 or 4 GB minus 16 MB Note: The value of the shm_max parameter must be at least 16 MB for the Oracle Database instance to start. If your system runs both Oracle9i Database and Oracle Database 10g instances, then you must set the value of this parameter to 2 GB minus 16 MB. On Solaris, this value can be greater than 4 GB on 64-bit systems.
Linux Half the size of the physical memory installed on the system.
AIX NA
HP-UX The size of the physical memory installed on the system.
Windows Estimated 40% of physical memory installed on the server
Mac OS X Half the size of the physical memory installed on the system
True64 UNIX see Solaris

If the size of the SGA exceeds the maximum size of a shared memory segment (shmmax or shm_max), then Oracle Database attempts to attach more contiguous segments to fulfill the requested SGA size. The shmseg kernel parameter (shm_seg on Tru64 UNIX) specifies the maximum number of segments that can be attached by any process. Set the following initialization parameters to control the size of the SGA:

  • DB_CACHE_SIZE
  • DB_BLOCK_SIZE
  • JAVA_POOL_SIZE
  • LARGE_POOL_SIZE
  • LOG_BUFFERS
  • SHARED_POOL_SIZE

Alternatively, set the SGA_TARGET initialization parameter to enable automatic tuning of the SGA size.

Use caution when setting values for these parameters. When values are set too high, too much of the physical memory is devoted to shared memory. This results in poor performance.

An Oracle Database configured with Shared Server requires a higher setting for the SHARED_POOL_SIZE initialization parameter, or a custom configuration that uses the LARGE_POOL_SIZE initialization parameter. If you installed the database with Oracle Universal Installer, then the value of the SHARED_POOL_SIZE parameter is set automatically by Oracle Database Configuration Assistant. However, if you created a database manually, then increase the value of the SHARED_POOL_SIZE parameter in the parameter file by 1 KB for each concurrent user.

Determining the Size of the SGA

You can determine the SGA size in one of the following ways:

  • Run the following SQL*Plus command to display the size of the SGA for a running database: 
    SQL> SHOW SGA
    The result is shown in bytes.
  • When you start your database instance, the size of the SGA is displayed next to the Total System Global Area heading.
  • On systems other than Mac OS X, run the ipcs command as the oracle user.