Describe the Memory Structures that make up the System Global Area.
Memory Structures that make up System Global Area
The acronym SGA stands for System Global Area. Because it's a shared memory structure, and because the word shared
also begins with an S, people sometimes refer to the SGA as the Shared Global Area. This is such a common occurrence that Oracle even makes mention of it in their manuals. I always use the correct term, but not everyone will. So if you hear someone talk about the Shared Global Area, just mentally translate that to System Global Area.
Automatic Memory Management
Oracle strongly recommends the use of automatic memory management to manage the memory on your system. Automatic memory management enables Oracle Database to automatically manage and tune the instance memory. Automatic memory management can be configured using a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The database tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Before setting any memory pool sizes, consider using the automatic memory management feature of Oracle Database. If you must configure memory allocations, consider using the Memory Advisor for managing memory
Considering Multiple Buffer Pools
A single default buffer pool is generally adequate for most systems. However, users with detailed knowledge of an application's buffer pool might benefit from configuring multiple buffer pools. With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be atypical if it is constantly accessed
(that is, hot) or infrequently accessed (for example, a large segment accessed by a batch job only once a day).
Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is associated with a cache, all blocks from that object are placed in that cache. Oracle Database maintains a DEFAULT buffer pool for objects that have not been assigned to a specific buffer pool. The default buffer pool is of size DB_CACHE_SIZE. Each buffer pool uses the same Least Recently Used (LRU) replacement policy (for example, if the KEEP pool is not large enough to store all of the segments allocated to it,
then the oldest blocks age out of the cache).
By allocating objects to appropriate buffer pools, you can:
Reduce or eliminate I/Os
Isolate or limit an object to a separate cache
Databases and Instances
Many Oracle practitioners use the terms instance and database interchangeably. In fact, an instance and a database are different entities, but they are still related. This distinction is important because it provides insight into Oracle's architecture. In Oracle, the term database refers to the physical storage of information, and the term instance refers to the software executing on the server that provides access to the information in the database. The instance runs on the computer or server; the database is stored on the disks attached to the server.
The database is physical: it consists of files stored on disks.
The instance is logical: it consists of in-memory structures and processes on the server.
For example, Oracle uses an area of shared memory called the System Global Area (SGA) and a private memory area for each process called the Program Global Area (PGA). An instance can be part of one and only one database, although multiple instances can be part of the same database. Instances are temporal, but databases, with proper maintenance, last forever. Users do not directly access the information in an Oracle database. Instead, they pass requests for information to an Oracle instance.
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.
Database Buffer Cache: Holds data blocks read from disk
Keep Buffer Pool: Data blocks that are kept in memory
Recycle Buffer Pool: Data blocks that are recycled as soon as possible
Default Buffer Pool: Data blocks managed in the default fashion
Redo Log Buffer: Holds redo log entries waiting to be written to disk
Fixed SGA: Contains general information about the state of the database and the instance
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
Shared SQL Area: SQL statements and execution plans
PL/SQL code, packages, procedures, and functions
Control Structures: Library cache, Locks, cache handles, etc.
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.
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:
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.
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.
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:
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.
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.
Main Title:
Shared Pool
Library Cache:
Shared SQL Area
Parsed SQL Statements
SQL Execution Plans
Parsed and Compiled PL/SQL Program Units
Private SQL Area
(Shared Server Only)
Data Dictionary Cache:
Dictionary Data Stored in Rows
Server Result Cache:
SQL Query Result Cache
PL/SQL Function Result Cache
Other:
(No additional data provided)
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.