오라클 데이터베이스 11g~21c까지의 SGA 구조 정리

오라클 데이터베이스 메모리 관리의 핵심 SGA 구조에 변화가 생겼다. 

19c부터 "Memoptimize Pool" 영역이 추가되었다.

아래 도표와 설명을 살펴보면 "Memoptimize Pool" 영역이 추가된 것을 확인할 수 있다.

 

오라클을 전문적으로 다루는 사람도 잘 알고 있다고 그냥 넘어가지 말고, 이번 기회에 다시 한번 정독하도록 하자. 

의외로 모르고 있던 부분이 있을 수 있다.

 

"In-Memory Area"의 존재와 역할에 대해서 잘 알고 있는가?

"Database Smart Flash Cache"의 존재와 역할에 대해 잘 알고 있는가?

"Memoptimize Pool"의 존재와 역할에 대해 잘 알고 있는가?

 

▶ 오라클 데이터베이스 11g, 12c, 18c의 SGA 구조

오라클 데이터베이스 18C의 SGA 구조

• Shared pool: Caches various constructs that can be shared among users; for example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and 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.
• Flashback buffer: Is an optional component in the SGA. When Flashback Database is enabled, the background process called Recovery Writer Process (RVWR) is started. RVWR periodically copies modified blocks from the buffer cache to the flashback buffer, and sequentially writes Flashback Database data from the flashback buffer to the Flashback Database logs, which are circularly reused.
• Database 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.
• Database Smart Flash cache: Is an optional memory extension of the database buffer cache for databases running on Solaris or Oracle Linux. It provides a level 2 cache for database blocks. It can improve response time and overall throughput for both read-intensive online transaction processing (OLTP) workloads and ad-hoc queries and bulk data modifications in a data warehouse (DW) environment. Database Smart Flash Cache resides on one or more flash disk devices, which are solid state storage devices that use flash memory. Database Smart Flash Cache is typically more economical than additional main memory, and is an order of magnitude faster than disk drives.
• Redo log buffer: Is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct (or redo) changes that are made to the database by data manipulation language (DML), data definition language (DDL), or internal operations. Redo entries are used for database recovery if necessary.
• Large pool: Is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. The large pool can provide large memory allocations for the User Global Area (UGA) for the shared server and the Oracle XA interface (used where transactions interact with multiple databases), message buffers used in the parallel execution of statements, and buffers for Recovery Manager (RMAN) I/O slaves.
• 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. This feature is particularly useful for analytic applications that operate on a few columns returning many rows rather than for OLTP, which operates on a few rows returning many columns.
• Shared I/O pool (SecureFiles): Is used for large I/O operations on SecureFile 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.
• Streams pool: Is used by Oracle Streams, Data Pump, and GoldenGate integrated capture and apply processes. The Streams pool stores buffered queue messages, and it provides memory for Oracle Streams capture processes and apply processes. Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as needed when Oracle Streams is used.
• Java pool: Is used for all session-specific Java code and data in the Java Virtual Machine (JVM). Java pool memory is used in different ways, depending on the mode in which Oracle Database is running.
• Fixed SGA: Is an internal housekeeping area containing general information about the state of the database and database instance, and information communicated between processes.

 

▶ 오라클 데이버베이스 19c, 21c의 SGA 구조

오라클 데이xjqpdltm 19C~21C의 SGA 구조

새로 추가된 Memoptimize Pool에 대한 설명

• 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.

 

끝.