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 5X.

— 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

About bscalzo2

Bert Scalzo is an Oracle ACE, blogger, author, speaker and database technology consultant. His work experience includes stints as product manager for DBArtisan and Rapid SQL at IDERA and chief architect for the popular Toad family of products at Quest Software. He has three decades of Oracle® database experience and previously worked for both Oracle Education and Oracle Consulting. Bert holds several Oracle Masters certifications and his academic credentials include a BS, MS and Ph.D. in computer science, as well as an MBA. He has presented at numerous Oracle conferences and user groups, including OOW, ODTUG, IOUG, OAUG, RMOUG and many others. Bert’s areas of interest include data modeling, database benchmarking, database tuning and optimization, "star schema" data warehouses, Linux® and VMware®. He has written for Oracle Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell Power Solutions Magazine, The LINUX Journal, LINUX.com, Oracle FAQ and Toad World. Bert has also written the following books: • Oracle DBA Guide to Data Warehousing and Star Schemas • TOAD Handbook (1st Edition) • TOAD Handbook (2nd Edition) • TOAD Pocket Reference (2nd Edition) • Database Benchmarking: Practical Methods for Oracle & SQL Server • Advanced Oracle Utilities: The Definitive Reference • Oracle on VMware: Expert Tips for Database Virtualization • Introduction to Oracle: Basic Skills for Any Oracle User • Introduction to SQL Server: Basic Skills for Any SQL Server User • Toad Unleashed • Leveraging Oracle Database 12cR2 Testing Tools • Database Benchmarking and Stress Testing (coming 2018)

Category

Uncategorized