System Global Area
The System Global Area (SGA) is the memory area that contains data and control information for one Oracle Database instance. When you start a database instance, the amount of memory allocated for the SGA is displayed.
Shared Pool:
The shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool participates in almost every operation that occurs in the database.
Library Cache
The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures. In a shared server architecture, the library cache also contains private SQL areas.
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 . Otherwise, the database must build a new executable version of the application code, known as a hard parse.
SQL Areas
The database represents each SQL statement that it runs in the shared SQL area and private SQL area.
The database uses the shared SQL area to process the first occurrence of a SQL statement. The database automatically determines when applications submit similar SQL statements.
The Shared SQL Area is part of the Shared Pool within the System Global Area (SGA). It is used to store the parsed SQL statements and their execution plans. This area allows multiple users to share SQL statements and execution plans, reducing redundancy and improving performance.
The Private SQL Area is part of the Program Global Area (PGA), which is a memory region allocated to each user session. The Private SQL Area contains session-specific information needed for SQL execution, such as bind variables, runtime buffers, and cursor states.
- The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.
- shows a dedicated server architecture in which two sessions keep a copy of the same SQL statement in their own PGAs. In a shared server, this copy is in the UGA, which is in the large pool or in the shared pool when no large pool exists.
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
Result Cache
- Caches query and PL/SQL function results.
- Returns cached results for identical queries, reducing execution time.
The server result cache is a memory pool within the shared pool. Unlike the buffer pools, the server result cache holds result sets and not data blocks.
A client result cache differs from the server result cache. A client cache is configured at the application level and is located in client memory, not in database memory.
The SQL query result cache is a subset of the server result cache that stores the results of queries. You can enable or disable result caching at the database or statement level
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. The database allocates memory from the shared pool in chunks. Chunking allows large objects (over 5 KB)
Other components:
Include enqueues, latches, Information Lifecycle Management (ILM) bitmap tables, Active Session History (ASH) buffers, and other minor memory structures. Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Examples are: Control file Transaction, Datafile, Instance Recovery, Media Recovery, Transaction Recovery, Job Queue, and so on. Latches are used as a low-level serialization control mechanism used to protect shared data structures in the SGA from simultaneous access.
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.
The Database Buffer Cache can be divided into multiple buffer caches to optimize performance for different types of workloads. The main buffer pools are:
- DEFAULT cache(pool): Used for standard operations.
- KEEP cache: Used to retain frequently accessed blocks in memory.
- RECYCLE cache: Used for blocks that are not expected to be reused frequently.
Buffer States
The database uses internal algorithms to manage buffers in the cache.
Unused(free): The buffer is available for use because it has never been used or is currently unused.
Clean or pinned: This buffer was used earlier and now contains a read-consistent. The block contains data but is “clean”. The database can pin the block and reuse it.
Dirty: The buffer contains modified data that has not yet been written to disk. The database must checkpoint the block before reusing it.
Every buffer has an access mode: pinned or free (unpinned). A buffer is “pinned” in the cache. so that it does not age out of memory while a user session accesses it. Multiple sessions cannot modify a pinned buffer at the same time.
Redo Log Buffer
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, 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 background process log writer process (LGWR) writes the redo log buffer to the active online redo log group on disk
Large Pool
The Large Pool in Oracle Database is an optional component of the System Global Area (SGA) that is used to allocate memory for large memory allocations. It helps to certain memory-intensive operations from the Shared Pool to reduce fragmentation and improve performance.
It can provide large memory allocations for the following:
- UGA for the shared server and the Oracle XA interface (used where transactions interact with other or multiple databases)
- Used by Recovery Manager (RMAN) during backup and restore operations to allocate memory for I/O buffers.
- Allocates memory for user sessions in a shared server environment.
- Allocates memory for message buffers used in parallel query execution.
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 Streams Pool
It’s specifically designed to provide memory for Oracle Streams, which is a feature used for data replication and messaging. In recent versions, the Streams Pool is also used for Oracle Golden Gate Integrated Extract and Integrated Replicate processes.
Fixed SGA
The fixed SGA is an internal housekeeping area. It contains general information about the state of the database and the instance. The Fixed SGA is allocated at instance startup and its size is determined by the operating system and Oracle internal requirements.
Flashback buffer: Is an optional component in the SGA. When Flashback Database is enabled, the background process called Recovery Writer Process (RVWR) is started.
Database Smart Flash cache: Is an optional memory extension of the database buffer cache for databases running on Solaris or Oracle Linux. It can improve response time and overall throughput for both workloads (OLTP) and (OLAP) data warehouse environment. Database Smart Flash Cache resides on one or more flash disk devices, which are solid state storage devices that use flash memory.
In-Memory Area: Is an optional component that enables objects (tables, partitions, and other types) to be stored in memory in a new format known as the columnar format. This format enables scans, joins, and aggregates to perform much faster than the traditional on-disk format, thus providing fast reporting and DML performance for both OLTP and DW environments.
Memoptimize Pool: Is an optional component that provides high performance and scalability for key-based queries. The Memoptimize Pool contains two parts, the memoptimize buffer area and the hash index. Fast lookup uses the hash index structure in the memoptimize pool providing fast access to the blocks of a given table (enabled for MEMOPTIMIZE FOR READ) permanently pinned in the buffer cache to avoid disk I/O. The buffers in the memoptimize pool are completely separate from the database buffer cache. The hash index is created when the Memoptimized Rowstore is configured, and is maintained automatically by Oracle Database.
Shared I/O pool (Secure Files): Is used for large I/O operations on Secure File Large Objects (LOBs). LOBs are a set of data types that are designed to hold large amounts of data. SecureFile is an LOB storage parameter that allows deduplication, encryption, and compression.
