Oracle 220.127.116.11 debuted recently and I decided to benchmark the IN MEMORY COLUMN STORE feature. There’s been a lot of press and excitement about this feature, so I wanted to add something of value to that dialog. So this blog covers what I learned while running industry standard TPC-H data warehousing benchmark utilizing the IN MEMORY COLUMN STORE feature (plus some other 11g and 12c features).
So what exactly is the feature “IN MEMORY COLUMN STORE”? I think the August 2014 white paper from Oracle titled Oracle Database In-Memory is a great resource and a “must read”. I’ll try to summarize the concept with the following basic diagram. Note that the SGA memory now has a second data cache area called the “IN MEMORY” cache which is controlled by the “INMEMORY_SIZE” configuration parameter.
So now Oracle offers to handle data in both tabular formats – row vs. column oriented. In fact Oracle automagically provides what they call the “dual format architecture”. Basically any object marked as being “in memory enabled” will simultaneously exist in both the DB Buffer Cache and the IN MEMORY Cache shown above. The objects located in the IN MEMORY Cache are compressed, so the overhead is minimal. Plus column oriented data tends to consume far less space as well. So Oracle claims to expect only 20% more memory required. Moreover the database maintains full transactional consistency across the two tabular formats, plus the optimizer will intelligently direct queries to the best tabular format based upon the nature of the queries. The only downside is that dual architecture objects must at some point get loaded into the IN MEMORY Cache, which can both consume significant CPU and take time.
In order to enable the IN MEMORY COLUMN STORE feature I had to do the following:
Strictly speaking you don’t have to bounce the database to enable and use the IN MEMORY COLUMN STORE feature. However note that I marked each TPC-H table as “PRIORITY CRITICAL” in order to force those objects to load into memory when the database starts. My goal was to have those TPC-H objects 100% in memory before running the TPC-H queries so as to score the best results. Of course I first run the TPC-H benchmark without using the IN MEMORY COLUMN STORE feature to establish a comparative baseline. Note that I used the commercial version of Dell Software’s Benchmark Factory to create and run the TPC-H benchmark – there is also a freeware version available.
Here are my run time results against a 10GB TPC-H scale factor – note that I also tested an older 11g feature (database flash cache) and another new 12c feature (big table caching):
Run Time (Mins:Secs)
SGA_TARGET = 4G
*** Nothing Else – Baseline ***
DB_FLASH_CACHE_SIZE = 16G
DB_FLASH_CACHE_FILE = '/flash/flash01.dbf'
SGA_TARGET = 20G
*** Nothing Else – All memory buffer cache ***
DB_BIG_TABLE_CACHE_PERCENT_TARGET = 80
INMEMORY_SIZE = 16G
Try #1 - INMEMORY (default)
Try #2 - INMEMORY MEMCOMPRESS FOR QUERY HIGH
Test Case #1 allocates just 4G of SGA. The reason for choosing this size was simple – my virtual machine limit for memory is 24G, so leaving 4G for the OS – the most memory I can allocate to the Oracle SGA is 20G. My later “IN MEMORY” test cases will set INMEMORY_SIZE = 16G, thus: 20G “Max possible SGA” minus 16G “IN MEMORY” leaves a remaining SGA of just 4G which is now the baseline size.
Test case #2 then extends the baseline SGA from 4G to 20G (same total SGA in in the later “IN MEMORY” test cases) using the 11gR2 feature known as “Database Smart Flash Cache”. I therefore allocated 16G of flash which should have been sufficient to house all my tables and indexes, or very close to it. I had read Guy Harrison’s blog on database flash cache, and was hoping to test as he says a decent commercial SSD flash drive (i.e. OCZ Vertex 4).
Test case #3 then extends the baseline SGA from 4G to 20G (same total SGA in in the later “IN MEMORY” test cases) using memory (i.e. DRAM) instead of SSD. I did so for two reasons. First I wanted to show the performance difference DRAM and SSD. Second I wanted to be able to compare old style row-oriented vs. new column-oriented with the same size SGA (i.e. 20G).
Test Case #4 then sought to improve upon Test Case #3 by using the new 18.104.22.168.0 feature known as “Automatic Big Table Caching” (which also requires setting parallel degree policy = auto). However as the popular saying goes “the best-laid plans of mice and men often go awry”. Unfortunately using this new feature yielded ORA-600 errors that were not as yet documented on Oracle support. So I had to bail on testing it.
Finally Test Cases #5 and #6 test utilizing the IN MEMORY COLUMN STORE feature, just with different compression options (i.e. default vs. MEMCOMPRESS FOR QUERY HIGH). Remember as I said above, I set all my table priorities to critical so that they load into memory on database startup – I just had to wait until that process completed before running the TPC-H benchmark. Note well that I did not have to do anything special for testing “IN MEMORY”. I did not drop any indexes nor collect statistics differently. Basically the dual architecture automagically handles when to use row-oriented vs. column-oriented data – so there’s nothing to drop or additional to do. My IN-MEMORY compression levels were from 3.63X to 4.68X (the calculation scripts follow).
Now let’s review the results. As with any ad-hoc benchmarks, do not take these results as universally indicative of anything!
I think WOW sums it up best. The new IN MEMORY COLUMN STORE feature is easy to use, simply works as advertised – and in my case (remember your mileage will vary), I achieved nearly 5X compression and between 75% and 90% run time improvements depending on which comparison is made. In my books that’s a home run. Oracle has clearly done their homework in delivering this great new feature.
Finally here are some scripts I used to monitor what objects were in memory enabled, when my objects were successfully loaded in memory, and how well they compressed in memory. We’ll take a quick look at each one.
First, we need to know which tables for our test schema (BMF1) have been “in memory” enabled – and with which options. Remember that I tried two different scenarios with different compression settings, so this is the output for the first (default) scenario.
set linesize 256
set pagesize 999
set verify off
col OBJECT format a30
SELECT owner||'.'||table_name OBJECT,
where owner like upper('%&1%')
ORDER BY inmemory, owner||'.'||table_name;
SQL> @im_tabs BMF1
OBJECT INMEMORY PRIORITY DISTRIBUTE COMPRESSION DUPLICATE
------------------------------ -------- -------- --------------- ----------------- ------------
BMF1.H_CUSTOMER ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
BMF1.H_LINEITEM ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
BMF1.H_NATION ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
BMF1.H_ORDER ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
BMF1.H_PART ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
BMF1.H_PARTSUPP ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
BMF1.H_REGION ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
BMF1.H_SUPPLIER ENABLED CRITICAL AUTO FOR QUERY LOW NO DUPLICATE
Second, we need to know when our “in memory” enabled tables have been successfully loaded. In my tests I waited until all such enabled tables were COMPLETED so as to best or more fully leverage the in memory feature.
col owner format a20
col segment_name format a30
select owner, segment_name, populate_status
OWNER SEGMENT_NAME POP STATU
-------------------- ------------------------------ ---------
BMF1 H_LINEITEM STARTED
BMF1 H_SUPPLIER COMPLETED
BMF1 H_PARTSUPP COMPLETED
BMF1 H_ORDER COMPLETED
BMF1 H_PART COMPLETED
BMF1 H_CUSTOMER COMPLETED
Third, we need to know how well our “in memory” enabled tables compressed. This confirms the Oracle statement that in memory does not add much overhead (20%) because my tables compressed to reduce by almost 4X.
select segment_name,ROUND(SUM(BYTES)/1024/1024/1024,2) "ORIG GB",
ROUND(SUM(INMEMORY_SIZE)/1024/1024/1024,2) "IN-MEM GB",
ROUND(SUM(BYTES-BYTES_NOT_POPULATED)*100/SUM(BYTES),2) "% IN_MEM",
ROUND(SUM(BYTES-BYTES_NOT_POPULATED)/SUM(INMEMORY_SIZE),2) "COMP RATIO"
group by owner,segment_name
order by SUM(bytes) desc;
SQL> @im_pct -- Try #1 - INMEMORY (default)
SEGMENT_NAME ORIG GB IN-MEM GB % IN_MEM COMP RATIO
------------------------------ ---------- ---------- ---------- ----------
H_LINEITEM 8.42 2.32 100 3.63
H_ORDER 1.75 .89 100 1.97
H_PARTSUPP .6 .35 100 1.71
H_PART .29 .13 100 2.29
H_CUSTOMER .22 .19 100 1.19
H_SUPPLIER .01 .02 100 .91
SQL> @im_pct -- Try #2 - INMEMORY MEMCOMPRESS FOR QUERY HIGH
SEGMENT_NAME ORIG GB IN-MEM GB % IN_MEM COMP RATIO
H_LINEITEM 8.42 1.77 98.17 4.68
H_ORDER 1.75 .63 100 2.78
H_PARTSUPP .6 .32 100 1.86
H_PART .29 .09 100 3.16
H_CUSTOMER .22 .15 100 1.52
H_SUPPLIER .01 .01 100 1.31