Memory Architecture   «Prev  Next»

Lesson 2The System Global Area (SGA)
Objective Describe the Memory Structures that make up the System Global Area.

Memory Structures that make up System Global Area

SGA Alternative Meaning
The System Global Area, or SGA, is the heart of any Oracle instance. All the various Oracle processes communicate with the SGA in one way or another.
  • Memory Structures in SGA:
    The previous module introduced you to some of the major structures in the SGA. This module will go a bit deeper and discuss the topics in more detail. Here is a more detailed version in the diagram below.

SGA Details
  • Database Buffer Cache: Holds data blocks read from disk
    1. Keep Buffer Pool: Data blocks that are kept in memory
    2. Recycle Buffer Pool: Data blocks that are recycled as soon as possible
    3. Default Buffer Pool: Data blocks managed in the default fashion
    1. Redo Log Buffer: Holds redo log entries waiting to be written to disk
    2. Fixed SGA: Contains general information about the state of the database and the instance
    3. Large Pool: An optional memory area used for backup and restore operations
  • Shared Pool: Contains memory structures related to SQL execution
    • Library Cache: Contains the shared SQL area and PL/SQL code
      1. Shared SQL Area: SQL statements and execution plans
      2. PL/SQL code, packages, procedures, and functions
      3. Control Structures: Library cache, Locks, cache handles, etc.
      1. Data Dictionary Cache: An area in the Shared Pool where Oracle stores frequently accessed data dictionary information so that it does not need to be continuously reread from disk.
      2. Control Structures: Shared pool, character set conversion memory, etc.

Purpose of the Database Buffer Cache

The database buffer cache, also called the buffer cache, is the memory area that stores copies of data blocks read from data files. A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users concurrently connected to a database instance share access to the buffer cache.
Oracle Database uses the buffer cache to achieve the following goals:
  1. Optimize physical I/O: 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 the online redo log but does not immediately write data blocks to the data files. Instead, database writer (DBW) performs lazy writes in the background.
  2. Keep frequently accessed blocks in the buffer cache and write infrequently accessed blocks to disk. When Database Smart Flash Cache (flash cache) is enabled, part of the buffer cache can reside in the flash cache. This buffer cache extension is stored on one or more flash disk devices, which are solid state storage devices that uses flash memory. The database can improve performance by caching buffers in flash memory instead of reading from magnetic disk.Use the DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE initialization parameters to configure multiple flash devices. The buffer cache tracks each device and distributes buffers to the devices uniformly.

SGA Structure Terms

The following section discusses more about SGA Structure Terms.
SGA Structure Terms
Data Dictionary Cache Holds frequently accessed data dictionary information
Redo Log Buffer Holds redo log entries waiting to be written to disk
Library Cache Contains the shared SQL area and PL/SQL code
Shared Pool Contains memory structures related to SQL execution
Large Pool An optional memory area used for backup and restore operations

Data Dictionary Cache

The data dictionary is a 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 data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data:
  1. Data dictionary cache: This cache holds information about database objects. The cache is also known as the row cache because it holds data as rows instead of buffers.
  2. Library cache:All server processes share these caches for access to data dictionary information.

  • Redo Log Buffer in Oracle Database 12c:
    The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database. A redo record is a data structure that contains the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes. The 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 process (LGWR) writes the redo log buffer to the active online redo log group on disk.

Shared Pool consisting of I. Library Cache II. 1) Data Dictionary Cache 2) Server Result Cache 3) Other 4) Reserved Pool
  1. Main Title:
    • Shared Pool
  2. Library Cache:
    • Shared SQL Area
      • Parsed SQL Statements
      • SQL Execution Plans
      • Parsed and Compiled PL/SQL Program Units
    • Private SQL Area
      • (Shared Server Only)
  3. Data Dictionary Cache:
    • Dictionary Data Stored in Rows
  4. Server Result Cache:
    • SQL Query Result Cache
    • PL/SQL Function Result Cache
  5. Other:
    • (No additional data provided)
  6. Reserved Pool:
    • (No additional data provided)

Shared Pool consisting of I. Library Cache II.: 1) Data Dictionary Cache, 2) Server Result Cache, 3) Other, 4) Reserved Pool

The next few lessons talk about each SGA memory structure in detail.

SEMrush Software 2SEMrush Software Banner 2