SYSTEM GLOBAL AREA or SHARED GLOBAL AREA (SGA)

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 SGA is shared among the users. Consequently, the SGA is sometimes also called Shared Global Area.

SGA and Oracle background processes such as (pmon, smon, mmon, arch01, dbwr01 .....) constitutes an Oracle instance. Oracle automatically allocates memory for SGA when you start oracle instance using either PFILE or SPFILE. 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 SGA, and several processes write to the SGA during execution of Oracle.

The SGA is allocated in memory and virtual memory.  The size of the SGA can be established by a DBA thru assigning a value to the parameter “SGA_MAX_SIZE” in the parameter file—this is an optional parameter. The Value of the SGA is also given at the time of Oracle Software Installation.

In Unix/Linux environments it is allocated in  “/etc/sysctl.conf”

       kernel.shmmax = 4294967295 (Bytes)

       kernel.shmmni = 4096

Checking the total and free memory (GB):

free -g

                            total              used       free     shared    buffers     cached

Mem:                   47                  34           12          0              0               6

To find the memory allocated to oracle:

                                                                

ipcs -m | grep ora  (*If the instance is not yet started, it won’t display anything)

key                  shmid         owner    perms      bytes               nattch     status

0x66e7a3e0    1245214    oracle     600         220200960      15

0x099288d0    2097183    oracle     600         6327107584*   56   

*5.89 G (allocated to oracle)

ipcs :Active Inter-Process Communication

m     :Shared Memory Segment

s       :Active Semaphores.

                                                           

To get the information of SGA from database:

Sql>  show  sga

Total System Global Area     6413680640 bytes

Fixed Size                                      2240376 bytes

Variable Size                          1157628040 bytes

Database Buffers                   5100273664 bytes                                     

Redo Buffers                             153538560 bytes

Sql> select   *  from v$sgainfo;

NAME                                                 BYTES              RESIZEABLE

--------------------------------------             -------------------   --------------------

Fixed SGA Size                                         2238528  No

Redo Buffers                                          140206080  No

Buffer Cache Size                               4328521728  Yes

Shared Pool Size                                1090519040  Yes

Large Pool Size                                       67108864  Yes

Java Pool Size                                         16777216  Yes

Streams Pool Size                                                   0  Yes

Shared IO Pool Size                                                0  Yes

Granule Size                                                6777216  No

Maximum SGA Size                             5645373440  No

Startup overhead in Shared Pool          342473512  No

Free SGA Memory Available                                   0

Memory can be managed in database via Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM).

The main difference between ASMM and AMM is that AMM can manage SGA and PGA settings. ASMM can only manage the SGA.

Automatic Shared Memory Management (ASMM): is a pre 11g feature that automatically readjusts the sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size, Streams_pool) based on existing workloads.

When we set a value for SGA_TARGET, Oracle Database automatically sizes the most commonly configured components, including:

• The shared pool (for SQL and PL/SQL execution)
• The Java pool (for Java execution state)
• The large pool (for large allocations such as RMAN backup buffers)
• The buffer cache
• The Streams pool

ASMM is enabled by setting following parameters:                      

• You must use spfile for the initialization parameters.
• sga_target parameter is set to a non-zero value
• statistics_level parameter set to TYPICAL or ALL (Should not be BASIC)
• shared_pool_size must be set to a non-zero value

sga_max_size:  sets the overall amount of memory the SGA can consume but is not dynamic. The sga_max_size parameter is the max allowable size to resize the SGA memory area parameters. The sga_target value can be adjusted up to the sga_max_size parameter, not more than sga_max_size parameter value.

Automatic Memory Management (AMM): is introduced in 11g enables the database to automatically tune the sizes of the individual SGA components and the sizes of the individual PGAs. AMM is the simplest way of managing the memory for the database.

AMM is enabled by setting these two initialization parameters “MEMORY_TARGET” & “MEMORY_MAX_TARGET” in spfile or pfile. The MEMORY_MAX_TARGET parameter specifies max size the MEMORY_TARGET can take memory from the system.

To avoid the commonly occur Error ORA-00845: MEMORY_TARGET not supported on this system on Linux system make sure to configure “/dev/shm” before enabling automatic memory management (AMM). dev/shm is nothing but implementation of traditional shared memory concept an efficient means of passing data between programs.

To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. Set the values of the other automatically sized SGA components to zero. Do this either in PFILE or SPFILE.

To monitor the all resize operations done on SGA and current size of SGA componenets run the following query respectively:

select   component, oper_type,  oper_mode,
              initial_size/1024/1024 "Initial",
              TARGET_SIZE/1024/1024  "Target",
              FINAL_SIZE/1024/1024   "Final",
               status
from      v$sga_resize_ops;

select    component,
              current_size/1024/1024 "CURRENT_SIZE",
              min_size/1024/1024 "MIN_SIZE",
              user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
              last_oper_type "TYPE"
from      v$sga_dynamic_components;