Oracle In-Memory Option Performance Testing with TPC-H Benchmark

One of the key innovations of the Oracle DBMS version 12.1.0.2 is the In-Memory option. Its main idea is that for the selected tables you can easily activate the dual-format mode, which combines the standard for Oracle DB row-based data storage format on the disk and in-memory wrap-by-side format.

The corresponding conversion and duplication of data into memory occurs automatically. For me personally, this was great news, as I was developing data warehousing (DWH) and had experience working with column-oriented DBMS Sybase IQ and HP Vertica, which are designed for storage and analytics. And Oracle offered the Column Store plus In-Memory plus all the features of the beloved DBMS! In fact, with this solution, Oracle has entered the market of analytical in-memory databases (who have not read, I recommend an excellent article on Habré comparing databases of this class). The idea of ​​Oracle is very promising, but in practice, on my test examples, the results, unfortunately, were not impressed. It was last year and I decided to wait for the technology to be improved. After the release of the next patch with improvements in the In-Memory Option, I returned to this issue. A more objective test was chosen for the article, which the readers can repeat if they wish.

Before turning to your benchmark, I will give a couple of links. In article on Habré in the blog of the Oracle company the detailed description of In-Memory Option and its fantastic results. In another article of the same blog, a performance test is given, but only 2 tables and a couple of simple queries are used.

TPC-H Benchmark

For the performance test, I used the tpc-h benchmark , which is used to compare the performance of analytical systems and data warehouses. This benchmark is used by many manufacturers of both DBMS and server hardware. There are many results available on the tpc-h page , for publication of which it is necessary to fulfill all the requirements of the specification on 136 pages. I did not officially publish my test, so I strictly did not follow all the rules. Also, to simplify the testing process, I used the free version of the Benchmark Factory for Databases program.
')
TPC-H allows you to generate data for 8 tables using the specified scale factor, which determines the approximate amount of data in gigabytes. I limited myself to 2 GB, since the free version of the Benchmark Factory no longer allows. The total number of rows in the tables:

Table	Number of lines
H_LINEITEM	11,996,782
H_ORDER	3,000,000
H_PARTSUPP	1,600,000
H_PART	400,000
H_CUSTOMER	300,000
H_SUPPLIER	20,000
H_NATION	25
H_REGION	five

The test includes 22 SQL queries of varying complexity. Comparing runtime with and without In-Memory. In this case, the following load was generated: 8 virtual users in parallel 3 times in a circle perform all 22 requests. As a result, the estimated execution time of 528 SQL queries was evaluated.

Those who find this test difficult enough, I recommend to pay attention to another more recent Benchmark - TPC-DS . It has more tables and significantly more queries - 99.

Test bench

A laptop with the following features:
- Intel Core i5-4210 CPU 1.70GHz - 4 cores; DDR3 16 Gb; SSD Disk.
OS:
- MS Windows 8.1 x64
DBMS:
- Oracle Database 12c EE 12.1.0.2.0
- Interim patches (1): "WINDOWS DB BUNDLE PATCH 12.1.0.2.160531 (64bit): 23179016"
DB Memory Configuration:
- memory_target = 10G;
- sga_target = 8G;
- inmemory_size = 3G;

Setting In-Memory (IM) Options

In addition to setting the inmemory_size database parameter, it is enough to specify the data of which tables or their parts need to be duplicated in the IM cache, Oracle will do the rest for you. Thus, it is very simple to transfer an existing database to IM if there is a sufficient amount of RAM. You do not need to rewrite anything, you can only delete indexes that are not needed for IM tables. Also note the stable work, I have not encountered a single bug related to IM.

In my test, all the tables went entirely to IM:

ALTER TABLE MY_TABLE_NAME INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY CRITICAL; 

• MEMCOMPRESS FOR QUERY HIGH is an option optimized for query performance and memory saving (there are 5 other options that can be found in the documentation).
• PRIORITY CRITICAL - determines the priority of replication in the IM cache.

Another important nuance is that the data in the columns compresses well, which is what Oracle does. The following query shows the amount of data on disk, in IM, and the compression ratio:

 select SEGMENT_NAME, ROUND(SUM(BYTES)/1024/1024/1024,2) "ORIG SIZE, Gb", ROUND(SUM(INMEMORY_SIZE)/1024/1024/1024,2) "IM SIZE, Gb", ROUND(SUM(BYTES)/SUM(INMEMORY_SIZE),2) "COMPRESS RATIO" from V$IM_SEGMENTS group by SEGMENT_NAME order by 2 desc; 

SEGMENT_NAME	ORIG SIZE, GB	IM SIZE, GB	COMPRESS RATIO
H_LINEITEM	1.74	0.67	2.62
H_ORDER	0.39	0.35	1.1
H_PARTSUPP	0.12	0.08	1.58
H_PART	0.06	0.02	2.96
H_CUSTOMER	0.04	0.03	1.42
H_NATION	0	0	0.22
H_SUPPLIER	0	0	0.89
H_REGION	0	0	0.22

Test results

	# 1 No In-Memory	# 2 In-Memory
Elapsed time	7 min 23 sec	6 min 26 sec
Avg. Response Time (sec)	5.617	4.712

Finally

I do not think that based on the results of any one test, it is possible to draw some categorical conclusions. Results can vary greatly depending on the models and data volumes, the specifics of queries, the configuration of parameters of the DBMS, as well as the hardware. As an alternative example, I will cite a link to a once-acclaimed benchmark, where Oracle compared the performance of Oracle IM (to Exadata + Exalogic) and SAP HANA. Used SAP BW-EML Benchmark. In this test, the hardware and software complex from Oracle was at the height.

If you have experience using Oracle In-Memory, I will be glad to read about it in the comments.