The Introduction section of this design guide provides a high-level overview of the Oracle Database In-Memory’s dual architecture. As part of this dual architecture, the purely in-memory columnar format includes several optimizations for accelerating OLAP query processing in the server’s main memory. In-Memory Column Store (IMCS), or the In-Memory area’s size, is controlled by the database initialization parameter inmemory_size.
Theoretically, the more of the OLAP schema dataset we can bring into the In-Memory area of the SGA, the more performance benefit we can potentially achieve with the Oracle Database In-Memory feature. Therefore, as documented in this guide, we tested the IMCS OLAP query performance with different capacities of Intel Optane PMem in Memory Mode, and we compared it to the query performance on different capacities of traditional DRAM.
The Oracle Database In-Memory supports six varying IMCS compression levels or methods for space savings and query optimizations, described briefly in the following table.
Table 2. Oracle Database In-Memory Column Store (IMCS) compression levels
1 |
NO MEMCOMPRESS |
The data is not compressed |
2 |
MEMCOMPRESS FOR DML |
Provides best DML performance |
3 |
MEMCOMPRESS FOR QUERY LOW |
Most optimized for query performance |
4 |
MEMCOMPRESS FOR QUERY HIGH |
Less query optimized and more space-savings optimized than Query Low |
5 |
MEMCOMPRESS FOR CAPACITY LOW |
Less query optimized and more space-savings optimized than Query High |
6 |
MEMCOMPRESS FOR CAPACITY HIGH |
Least query optimized and most space-savings optimized compression method |
For details on the IM compression levels, refer to Oracle Database In-Memory Guide: IM Column Store Compression Methods.
In the rest of this guide, the four compression levels we tested is referred to as follows:
- MEMCOMPRESS FOR QUERY LOW = Query Low
- MEMCOMPRESS FOR QUERY HIGH = Query High
- MEMCOMPRESS FOR CAPACITY LOW = Capacity Low or Cap Low
- MEMCOMPRESS FOR CAPACITY HIGH = Capacity High or Cap High
The query-optimized algorithms provide options to bring less-compressed data into the In-Memory area, but at the cost of larger memory capacity requirements. Conversely, the space-saving algorithms provide options to bring more data in to the In-Memory area, but at the cost of having a potential overhead on query performance. Depending on the type of compression level used, In-Memory population is a CPU-bound and time-consuming operation, involving reformatting of data into a columnar format and compressing the data before placing it into memory.
As described briefly in the Introduction section, IMFS allows the database to write the compressed columnar in-memory data, called In-Memory Compression Units (IMCUs), on to a persistent disk area—specifically on to a dedicated tablespace created just for this purpose.
Therefore, the second Oracle Database In-Memory use case we tested evaluated the reload time of IMCS from IMFS area when it is created on various fast-tiered persistent disks, including PMem as a persistent block-addressable storage (App Direct Mode). We compared that performance to the IMCS load time from source row-based tables to study the performance benefits of the IMFS feature.
IMPORTANT: At the time of this writing, storing Oracle Database 19c datafiles on Intel Optane PMem as persistent storage (running in App Direct Mode) is NOT supported, including using it as IMFS disk area. We recommend that you check the Oracle website for the latest Support updates before using PMem as a persistent device in your production environment with Oracle Databases 19c or older.
Note: At the time of this writing, Oracle Database 19c and older versions do not support the direct-access (DAX) feature to fully take advantage of the performance of PMem modules as a byte-addressable persistent memory device. Hence, this design guide focuses mainly on how to configure PMem modules in App Direct Mode with Oracle Databases and places less emphasis on its performance.
For further details on Oracle Database In-Memory and its features, see the Oracle Database 19c Database In-Memory Guide.
