What are the components of the Oracle Database Memory Architecture

• In Oracle we have three main memory structures:

SGA (System Global Area)

is a large part of memory that all the oracle background processes access.

PGA (Process Global Area)

This is memory that is private to a single process or thread and is not accessible by any other process or thread

UGA (User Global Area)

This is memory that is associated with your session, it can be found in the PGA or SGA depending on whether you are connected to the database via shared server

• Shared Server - the UGA will be in the SGA
• Dedicated Server - the UGA will be in the PGA

Now let's talk about each of them in detail.

SGA (System Global Area)

There are five memory structures that make up the System Global Area (SGA) Shared Pool

• Library cache includes the shared SQL area, private SQL areas, PL/SQL procedures and packages the control structures such as locks and library cache handles.
• Dictionary cache is a collection of database tables and views containing information about the database, its structures, privileges and users.
• The parameter SHARED_POOL_SIZE is used to determine the size of the shared pool, there is no way to adjust the caches independently, you can only adjust the shared pool size.
• The shared pool uses a LRU (least recently used) list to maintain what is held in the buffer, see buffer cache for more details on the LRU. You can clear down the shared pool area by using the following command

    alter system flush shared_pool;

Buffer Cache This area holds copies of read data blocks from the datafiles. The buffers in the cache contain two lists, the write list and the least used list (LRU). The write list holds dirty buffers which contain modified data not yet written to disk. There are 3 buffer caches:

• Default buffer cache, which is everything not assigned to the keep or recycle buffer pools, DB_CACHE_SIZE
• Keep buffer cache which keeps the data in memory (goal is to keep warm/hot blocks in the pool for as long as possible), DB_KEEP_CACHE_SIZE.
• Recycle buffer cache which removes data immediately from the cache after use (goal here is to age out a blocks as soon as it is no longer needed), DB_RECYCLE_CACHE_SIZE.

Redo Buffer

The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily before it is written to disk, this area is normally less than a couple of megabytes in size. These entries contain necessary information to reconstruct/redo changes by the INSERT, UPDATE, DELETE, CREATE, ALTER and DROP commands.

Large Pool

The large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. The large pool can provide large memory allocations

Java Pool

Used to execute java code within the database. Use the parameter JAVA_POOL_SIZE parameter to adjust (default is 20MB) PGA (Process Global Area) The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA. Oracle creates a PGA area for each users session, this area holds data and control information, the PGA is exclusively used by the users session. The PGA is split in to two areas: - Session Information : PGA in an instance running with a shared server requires additional memory for the user's session, such as private SQL areas and other information. - Stack space : The memory allocated to hold a sessions variables, arrays, etc and other information relating to the session.

UGA (User Global Area)

The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. The OLAP page pool is also stored in the UGA. This pool manages OLAP data pages, which are equivalent to data blocks. The page pool is allocated at the start of an OLAP session and released at the end of the session. An OLAP session opens automatically whenever a user queries a dimensional object such as a cube. Note:(OLAP) - Online Analytic Processing. OLAP is characterized by dynamic, dimensional analysis of historical data.