Overview of Oracle Database In-Memory (IM)

 How Oracle Database In-Memory (IM) interacts with the SGA and the traditional buffer cache + indexes architecture?

How to sizing IN_MEMORY Area vs. SGA?

What happening when we have a B-Tree Index on the IM column?

How CBO use this mechanism?

*****************************************************************

How the In-Memory Area Fits into the SGA

 The IM area is part of the SGA — not independent.

When you enable the In-Memory column store (IM column store), it consumes a dedicated portion of the SGA, defined by the parameter:

INMEMORY_SIZE = <n>G

Example

If:

SGA_TARGET = 100G

INMEMORY_SIZE = 20G

Then:

• The In-Memory area (20G) is carved out inside the total 100G SGA.
• The remaining 80G will be available for other SGA components (buffer cache, shared pool, large pool, etc.).

You do not add the IM memory on top of SGA; you allocate it from SGA.
So effectively:

SGA Total = In-Memory Area + Other Components

100 GB = 20 GB + 80 GB

 

Structure

The IM area has two parts:

• IMCU Store (In-Memory Compression Units) → holds columnar data.
• Metadata Store → keeps dictionary and access info for IMCUs.

 

 How Oracle Decides to Use IM vs Buffer Cache

When you mark a table (or part of it) as INMEMORY, Oracle maintains two copies:

• Row format (in buffer cache)
• Columnar format (in IM area)

Example

ALTER TABLE employees INMEMORY;

When you query:

SELECT COUNT (salary) FROM employees;

Oracle’s optimizer (CBO) checks:

• Is the table or column populated in the IM column store?
• Does the query benefit from columnar scan (aggregation, filtering)?
• Is the IM area enabled and data populated?

If yes → it reads from the IM area, bypassing the buffer cache and indexes.

If not (e.g., IM area full, or data not populated yet) → it falls back to the traditional buffer cache access path (row store and indexes).

 

What Happens to Indexes?

When you mark a table/column as INMEMORY:

• Existing indexes are still valid.
• But Oracle may stop using them for analytic or aggregation queries, because the IM column store can scan and aggregate data much faster than index lookups.

Example

If EMPLOYEES(SALARY) has a B-tree index:

CREATE INDEX emp_sal_idx ON employees(salary);

And you run:

SELECT COUNT (salary) FROM employees;

Oracle will prefer the IM column store for full-table aggregations.

However, if you query:

SELECT * FROM employees WHERE salary = 10000;

Oracle might still use the index, because it’s a highly selective point lookup.

 

Should You Drop the Index?

• For OLAP-style analytic queries → IM area makes many indexes unnecessary, and dropping them can save storage and maintenance overhead.
• For OLTP-style queries (point lookups, selective filters) → keep the indexes, because IM scans are still full-column reads, even if optimized.

So, the general rule:

Keep indexes for OLTP and selective predicates;
Drop redundant ones if queries are mostly analytic and IM-enabled.

 

Summary Table

Aspect	Description
INMEMORY_SIZE	Allocated within SGA; not additional
Isolation	Logically independent but physically inside SGA
Usage	Stores compressed columnar data for fast scans
Query decision	CBO chooses IM or index based on cost
Index conflict	No conflict; coexist peacefully
Index removal	Optional; depends on workload pattern

 

Example Configuration

ALTER SYSTEM SET SGA_TARGET = 100G SCOPE=SPFILE;

ALTER SYSTEM SET INMEMORY_SIZE = 20G SCOPE=SPFILE;

ALTER SYSTEM SET INMEMORY_QUERY = ENABLE SCOPE=BOTH;

 

-- On specific tables

ALTER TABLE sales INMEMORY PRIORITY HIGH;

ALTER TABLE employees INMEMORY (salary, department_id);

After startup:

SELECT pool, name, bytes/1024/1024 AS MB

FROM v$sga_dynamic_components

WHERE name LIKE '%In-Memory%';

 

INMEMORY_AUTOMATIC_LEVEL — Automatic Management of In-Memory Population and Eviction

What It Is:

Starting from Oracle 19c, this parameter controls how Oracle automatically manages what data stays in the IM column store.

The IM column store can fill up quickly, and not every table or column may fit in memory.
This feature introduces dynamic population, eviction, and repopulation policies similar to buffer cache LRU behavior — but for the IM column store.

Parameter Definition

INMEMORY_AUTOMATIC_LEVEL = {OFF | LOW | MEDIUM | HIGH}

Level	Behavior
OFF	Default traditional mode. DBA fully controls what gets populated and stays resident.
LOW	Oracle may automatically evict cold objects when IM area is full, based on usage statistics.
MEDIUM	Adds automatic population: Oracle can load frequently accessed objects into IM store even if you didn’t explicitly mark them INMEMORY.
HIGH	Aggressive automation: Oracle autonomously decides which objects to populate, evict, compress, or skip — similar to Autonomous Database behavior.

 

Relationship with INMEMORY_SIZE

• This parameter doesn’t increase IM size; it manages how that space is used.
• IM size is still a hard cap defined by INMEMORY_SIZE.

Think of it as:

INMEMORY_SIZE = physical size (capacity)

INMEMORY_AUTOMATIC_LEVEL = intelligence (what & when to load)

When IM area becomes full:

• With OFF, Oracle raises ORA-64307 “out of In-Memory space”.
• With LOW/MEDIUM/HIGH, Oracle automatically evicts least-used IMCUs to make room.

This is very useful in large data warehouses or mixed workloads where not all data fits in memory simultaneously.

 

🧠 Example

ALTER SYSTEM SET INMEMORY_SIZE = 50G SCOPE=SPFILE;

ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = MEDIUM SCOPE=BOTH;

Oracle now:

• Uses 50GB of SGA for IM.
• Automatically populates “hot” tables.
• Evicts “cold” ones dynamically.

 

INMEMORY_DEEP_VECTORIZATION — CPU-Level SIMD Optimization for Scans

What It Is

Introduced around Oracle 21c, this parameter enables deep vectorization — an enhancement that uses modern CPU SIMD instructions (AVX2, AVX-512) to process multiple column values per CPU instruction cycle.

 

Definition

INMEMORY_DEEP_VECTORIZATION = {ENABLE | DISABLE | AUTO}

Value	Meaning
ENABLE	Forces Oracle to use the new SIMD-based deep vectorization engine for all IM scans.
AUTO (default)	Oracle decides automatically based on CPU capabilities, workload, and statistics.
DISABLE	Disables deep vectorization (falls back to standard IM scan engine).

 

What It Actually Does

In a normal IM query, Oracle scans data column-by-column, applying filters and aggregations row-by-row in compressed IMCUs.

With deep vectorization:

• Oracle groups multiple values (e.g., 8–16 doubles or integers) in a single CPU register.
• Applies filters, aggregates, and joins using vector instructions, not loops.
• Achieves 2–4× faster analytics on CPUs that support AVX2/AVX512 (Intel/AMD).

This optimization is purely CPU/algorithmic and does not affect IM size.

 

Interaction with IM Size

• No impact on allocated size.
  It just accelerates operations inside existing IMCUs.
• If your INMEMORY_SIZE is small, you still benefit — but only for objects that fit in memory.
• It can reduce CPU time per query, effectively increasing throughput per GB of IM memory.

 

Real-World Example

Configuration

ALTER SYSTEM SET INMEMORY_SIZE = 100G SCOPE=SPFILE;

ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = MEDIUM SCOPE=BOTH;

ALTER SYSTEM SET INMEMORY_DEEP_VECTORIZATION = AUTO SCOPE=BOTH;

 

Query

SELECT dept_id, AVG (salary), COUNT (*)

FROM employees

GROUP BY dept_id;

• INMEMORY_AUTOMATIC_LEVEL=MEDIUM ensures employees table likely resides in memory.
• INMEMORY_DEEP_VECTORIZATION=AUTO allows Oracle to use CPU vector instructions for scanning and aggregation.
• Result: Fast scan, minimal CPU, zero buffer cache I/O.

 

 

 

Summary Comparison

 

Feature	Purpose	Affects IM Size?	Benefit
INMEMORY_SIZE	Defines total memory for IM column store	Yes	Sets physical capacity
INMEMORY_AUTOMATIC_LEVEL	Controls auto population & eviction	No	Better utilization of IM area
INMEMORY_DEEP_VECTORIZATION	Enables SIMD-level CPU parallelism	No	Faster IM scans & aggregations

 

Best Practice Recommendations

 

Environment	Suggested Settings
Static DW / known tables	INMEMORY_AUTOMATIC_LEVEL=OFF, INMEMORY_DEEP_VECTORIZATION=AUTO
Dynamic or mixed workloads	INMEMORY_AUTOMATIC_LEVEL=MEDIUM or HIGH
Modern CPU (Intel Xeon, AMD EPYC)	Keep INMEMORY_DEEP_VECTORIZATION=AUTO (don’t disable)
Tight SGA budget	Tune INMEMORY_SIZE carefully; use AUTOMATIC_LEVEL to avoid ORA-64307