Oracle Memory Architecture in detail

Oracle Memory Architecture

The basic memory structures associated with Oracle instances include:

System global Area (SGA): shared by all servers and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL regions.

Program Global Zone (PGA): Individual server processes and background processes are dedicated, each process has a PGA.

The SGA is a shared memory area that contains data and control information for the instance, consisting of the following:

Database buffer cache: used to cache data blocks retrieved from disk
Redo Log buffers: Used to cache redo information until it can be written to disk
Shared pools: used to cache various structures that can be shared among users
Large pools: Optional areas for buffering large I/O requests to support parallel queries, shared servers, Oracle XA, and certain types of backup operations

Java pools: Used to hold session-specific Java code and data in a Java Virtual machine (JVM)
Stream pool: Used by Oracle Streams
Reserved buffer cache: Used to hold data that is kept in the buffer cache as long as possible

Cyclic buffer cache: used to store data that expires quickly in the buffer cache

NK Block Size buffer cache: For data blocks that have different cache sizes from the default database block size, to support transportable table spaces
The size of the database buffer cache, shared pools, large pools, stream pools, and Java pools can be automatically adjusted to the current requirements. Also, these memory buffers, along with the reserved buffer cache, the cyclic buffer cache, and the NK block size buffer cache, can be changed without shutting down the instance.

The Oracle database provides alerts and guidance to identify memory size issues to help determine the appropriate memory parameter values.

The program Global Zone (PGA) is a memory area that contains data and control information for each server process. A server process is a process that processes client requests. Each server process has its own dedicated PGA that is created when the server process starts. Only the server process can access it.

The amount of PGA memory used and the content of the PGA depends on whether the instance is configured in Shared server mode. Typically, the PGA contains the following:

Dedicated SQL zone: contains data such as binding information and run-time memory structure. Each session that emits an SQL statement has a dedicated SQL area.
Session Memory: The memory allocated here is used to hold session variables and other information related to that session.

1. Buffer caches data buffer cache

You can configure the buffer cache by specifying the value of the Db_cache_size parameter. The buffer cache can hold a copy of a chunk of data in a data file that has a block size of db_block_size. The buffer cache is part of the SGA, so all users can share the blocks. The server process reads the data in the data file into the buffer cache. To improve performance, the server process sometimes reads multiple blocks in a single read operation. The data is then written to the data file by the DBWN process from the buffer cache. To improve performance, Dbwn writes multiple blocks in a single write operation.

At any given time, the buffer cache may hold multiple copies of a database block. Although there is only one current copy of the block, the server process may need to construct a read-consistent copy based on past image information in order to satisfy the query. This is called a read consistency (CR) block.

The least Recently Used (LRU) list can reflect the use of buffers. The buffer is sorted by the distance and the reference frequency of the referenced time. As a result, the most frequently used and recently used buffers will be listed on the most recently used one end. The passed-in block is first copied to the least recently used buffer, and the buffer is then assigned to the center of the list as a starting point. Starting from this starting point, the buffer moves up and down the list based on usage.

Buffers in the buffer cache can be in one of the following four states:
Connected: The block is currently being read into the cache or is being written to. Other sessions are waiting to access the block.

Clean: The buffer is not currently connected, and if its current content (block of data) is no longer referenced, it can perform an expired processing immediately. The content is synchronized with the disk, or the buffer contains a read-consistent snapshot of the block.

Idle/Unused: The buffer is in a blank state because the instance was just started. This state is very similar to the "clean" state, except that the buffer has not been used.

Gray: The buffer is no longer connected, but the content (data block) has changed, so the content must first be flushed to disk through dbwn before it can be processed out of date.

The server process uses buffers in the buffer cache, and the DBWN process makes the buffers in the cache available by writing the changed buffers back into the data file. The checkpoint queue lists the buffers that will be written out to disk.

The Oracle database supports multiple block sizes in the same database. The standard block size is used for the SYSTEM table space. The standard block size can be specified by setting an initialization parameter of db_block_size. Its valid value is between 2 KB and 8 KB, and the default value is. The cache size of non-standard block-sized buffers is specified by the following parameters:

Db_2k_cache_size

Db_4k_cache_size

Db_8k_cache_size

Db_16k_cache_size

Db_32k_cache_size

The Db_nk_cache_size parameter cannot be used to adjust the size of the cache for standard block sizes. If the value of Db_block_size is NK, then setting db_nk_cache_size is illegal. The size of a standard block-size cache is always determined by the value of db_cache_size.

Because there is a limit to the size of each buffer cache, it is not always possible to place all data on the disk in the cache. When the cache is full, subsequent cache misses cause the Oracle database to write gray data that is already in the cache to disk to make room for new data. (If there is no gray data in the buffer, you do not need to write to the disk to read the new block into the buffer.) Subsequent access to any data that has been written to the disk results in a recurring cache miss.

The probability that a data request causes a cache hit is affected by the cache size. The larger the cache, the greater the chance of containing the requested data. Therefore, increasing the cache size increases the percentage of data requests that cause cache hits.

A database administrator (DBA) can create multiple buffer pools to improve the performance of the database buffer cache. You can assign it to a buffer pool based on the access of the object. There are three types of buffer pools:

Reserved: This pool is used to preserve objects that may be reused in memory. Keeping these objects in memory can reduce I/O operations. You can keep the buffer in this pool by making the pool larger than the total size of the individual segments assigned to the pool. This means that the buffer does not have to perform expiration processing. A reserved pool can be configured by specifying the value of the Db_keep_cache_size parameter.

Loop: This pool is used for blocks with very little chance of reuse in memory. The size of the loop pool is smaller than the total size of each segment assigned to the pool. This means that chunks that are read into the pool often need to perform outdated processing within the buffer. The loop pool can be configured by specifying the value of the Db_recycle_cache_size parameter.

Default: This pool is always present. It is equivalent to a buffer cache that does not have an instance of the pool and the loop pool, and can be configured with the Db_cache_size parameter.

Note: Memory in a reserved pool or circular pool is not a subset of the default buffer pool.

CREATE INDEX Cust_idx ...
STORAGE (Buffer_pool KEEP ...);

ALTER TABLE Oe.customers
STORAGE (Buffer_pool RECYCLE);

ALTER INDEX Oe.cust_lname_ix
STORAGE (Buffer_pool KEEP);

To manually refresh the memory:
Alter system flush buffer cache;

The Buffer_pool clause is used to define the default buffer pool for an object. It is part of the STORAGE clause and is valid for CREATE and ALTER tables, clusters, and index statements. Blocks in objects that do not explicitly set the buffer pool will enter the default buffer pool.

The syntax is: buffer_pool [KEEP | RECYCLE | DEFAULT].

When you change the default buffer pool for an object by using the ALTER statement, the cached block remains in its current buffer until the normal buffer management activity clears them. Blocks that are read from disk will be placed in the newly specified buffer pool for that segment.

Because multiple buffer pools are assigned to a segment, objects with multiple segments can place blocks in multiple buffer pools. For example, a table organized by index can have multiple different pools on an index segment and an overflow segment.

2. Shared Pool

The size is specified by Shared_pool_size.
The library cache contains statement text, code that has been parsed, and execution plans.
The data dictionary cache contains the definitions of tables, columns, and permissions in the data dictionary table.
The user Global Zone (UGA) contains session information (if an Oracle shared server is used).

Library cache: The library cache contains the shared SQL and PL + SQL areas-representations of the full parsing or compilation of PL/ PL/SQL blocks include:

Procedures and functions

Package

Trigger

Anonymous
PL/SQL block

Data dictionary cache: The data dictionary cache stores the definition of a Dictionary object in memory.

Result cache: The result cache contains the SQL query result cache and the PL/s function result cache. This cache is used to store the results of an SQL query or a/PL function to speed up its future execution.

User Global Zone: UGA contains session information for the Oracle shared server. When using a shared server session, if a large pool has not been configured, the UGA is located in the shared pool.

3. Large Pools Large pool

Can be configured as a separate memory area in the SGA

Size is specified by the Large_pool_size parameter

Used to store data in memory for the following:
--uga

--Backup and restore operations

--Parallel Query message delivery

Large pools must be explicitly configured. The memory of the large pool is not from the shared pool, but comes directly from the SGA, which increases the amount of shared memory that the Oracle server needs when the instance starts.

A large pool is used to allocate large amounts of session memory for the following:

--I/O Server process

--Backup and restore operations

--oracle Shared server process and Oracle XA interface (used when transaction processing is interacting with multiple databases)

Because of the allocation of session memory to an Oracle shared server from a large pool, there is little fragmentation in the shared pool due to the frequent allocation and deallocation of large objects. Separating large objects from a shared pool increases the efficiency of shared pool memory, which means that it can use more memory to process new requests and to preserve existing data when needed.

4.Java Pool

Can be configured as a separate memory area in the SGA

Size is specified by the Java_pool_size parameter

Used to store all session-specific Java code and data in the JVM in memory

5. Redo log buffer Redo buffer cache

The Oracle server process copies the redo entries from the user's memory space to the redo log buffers for each DML or DDL statement. Redo entries contain information that is necessary to rebuild or redo DML and DDL operations for changes to the database. They are used for database recovery and need to occupy contiguous space in the buffer.

The redo log buffer is a circular buffer, and the server process can overwrite entries that have been written to the disk in the Redo log buffer with new entries. The LGWR process usually writes fast enough to ensure that there is always room for new entries in the buffer. The LGWR process writes the redo log buffer to the active online redo log file (or active group member) on disk. The LGWR process copies all the redo entries that lgwr into the buffer since the last time the disk was written to disk.

What causes LGWR to perform write operations?

When a user process commits a transaction

Every three seconds, or whenever the redo log buffer fills One-third

When the DBWN process writes a modified buffer to disk (if the corresponding redo log data has not been written to disk)

Oracle Memory Architecture in detail