Unlock Oracle 12c Performance: Mastering In-Memory Option (IMO)

Overview of Oracle In‑Memory Option (IMO)

IMO is an optional column‑store component introduced in Oracle Database 12c (starting with 12.1). It stores a copy of tables, partitions, or materialized views in columnar format inside the SGA while the traditional row‑store buffer cache remains unchanged, allowing simultaneous row and column access.

To enable IMO set the initialization parameter INMEMORY_SIZE to a non‑zero value (minimum 100 MB). After changing the parameter the instance must be restarted.

Levels at Which In‑Memory Can Be Enabled

Column

Table

Materialized view

Tablespace

Partition

Enabling at the tablespace level propagates the setting to all tables and materialized views contained in that tablespace.

Workloads Best Suited for IMO

Full‑table scans with simple predicates (=, <, >, IN)

Selecting a small subset of columns from a very wide table

Joins where one side is a small table and the other side is large

Aggregations over large datasets

When data is stored column‑wise many composite indexes become unnecessary because the columnar format already provides fast predicate evaluation.

SQL Statements Supporting the INMEMORY Clause

CREATE TABLE

ALTER TABLE

CREATE TABLESPACE

ALTER TABLESPACE

CREATE MATERIALIZED VIEW

ALTER MATERIALIZED VIEW

Finding Segments with In‑Memory Enabled

Query the view V$IM_SEGMENTS to list objects that have IM attributes:

SELECT OWNER,
       SEGMENT_NAME,
       INMEMORY_PRIORITY,
       INMEMORY_COMPRESSION
FROM   V$IM_SEGMENTS;

INMEMORY_PRIORITY

shows the priority (default NONE ) and INMEMORY_COMPRESSION shows the compression level.

Operations Unsuitable for In‑Memory

Queries with complex predicates (e.g., functions, OR conditions)

Queries that select a large number of columns from a table

Queries that return a very large result set

Queries involving many large‑table joins

Objects owned by SYS or stored in the SYSTEM or SYSAUX tablespaces cannot be placed in the In‑Memory column store.

Data Compression Options

IMO supports several compression levels that trade storage density for CPU overhead. The most common options are: MEMCOMPRESS FOR QUERY – low‑overhead compression suitable for query‑only workloads. MEMCOMPRESS FOR CAPACITY LOW – higher compression, modest CPU impact. MEMCOMPRESS FOR CAPACITY HIGH – maximum compression, higher CPU cost.

Higher compression reduces the in‑memory footprint but may increase query latency.

IM Storage Settings and Priority

The total amount of SGA allocated to the In‑Memory column store is controlled by INMEMORY_SIZE. When the combined size of IM‑enabled objects exceeds this limit, Oracle evicts objects with the lowest priority.

Priority can be set per table or per partition (not per column) using the PRIORITY clause. Valid values are NONE , LOW , MEDIUM , HIGH , and CRITICAL . Objects smaller than 64 KB are never cached in IM.

Initialization Parameters Related to IMO

INMEMORY_SIZE – total IM memory (default 0, must be ≥100 M if non‑zero).

INMEMORY_FORCE – globally enables (DEFAULT) or disables IM.

INMEMORY_CLAUSE_DEFAULT – default INMEMORY clause applied to newly created objects.

INMEMORY_QUERY – toggles optimizer use of IM (ENABLE/DISABLE).

INMEMORY_MAX_POPULATE_SERVERS – maximum number of background processes that populate IM.

INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT – percentage of the above used for incremental repopulation.

OPTIMIZER_INMEMORY_AWARE – tells the Cost‑Based Optimizer whether to consider IM (TRUE/FALSE).

Example of setting a default clause:

ALTER SYSTEM SET INMEMORY_CLAUSE_DEFAULT='INMEMORY MEMCOMPRESS FOR CAPACITY HIGH' SCOPE=SPFILE;

Enabling IM on Tables, Tablespaces, and Materialized Views

Prerequisites:

Oracle version 12.1.0 or higher. INMEMORY_SIZE set to at least 100 M.

Instance restart after setting INMEMORY_SIZE.

Examples:

CREATE TABLE test_inmem (
  id        NUMBER(5) PRIMARY KEY,
  test_col  VARCHAR2(15)
) INMEMORY;

ALTER TABLE oe.product_information INMEMORY;

ALTER TABLE oe.product_information INMEMORY MEMCOMPRESS FOR CAPACITY LOW;

ALTER TABLE oe.product_information INMEMORY PRIORITY HIGH;

Disable IM for a table:

ALTER TABLE oe.product_information NO INMEMORY;

Tablespace Example

CREATE TABLESPACE tbs1
  DATAFILE 'tbs1.dbf' SIZE 40M
  ONLINE
  DEFAULT INMEMORY;

Alter an existing tablespace to set compression and priority:

ALTER TABLESPACE tbs1 DEFAULT INMEMORY
  MEMCOMPRESS FOR CAPACITY HIGH
  PRIORITY LOW;

Materialized View Example

CREATE MATERIALIZED VIEW oe.prod_info_mv INMEMORY
AS SELECT * FROM oe.product_information;

ALTER MATERIALIZED VIEW oe.prod_info_mv INMEMORY PRIORITY HIGH;

Dictionary Views for IMO

Key dynamic performance views:

V$INMEMORY_AREA – memory consumption of IM objects.

V$IM_SEGMENTS – segment attributes (priority, compression, etc.).

V$IM_COLUMN_LEVEL – column‑level compression settings.

V$IM_COL_CU – column‑level statistics stored in IM compression units.

V$IM_HEADER , V$IM_SMU_CHUNK , V$IM_SMU_HEAD – metadata about IMCU allocation.

These views help monitor which objects are cached, their priority, compression level, and related wait events.

Practical Observations

An object must be accessed after IM is enabled before it is actually loaded into the In‑Memory column store.

Changing an object's priority to a lower value triggers immediate eviction from IM.

IM automatically detects data changes for cached objects, but row‑store statistics are not refreshed automatically.

The optimizer may display TABLE ACCESS INMEMORY FULL in an execution plan even if the object is not currently cached; actual execution will fall back to row‑store access.

All observations were verified on Oracle Database 12.1.0.2 running on RHEL 6.4 using test tables created via CTAS.

References

Official Oracle documentation (plain URLs):

https://docs.oracle.com/database/121/REFRN/GUID-5772F775-2A3E-4BC8-AA03-B8FF383BEE52.htm#REFRN10350

https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN14257