Unlocking Oracle In-Memory Column Store: Performance Gains, Compression, and Best Practices

1. IM Feature Overview

Oracle 12.1.0.2 introduced the In‑Memory Column Store (IM) feature. When enabled, a static memory pool called the In‑Memory Area is allocated in the SGA to store columnar versions of user tables. Columnar storage reduces I/O by reading only required columns and each column acts as an index, dramatically improving query performance.

IM does not replace the traditional row‑based buffer cache; it complements it. The optimizer automatically chooses between row and column access paths based on query characteristics.

The feature supports several compression levels, from no compression to high‑level query compression:

NO MEMCOMPRESS

MEMCOMPRESS FOR DML

MEMCOMPRESS FOR QUERY LOW

MEMCOMPRESS FOR QUERY HIGH

MEMCOMPRESS FOR CAPACIT LOW

MEMCOMPRESS FOR CAPACITY HIGH

The default is MEMCOMPRESS FOR QUERY LOW , which offers good compression while preserving optimal query performance. Higher levels provide stronger compression but add extra CPU overhead.

2. IM Feature Testing

Enabling IM

The feature is controlled by the inmemory_size parameter. Setting it to a value greater than 0 (minimum 100 M) activates IM. Example:

Alter system set inmemory_size=10g sid='db12c1' scope=spfile;

After a database restart, the SGA shows an additional In‑Memory Area, confirming that IM is active.

Performance Tests

Full‑table scan on a 360 k‑row table:

Without IM: logical reads 6130, CPU cost 427, plan TABLE ACCESS FULL.

With IM: logical reads 6, CPU cost 17, plan TABLE ACCESS INMEMORY FULL.

Result: performance improved by hundreds to thousands of times.

Join query between im_tab_ja (100 k rows) and im_tab_jb (2 k rows):

Without IM: higher logical reads and longer execution time.

With IM: dramatically lower reads and faster execution.

Scenario where row storage is still better : a table with a highly selective index on table_name. The optimizer chose the row‑based plan; forcing IM increased logical reads to 59, showing that IM does not help when the query can be satisfied efficiently via an index.

Compression Ratio Tests

Two tables were compressed: im_gender (300 k rows, only two distinct values) and im_phone (300 k rows, all distinct). Results: im_gender compression ratio 3.56. im_phone compression ratio 0.98 (actually larger).

Using DBMS_COMPRESSION.GET_COMPRESSION_RATIO to estimate, higher compression levels yielded 5‑12× reduction under the default level.

3. IM Production Practice

Zhejiang Mobile’s X‑system enabled IM after a three‑week testing period. Prior to IM, queries often timed out (30‑60 s) with a 60 % timeout rate during peak hours. The workload consisted of analytical queries with low data cardinality, ideal for columnar storage.

After enabling IM, query times dropped from dozens of seconds to a few hundred milliseconds (average 200 ms). Page response stabilized around 3 s, achieving a >20× speedup and 100 % success rate.

Execution‑plan comparison:

Before IM: physical reads >168 k, logical reads >168 k, CPU cost ~29 k, execution time 6.65 s.

After IM: physical reads 0, logical reads 24, CPU cost 2 741, execution time 0.23 s.

Compression in production reached 5‑12× under the default compression level.

4. IM Daily Maintenance

IM requires periodic checks: ensure tables are loaded, monitor In‑Memory Area usage, and add or remove tables/partitions as needed to stay within memory limits.

Adding or removing tables Alter table tab_name inmemory; Note: the table is populated on the next query. Alter table tab_name no inmemory; Store only selected columns: Alter table im_table inmemory no inmemory(object_name); Exclude specific partitions: Alter table im_table modify partition P_201601 no inmemory; Useful views select * from v$inmemory_area; Shows sub‑pools (1 MB for column data, 64 KB for metadata) and load status (DONE, POPULATING, OUT OF MEMORY).

select * from v$im_segments;

Shows which tables are in memory and their memory consumption.

select * from v$im_column_level;

Displays column‑level compression settings; columns marked no inmemory are not stored in the columnar area.

In RAC environments, inmemory_size can be set per node to load different tables according to workload. The parameter can be changed online but requires a restart to take effect, so careful planning of memory allocation is essential.