Database Memory Structures

Introduction to Oracle Database Memory Structures

Oracle Database uses memory to store information such as the following:

• Program code
  
• Information about a connected session, even if it is not currently active
  
• Information needed during program execution (for example, the current state of a query from which rows are being fetched)
  
• Information that is shared and communicated among Oracle Database processes (for example, locking information)
  
• Cached data (for example, data blocks and redo log entries) that is also permanently stored on storage devices
  

Basic Memory Structures

The basic memory structures associated with Oracle Database include:

• Software code areas
  
  Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from users' programs—a more exclusive or protected location.
  
  
• System global area (SGA)
  
  The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
  
  
• Program global area (PGA)
  
  A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use database initialization parameters to set the size of the instance PGA, not individual PGAs.

SGA Component

The SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up. The SGA's size and function are controlled by initialization (INIT.ORA or SPFILE) parameters.

The common components are:

• Data buffer cache        -  cache data and index blocks for faster access.
• Shared pool                  -  cache parsed SQL and PL/SQL statements.
• Dictionary Cache         -  information about data dictionary objects.
• Redo Log Buffer          -  committed transactions that are not yet written to the redo log files.
• JAVA pool                    -  caching parsed Java programs.
• Streams pool                -  cache Oracle Streams objects.
• Large pool                    -  used for backups, UGAs, etc.

Here are two methods that can be used to determine the current SGA's size. All values are in bytes: 

SQL> SHOW SGA
Total System Global Area  638670568 bytes
Fixed Size                   456424 bytes
Variable Size             503316480 bytes
Database Buffers          134217728 bytes
Redo Buffers                 679936 bytes

SQL> SELECT * FROM v$sga;
NAME                      VALUE
-------------------- ----------
Fixed Size               456424
Variable Size         503316480
Database Buffers      134217728
Redo Buffers             679936

The size of the SGA is controlled by the DB_CACHE_SIZE parameter.

SQL> select * from v$sgainfo;
NAME                             BYTES                  RESIZEABLE 
-------------------------------- ---------------------- ---------- 
Fixed SGA Size                   2109352                No   
Redo Buffers                     13533184               No    
Buffer Cache Size                3103784960             Yes 
Shared Pool Size                 822083584              Yes  
Large Pool Size                  67108864               Yes 
Java Pool Size                   134217728              Yes  
Streams Pool Size                134217728              Yes 
Shared IO Pool Size              0                      Yes 
Granule Size                     16777216               No 
Maximum SGA Size                 4277059584             No 
Startup overhead in Shared Pool  251658240              No 
Free SGA Memory Available        0      

Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

PGA Component

The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.

Auto tuning

PGA areas can be sized manually by setting parameters like hash_area_size, sort_area_size etc.

To allow Oracle to auto tune the PGA areas, set the WORKAREA_SIZE_POLICY parameter to AUTO and the PGA_AGGREGATE_TARGET to the size of memory that can be used for PGA.

This feature was introduced in Oracle 9i.

Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

PGA usage statistics: 

select * from v$pgastat;

Determine a good setting for pga_aggregate_target: 

select * from v$pga_target_advice order by pga_target_for_estimate;

Show the maximum PGA usage per process: 

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

Share: