Executive Summary

Use Case 1: In-Memory Column Store (IMCS) query performance

In Use Case 1, we studied the Oracle Database In-Memory (IM) query performance on two different OLAP schema sizes—1 TB and 3 TB TPC-H-like schemas—in combination with different IM compression levels. These tests were conducted across different stand-alone R740xd server memory configurations, described below:

Test 1.1:  1 TB TPC-H-like or OLAP schema IMCS query performance

• R740xd server1 DRAM-only configuration: This server included a total main memory capacity of 768 GB using 12 x 64 GB DRAM DIMMs.
• R740xd server2 PMem-MM configuration: This server included a total main memory capacity of 1.5 TB using 12 x 128 GB PMem-MM modules. This server also included 384 GB of PMem front-end DRAM-cache using 12 x 32 GB DDR4 DIMMs. This server configuration had a DRAM:PMem-MM ratio of 1:4.

Test 1.2: 3 TB TPC-H-like or OLAP schema In-Memory query performance

• R740xd server3 DRAM-only configuration: This server included a total main memory capacity of 1.5 TB using 24 x 64 GB DRAM DIMMs.
• R740xd server4 PMem-MM configuration: This server included a total main memory capacity of 3 TB using 12 x 256 GB PMem-MM modules. This server also included 768 GB of PMem front-end DRAM-cache using 12 x 64 GB DDR4 DIMMs. This server configuration had a DRAM:PMem-MM ratio of 1:4.

The key observations from the results of the two tested OLAP schema sizes in Use Case 1 are as follows:

• In smaller OLAP environments (1 TB in our study), DRAM DIMM’s speed has the edge over PMem module’s speed for Database IM query performance. When the OLAP dataset is small, all IM scans and query executions can be accomplished inside the database’s IMCS and PGA memory components. Therefore, the memory media speed is the primary driver of query performance. As a result, for small OLAP schemas, the DRAM-only configuration delivers better Database In-Memory query performance than the PMem-MM configuration. For results and details, see test comparisons Results comparison 1: 768 GB DRAM-only Cap High vs 1.5 TB PMem-MM Cap High.

• In larger OLAP environments (3 TB in our study), PMem-MM with its front-end DRAM-cache architecture can deliver IM query performance equal to that of the DRAM-only configuration. This is because with larger datasets, larger dedicated In-Memory areas are required. This configuration will not usually leave enough memory headroom for PGA sizes to perform all query sorting of a large dataset in memory. Not enough PGA size causes I/O to TEMP space on disk to significantly increase, leading to significant database wait times. These external factors reduce the DRAM’s individual speed advantage over PMem-MM. For results and details, see test comparisons Results comparison 1: 1.5 TB DRAM-only Cap High vs 3 TB PMem-MM Cap High.
• Query-optimized IM compression level is not always the better option. It is better to use a space savings-optimized IM compression level that allows the entire source schema to fit inside the In-Memory area than to use a more query-optimized compression level that causes data to spill over to disk. When source data spills over to disk, full table scans and query processing take significantly longer to finish due to disk latencies. For results and details, see test comparisons Results comparison 2: 768 GB DRAM-only Cap high vs 768 GB DRAM-only Query Low vs 1.5 TB PMem-MM Query Low and Results comparison 2: 1.5 TB DRAM-only vs 3 TB PMem-MM: Cap Low and Query High tests.
• In larger OLAP environments (3 TB in our study), a query-optimized IM compression level is a better choice than space-savings-optimized compression level if we can populate entire datasets inside the IMCS with both options. This is because with larger datasets, significant CPU cycles and time are saved by not having to decompress the large IMCS data. This leads to much faster full-table IM scans and IM execution times, which lead in turn to better overall Database In-Memory query performance. Therefore, it is imperative to do memory capacity planning and sizing to determine the best IM compression level which will yield optimum query performance from the in-memory system. For results and details, see test comparisons Results comparison 3: 3 TB PMem-MM:  Capacity High vs Capacity Low vs Query High. For memory sizing guidelines, see Memory sizing.
• In larger OLAP environments (3 TB OLAP schema size in our study), PMem-MM with its front-end DRAM-cache architecture can even outperform the DRAM-only configuration’s IM query performance. In general, PMem-MM with its larger memory module capacity advantage over DRAM DIMMs can provide larger total main memory capacities than DRAM-only configurations. Thus, in multi-terabyte OLAP environments, PMem-MM with its larger memory capacities can not only help to populate larger datasets in-memory but can also help to bring less-compressed source schemas into the in-memory area. With larger datasets, less-compressed IMCS data (with a more query-optimized IM compression level) delivers better query performance than more compressed data. For test results and details, see test comparisons Results comparison 4: Best-case scenarios: 1.5 TB DRAM-only Cap High vs 3 TB PMem-MM Query High.

Use Case 2:  IMCS load performance with and without In-Memory FastStart (IMFS) area

In Use Case 2, we compared IMCS load performance from the IMFS disk area on three different fast-tiered storage disks—SAS SSDs, NVMe PCIe SSDs, and PMem in App Direct Mode. We compared their performance with a baseline configuration that did not implement the IMFS area, but instead loaded the IMCS from the source row-format schema on disk (SAS SSDs in this case). The key observations from the results of the test cases in Use Case 2 are shown below:

• The IMFS feature can load the data into IMCS at least four times faster than it can load into IMCS from the source schema on disk, any time the database must restart. This is because the data on the IMFS disk area is already compressed and in columnar format. For results and details, see Test 2.1:  IMCS load duration: Baseline vs IMFS on fast-tiered storage.
• Overall IMFS load performance is dependent on the disk type (SAS SSDs in this case) on which the source database resides. This is because the database cross-checks the IMFS data against the source schema for transactional consistency before loading it into IMCS. Therefore, for optimum IMCS load performance from the IMFS disk area, we recommend that you choose the same disk type as the disk type on which the source database is placed. For results and details, see Test 2.2:  IMFS-to-IMCS population: SAS-SSDs vs NVMe-PCIe-SSDs vs PMem-AD.
• The IMFS feature can reduce CPU usage by up to 10 times during the IMCS load operation, as compared to CPU usage when loading from source schema. This is because the data on the IMFS disk area is already compressed and in columnar format. For results and details, see Test 2.3:  IMCS population: CPU Utilization - Baseline vs IMFS on fast-tiered storage.