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

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.

For further details on Oracle Database In-Memory and its features, see the Oracle Database 19c Database In-Memory Guide.