Enabling Database In-Memory on Exadata: a 16-second query that became 0.12

Oracle 19c EE (Extreme Performance) 19.29 · two-node Exadata Database Service (ExaDB-D) · ~40M-row table · GROUP BY benchmark

I’ve been meaning to sit down with Oracle Database In-Memory for a while. Everyone quotes the marketing number, “100x faster analytics”, and I wanted to see it on my own data, on my own box, and understand what it actually takes to get there. Not the polished demo where everything works first try, but the real version with the sizing mistakes and the head-scratching along the way.

Spoiler: I got my big number.

But the interesting part wasn’t the result; it was the hour of “why isn’t this populating?” that came before it. So that’s what I’m writing up, prompts and all.

Environment, for context: Oracle 19c Enterprise Edition (Extreme Performance), running on a two-node Exadata Database Service (ExaDB-D) cluster. In-Memory is included with the Exadata cloud service, which is a nice perk, no separate license line to chase down.

Picking a table

I looked for a table that would actually show something. In-Memory rewards scans, aggregations, and joins, not single-row primary-key lookups, where the buffer cache is already plenty fast. I landed on TEST.USR.

About 4.7 GB, roughly 615K blocks. One row back, so it’s not partitioned. Then the row count and shape:

Just under 40 million rows, a narrow 69-byte average row, stats gathered recently. And a quick LOB check, because In-Memory doesn’t populate LOB columns into the column store:

No LOBs. Every column here is a scalar that goes in cleanly. This is a good demo table, big enough that a row-store scan does real work, narrow enough that most queries only touch a handful of columns, which is exactly where columnar storage pulls ahead.

Checking what I had to work with

Before touching anything, I looked at the host. This is the step people skip, and it’s the one that saves you.

Two things jumped out. First, sga_max_size and sga_target are both pinned at 16G with no slack. The In-Memory column store lives inside the SGA; it doesn’t sit on top of it. Whatever I hand to INMEMORY_SIZE gets carved out of the existing 16G, mostly at the expense of the buffer cache.

Second, and more important: the host has only ~7 GB available, and swap is already 3 GB in use. A database node that’s swapping is under real memory pressure. That gave me one hard rule: do not grow the SGA to make room for the store. The store comes from within the existing 16G, so it adds nothing to the host footprint. It just repurposes some buffer cache into column store. Safe path.

Current store size?

Zero on both nodes. And here’s the thing nobody tells you clearly: going from 0 to any nonzero value cannot be done dynamically. It’s a static change; set it in the spfile, and the instance only picks it up on restart. Once the store is already nonzero, you can raise it live (18c and up), but the first enablement needs a bounce. On a two-node RAC that’s a rolling restart, one instance at a time.

Sizing it: start low, grow later

Small trick that shaped my whole approach: you can increase INMEMORY_SIZE dynamically but can only decrease it with a restart. So it’s always cheaper to start low and grow. I set 2 GB and did the rolling restart:

Store’s up. Now to put the table in it.

Marking the table, and my first wrong turn

A word on those options, because I picked one wrong. MEMCOMPRESS FOR QUERY HIGH is good compression with fast scans, the usual sweet spot. PRIORITY HIGH populates right away instead of lazily. DUPLICATE ALL is an Exadata-only feature that mirrors a full copy on every node. Sounds great. It’s the mistake, and it takes a while to reveal itself.

Table altered, but marking a table INMEMORY is just a dictionary flag. It happily succeeds even with INMEMORY_SIZE = 0. I ran a populate and checked:

Nothing. GV$IM_SEGMENTS only lists segments that actually made it into the store, so “no rows” means nothing landed. And POPULATE returning “success” told me nothing. It just queues a request and returns, even when there’s nowhere for the data to go.

Reading the actual memory picture

Time to stop guessing and look at the store directly:

There it is. OUT OF MEMORY on the 1MB pool, both nodes. This detail clarified everything: a 2G store does not give you 2 GB for data. It splits into a 1MB pool (the actual column data) and a 64KB pool (metadata). Here, the split left only ~1.28 GB for data. So my real ceiling was 1.28 GB, not 2 GB.

A quick sanity check ruled out the boring explanations. The workers were running (inmemory_max_populate_servers = 2 per node), and an unfiltered GV$IM_SEGMENTS confirmed nothing else was competing for the pool. It really was just the size: the full table needs more than the 1.28 GB a 2G store exposes.

Growing the store, and a lesson in per-node fit

This is where starting at 2G paid off. Growing is dynamic, no restart:

I watched it populate over a couple of minutes: two populate servers per node grinding through ~3 GB takes a moment. First check caught it mid-flight, climbing:

And then a split result that told me something important:

Node 1 finished at 1855 MB, a clean 4796 → 1855, about 2.6x compression under QUERY HIGH. Node 2 ran out. Same store size, different outcome, because the 1MB/64KB pool split isn’t identical across instances and node 2’s data pool topped out a hair too low.

And that’s the real problem with DUPLICATE ALL: it demands the entire 1855 MB copy on both nodes. I could keep throwing memory at it, bump to 5G, give each node room for a full mirror, but I stopped to ask what DUPLICATE ALL was actually buying me. It’s an availability feature. It protects against an instance failure by keeping a second copy. My post is about query speed, not fault tolerance. I was paying a memory tax for something my demo didn’t need.

The fix: distribute instead of duplicate

So I dropped the duplicate and let the segment spread across the cluster instead:

With DISTRIBUTE AUTO and no duplicate, each node holds roughly half the segment, so each only has to fit ~900 MB instead of the full 1855. This time both nodes finished:

831 + 991 = 1822 MB across the cluster, bytes_not_populated = 0 on both. One full copy of the table, split in two, comfortably inside 4G. Finally.

One thing to remember with a distributed segment: each node only has its half locally. A serial query on one node would fetch the other half over the interconnect. So when you benchmark, run it in parallel — that engages both nodes’ PX servers, each scans its local half in memory, no cross-node fetch.

The Payoff

Quick look at cardinality to pick a good GROUP BY key:

GUID at 17,646 distinct values across 40M rows is the sweet spot. The harness — parallel, stats on, run each path warm:

Baseline first:

Then flip In-Memory on and run the identical query:

16.66 seconds down to 0.13. Same data, same instance, same query. But a timing number alone proves nothing. I wanted the execution plans to show why.

Gotcha: with SERVEROUTPUT on, a stray DBMS_OUTPUT call kept hijacking DBMS_XPLAN.DISPLAY_CURSOR (cannot fetch plan for SQL_ID ... BEGIN DBMS_OUTPUT.GET_LINES). Fix: SET SERVEROUTPUT OFF and fetch by explicit SQL_ID.

Two child cursors under one SQL_ID: child 0 is the row-store plan, child 1 is In-Memory.

Child 0: the baseline (row store), full plan

Child 1: In-Memory, full plan

There’s the whole story in two lines of plan output: the baseline reads INDEX STORAGE FAST FULL SCAN on IDX_USER_DEVICE1; the In-Memory run reads TABLE ACCESS INMEMORY FULL on USR. And the top-line Buffers on the in-memory plan are 19. Nineteen. The query only needed the GUID column, so that’s all it read.

An honest note worth making in any post like this: my baseline is an index fast-full-scan, not a full table scan. The optimizer chose the index on its own as the cheapest row-store option. That’s the fair comparison — In-Memory beating the optimizer’s own best non-In-Memory plan. A forced full table scan would have been slower still and made the gap look even bigger.

The columnar numbers, measured cleanly

To quantify the scan without cumulative-session noise, I reconnected a fresh session (counters reset to zero) and ran the in-memory query exactly once:

770 consistent gets for an aggregation over the whole table. That’s the number I keep coming back to. A row-store scan of this table is hundreds of thousands of block reads; the column store did the same logical work in 770.

The first time I pulled these stats — before the fresh session — they were nonsense (millions of “physical reads,” “IM scan rows” several times the row count). V$MYSTAT is cumulative for the whole session, and I’d run the query a half-dozen times. Reconnect, run once, read then. The plan’s own Buffers column is per-execution and doesn’t have that problem.

What I’d tell someone starting this

• The store size is not the space you get.

  A 2G store gave me ~1.28 GB of usable data pool after the 64KB metadata pool took its cut. Size against the pool confirms the real footprint in V$IM_SEGMENTS after population. (The in-memory compression advisor is useless before the store exists; it trial-populates, which it can’t at INMEMORY_SIZE = 0. Size conservatively, populate, then read the truth.)

   

• Start small and grow. 

  Increasing INMEMORY_SIZE is dynamic; shrinking needs a restart. Starting at 2G and growing to 4G cost me zero extra bounces.

• DUPLICATE ALL is availability, not speed.

  On a two-node cluster, it doubles your per-node footprint for fault tolerance. Chasing query performance, DISTRIBUTE AUTO fits in half the memory and benchmarks identically under a parallel query.

• When something won’t populate, list the whole store, not just your segment.

  Run GV$IM_SEGMENTS with no WHERE clause and check GV$INMEMORY_AREA for the pool split. A filtered query hides two of the usual culprits: another segment competing for the same pool, and the 1MB-vs-64KB pool ceiling, so you end up blaming the wrong thing.

• Watch how you measure.

  SERVEROUTPUT OFF, plans by SQL_ID, session stats from a clean session; otherwise your numbers will embarrass you in the comments.

The 135x is real, and it’s fun to quote. But what I’ll actually remember is the pool math and the stray backup — the stuff that turns a five-minute tutorial into an afternoon, and the stuff tutorials never mention.

For more information, check out our Oracle Database Services, or contact us today, and one of our experts will be in touch.