Memory Structures

Oracle consists of three memory structures known as :
* System Global Area (SGA): This is a large, shared memory segment that virtually all
Oracle processes will access at one point or another.

* Process Global Area (PGA): This is memory that is private to a single process or thread,
and is not accessible from other processes/threads.

* User Global Area (UGA): This is memory associated with your session. It will be found
either in the SGA or the PGA depending on whether you are connected to the database
using shared server (then it will be in the SGA), or dedicated server (it will be in the PGA, in the process memory

1. System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance. SGA is dynamic and is sized by SGA_MAX_SIZE parameter.
a. SGA consists of several memory structures such as:
i. Shared Pool:Used to store most recently executed SQL statements and most recently
used data definitions. Sized by the parameter SHARED_POOL_SIZE. It Consists of two
key performance-related memory structures:

- Library Cache: It stores information about the most recently used SQL and
PL/SQL statements and enables the sharing of commonly used statements. It is
managed by a least recently used (LRU) algorithm. Consists of two structures: Shared
SQL area, Shared PL/SQL area. Its size determined by the Shared Pool sizing

- Data Dictionary Cache: A collection of the most recently used definitions in the
database. Includes information about database files, tables, indexes, columns, users,
privileges, and other database objects. During the parse phase, the server process looks
at the data dictionary for information to resolve object names and validate access.
Caching data dictionary information into memory improves response time on queries
and DML. Size determined by the Shared Pool sizing.

ii. Database Buffer Cache: Stores copies of data blocks that have been retrieved from the
datafiles. Enables great performance gains when you obtain and update data. Managed
through an LRU algorithm. DB_BLOCK_SIZE determines primary block size and can be
managed dynamically.

iii. Redo Log Buffer: Records all changes made to the database data blocks. Primary
purpose is recovery. Changes recorded within are called redo entries. Redo entries contain
information to reconstruct or redo changes. Size defined by LOG_BUFFER

iv. Other structures (for example, lock and latch management, statistical data)

b. Two other additional memory structures that can be configured within the SGA:
i. Large Pool: An optional area of memory in the SGA. Relieves the burden placed on the
Shared Pool. Used for: (a) Session memory (UGA) for the Shared Server (b) I/O server
processes (c) Backup and restore operations or RMAN (d) Parallel execution message
buffers - PARALLEL_AUTOMATIC_TUNING set to TRUE. Does not use an LRU list.
Sized by LARGE_POOL_SIZE

ii. Java Pool: Services parsing requirements for Java commands. Required if installing and
using Java. Sized by JAVA_POOL_SIZE parameter.

2. Program Global Area (PGA) & User Global Area (UGA):
Memory reserved for each user process connecting to an Oracle database. Allocated when a process is created. Deallocated when the process is terminated. Used by only one process. The PGA is a process-specific piece of memory, memory specific to a single operating system process or thread.The PGA is never allocated in Oracle's SGA-it is always allocated locally by the process or thread. PGA memory management is controlled by the database initialization parameter WORKAREA_SIZE_POLICY and may be altered at the session level.

UGA is your session's state. It is memory that your session must always be able to get to.

• If you connected via a shared server, then the UGA must be stored in a memory structure that every shared server process has access to-and that would be the SGA.Every one can read and write your session's data.
• If you are using a dedicated server connection UGA will be be contained inthe PGA of your dedicated server
  

The other areas of PGA memory are generally used for in-memory sorting, bitmap merging, and
hashing. There are two ways to manage this other non-UGA memory in the PGA:

• Manual PGA memory management, where you tell Oracle how much memory is it allowed to use to sort and hash any time it needs to sort or hash in a specific process. Parameters used in manual memory management are:

a. SORT_AREA_SIZE: The total amount of RAM that will be used to sort information
before swapping out to disk. This will be allocated out of PGA.
b. SORT_AREA_RETAINED_SIZE: The amount of memory that will be used to hold
sorted data after the sort is complete. This will be in your UGA.
c. HASH_AREA_SIZE: The amount of memory your server process would use to store
hash tables in memory. These structures are used during a hash join, typically when
joining a large set with another set. The smaller of the two sets would be hashed into
memory and anything that didn’t fit in the hash area region of memory would be stored
in the temporary tablespace by the join key.

• Automatic PGA memory management, where you tell Oracle how much memory it should attempt to use system wide. It is a new way to manage PGA memory was introduced that
  avoids using the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE parameters. It addresses few issues:

a. Ease of use
b. Manual allocation was a “one-size-fits-all” method:
c. Memory control

Setting up of automatic PGA memory management will include configuration of below
initialization parameters:

a. WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which
will use the sort area and hash area size parameters to control the amount of memory
allocated, or AUTO, in which case the amount of memory allocated will vary based on
the current workload present in the database. The default and recommended value is
AUTO.
b. PGA_AGGREGATE_TARGET: This parameter controls how much memory the
instance should allocate, in total, for all work areas used to sort/hash data. Its default
value varies by version and may be set by various tools such as the DBCA. In general, if
you are using automatic PGA memory management, you should explicitly set this
parameter.

Note: In Oracle9i, when using a shared server connection, you can only use manual PGA memory management

Reference: Thomas, Kyte. Expert Oralce Database Architecture.