B.Sc. CSIT 7th Semester Database Administration

Oracle Memory Structure

Architecture of Oracle Memory Structure

oracle memory structure

Oracle database creates and uses memory structures for program code, data shared among users, private data areas for each connected user, cached data.

1. System global area

  • A system global area (SGA) is a group of shared memory structures that contain data and control information for one oracle database instance.
  • This is allocated when an oracle instance starts up.
  •  All server and background processes share the SGA. 
oracle memory structure

Fig: Starting Oracle Instance

  • It is also called shared global area.
  • The size of the SGA can be established by a dba by assigning a value to the parameter SGA_MAX_SIZE in the parameter file
  • The SGA consists of following components :
  • Shared pool
  • Database buffer cache
  • Redo log buffer
  • Large pool
  • Java pool
  • Streams pool
oracle memory structure

Fig: Shared Pool Structure

Shared Pool

  • It caches various types of program data.

              E.g.. Parsed SQL, PL/SQL code, system parameters, data dictionary information.

  • The shared pool is involved in almost every operation that occurs in the database.
  • For example, if a user executes a SQL statement, then oracle database accesses the shared pool.
  • Memory can be allocated to the shared pool by the parameter  shared_pool_size in the parameter file.  
  • The default value of this parameter is 8MB on 32-bit Platforms and 64MB on 64-bit Platforms.
  • The shared pool is divided into several subcomponents:
  •  Library Cache
  • The dictionary cache
  • Server result Cache
  • Reserved Pool

Library Cache

  • The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code.
  •  When a SQL statement is executed, the database attempts to reuse previously executed code.
  •  If a parsed representation of a SQL statement exists in the library cache and can be shared, then the database reuses the code, known as a soft parse or a library cache hit. 
  • Otherwise, the database must build a new executable version of the application code, known as a hard parse or a library cache miss.
oracle memory structure

Shared SQL area

  • The database uses the shared SQL area to process the first occurrence of a SQL statement. 
  • This area is accessible to all users and contains the statement parse tree and execution Plan.

Private SQL area in PGA

  • Each session issuing a SQL statement has a private SQL area in its PGA.
  • Each user that submits the same statement has a private SQL area pointing to the same shared SQL area.
  • Many private SQL areas in separate pages can be associated with the same shared SQL area.

Data Dictionary Cache

  • Collection of database tables and views containing reference information about the database, its structures, and its users.
  • Oracle database accesses the data dictionary frequently during SQL statement parsing.
  • The database server manages the size of the data dictionary cache internally 
  •  The size depends on the size of the shared pool in which the data dictionary cache resides. 

Server Result Cache

  • The server result cache holds result sets and not data blocks.
  • The server result cache contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.

1. SQL query result cache

  •  This cache stores the results of queries and query fragments. 

2. PL/SQL function result cache

  •  The PL/SQL function result cache stores function result sets

Reserved Pool

  • The reserved pool is a memory area in the shared pool that oracle database can use to allocate large contiguous chunks of memory.
  • Allocation of memory from the shared pool is performed in chunks. 
  • Chunking allows large objects (over 5 kb) to be loaded into the cache without requiring a single contiguous area. 
  •  In this way, the database reduces the possibility of running out of contiguous memory because of fragmentation.

Database Buffer Cache

  • The database buffer cache stores the actual data blocks that are retrieved from data files by system queries and other data manipulation language commands.
  • A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data
  • The purpose is to optimize physical input/output of data.
  • It uses LRU (least recently used) algorithm, which has hot end and cold end.
  • The database updates data blocks in the cache and stores metadata about the changes in the redo log buffer. 
  • After a commit, the database writes the redo buffers to disk but does not immediately write data blocks to disk. 
  • Instead, database writer (DBW) performs lazy writes in the background.
  • A buffer pool is a collection of buffers. The database buffer cache is divided into one or more buffer pools.
  • Possible Buffer Pools are:

i. Default Pool 

ii. Keep Pool

iii. Recycle Pool 

Redo Log Buffer

oracle memory structure
  • The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database.
  • Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. 
  • Oracle database processes copy redo entries from the user memory space to the redo log buffer in the SGA. 
  • The redo entries take up continuous, sequential space in the buffer.
  • The background process log writer (LGWR) writes the redo log buffer to the active online redo log group on disk.
  • LGWR writes redo sequentially to disk while DBWn performs scattered writes of data blocks to disk

Streams Pool

  • This pool stores data and control structures to support the oracle streams feature of oracle enterprise edition. 
  •  Oracle steams manages sharing of data and events in a distributed environment.
  •  Unless you specifically configure it, the size of the streams pool starts at zero. 
  •  It is sized with the parameter streams_pool_size.
  •  The pool size grows dynamically as required by oracle streams.

Java Pool

  • The Java Pool is an area of memory that stores all session-specific java code and data within the Java Virtual Machine (JVM). 
  • The java pool is an optional memory object, but is required if the database has oracle java installed and in use for oracle JVM (Java Virtual Machine). 
  • The size is set with the java_pool_size parameter that defaults to 24mb.
  • The java pool is used for memory allocation to parse java commands and to store data associated with java commands.

Large Pool

  • The large pool is an optional memory structure that primarily relieves the memory burden Placed on the shared pool.  
  • The large pool is used for the following tasks if it is allocated:
  •  Backup and restore operations by the recovery manager (RMAN) process.
  •  Transactions that interact with more than one database, e.g., A distributed database scenario.
  • The large pool does not have an LRU list. Pieces of memory are allocated and cannot be freed until they are done being used. 
  • As soon as a chunk of memory is freed, other processes can use it.

2. Program Global Area (PGA)

oracle memory structure

  •  This memory is allocated when the server process is started.
  •  A PGA is a memory region that contains data and control information for a server or background process. 
  •  Each server process and background process has its own PGA.
  • The program global area is also termed the process global area (PGA)
  • SQL work areas

A work area is a private allocation of PGA memory used for memory-intensive operations.

  • Private SQL areas 

A private SQL area holds information about a parsed SQL statement and other session-specific information for processing.

oracle memory structure

Fig: PGA Contents

  • The contents of the PGA differ depending on what type of connection is made to the database. 
  • When a user process connects to the database via a dedicated server process, user session data, stack space, and cursor state information is stored in the PGA.
  •  The user session data consists of security and resource usage information;
  • The stack space contains local variables specific to the user session; and
  • The cursor state area contains runtime information for the cursor, including rows

               returned and cursor return codes. 

  • If the user process connects through a shared server process, the session and cursor state information is stored within the SGA
About Author

Karina Shakya