Oracle 12.1.0.2 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:

 

  • Alter System
    • Before
      • SGA_TARGET = 4G
      • DB_FLASH_CACHE_SIZE = 0
    • After
      • SGA_TARGET = 20G
      • DB_FLASH_CACHE_SIZE = 16G
  • Alter each TPC-H table to enable in-memory:
    • Try #1 - INMEMORY PRIORITY CRITICAL
    • Try #2 - INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY CRITICAL
  • Restart the Database Instance
  • Wait for in memory objects to load (see queries that follow)

 

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):

 

Test #

Feature

Debut

Configuration Parameters

Run Time (Mins:Secs)

Compress

Factor

1

 

SGA_TARGET = 4G

*** Nothing Else – Baseline ***

45:52

 

2

11g

SGA_TARGET = 4G

DB_FLASH_CACHE_SIZE = 16G

DB_FLASH_CACHE_FILE = '/flash/flash01.dbf'

23:19

 

3

 

SGA_TARGET = 20G

*** Nothing Else – All memory buffer cache ***

19:50

 

4

12c

SGA_TARGET = 20G

DB_BIG_TABLE_CACHE_PERCENT_TARGET = 80

PARALLEL_DEGREE_POLICY=auto

ORA-600

 

5

12c

SGA_TARGET = 20G

INMEMORY_SIZE = 16G

Try #1 - INMEMORY (default)

05:01

3.63X

6

12c

SGA_TARGET = 20G

INMEMORY_SIZE = 16G

Try #2 - INMEMORY MEMCOMPRESS FOR QUERY HIGH

04:56

4.68X

 

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 12.1.0.2.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!

 

  • Test Case #1 -> Test Case #1
    • Grow SGA from 4G to 20G via SSD
    • 49% reduction in run time vs. baseline
  • Test Case #2 -> Test Case #3
    • Grow SGA from 4G to 20G via DRAM
    • 57% reduction in run time vs. baseline
    • 15% reduction in run time vs. SSD
  • Test Case #3 -> Test Case #5
    • IN MEMORY = 16G default compression
    • 89% reduction in run time vs. baseline
    • 78% reduction in run time vs. SSD
    • 75% reduction in run time vs. DRAM
  • Test Case #3 -> Test Case #6
    • IN MEMORY = 16G MEMCOMPRESS FOR QUERY HIGH 
    • 89% reduction in run time vs. baseline
    • 79% reduction in run time vs. SSD
    • 75% reduction in run time vs. DRAM

 

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.

 

-- im_tabs.sql

set linesize 256

set pagesize 999

set verify off

col OBJECT format a30

SELECT owner||'.'||table_name OBJECT,

       inmemory INMEMORY,

       inmemory_priority PRIORITY,

       inmemory_distribute DISTRIBUTE,

       inmemory_compression COMPRESSION,

       inmemory_duplicate DUPLICATE

FROM  all_tables

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.

 

-- im_segs.sql

set linesize 256

set pagesize 999

set verify off

col owner format a20

col segment_name format a30

select owner, segment_name, populate_status

from v$im_segments;

 

SQL> @im_segs

 

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.

 

-- im_pct.sql

set linesize 256

set pagesize 999

set verify off

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"

from V$IM_SEGMENTS

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