How to Enable and Size Oracle In-Memory Column Store: Configuration, Memory Planning, and Best Practices

Why Configuration and Sizing Matter

Have you enabled In-Memory but still not seeing performance improvements?

The most common reason is incorrect configuration or poor memory sizing.

Oracle In-Memory Column Store configuration is not just about enabling a parameter. It requires:

• Proper memory planning

• Understanding workload

• Selecting the right objects

If you skip these steps, you may waste memory without gaining performance.

If you are new to architecture, read:

https://oracledbexpert.com/blog/oracle-database-in-memory-architecture-overview-dual-format-im-column-store-and-internal-design

Step 1: Enable In-Memory Column Store

The first step is to allocate memory for the In-Memory Column Store.

Enable In-Memory

ALTER SYSTEM SET INMEMORY_SIZE = 8G SCOPE=SPFILE;

Restart Database

shutdown immediate;
startup;

Verify Configuration

SHOW PARAMETER INMEMORY_SIZE;

Step 2: Understand Memory Architecture

The In-Memory Column Store is part of the SGA.

It does not replace:

• Buffer cache

• Shared pool

Instead, it adds a new memory area specifically for columnar data.

Check Memory Usage

SELECT pool, alloc_bytes, used_bytes
FROM v$inmemory_area;

Step 3: Memory Sizing Strategy

Sizing is the most critical step.

Basic Rule

• Start with 5–10% of total memory

• Increase based on workload

Example

If server has 64GB RAM:

In-Memory Size = 6GB to 12GB

Step 4: Identify Candidate Tables

Not all tables should be in memory.

Good Candidates

• Large tables

• Frequently queried tables

• Analytical workloads

Poor Candidates

• Small tables

• Rarely accessed data

• Highly volatile tables

Identify Hot Objects

SELECT owner, object_name, logical_reads
FROM v$sql_plan_statistics_all
ORDER BY logical_reads DESC;

Step 5: Enable Tables for In-Memory

Enable Table

ALTER TABLE sales INMEMORY;

Enable Specific Columns

ALTER TABLE sales INMEMORY (product_id, amount_sold);

Disable Table

ALTER TABLE sales NO INMEMORY;

Step 6: Control Population Priority

Oracle uses priority levels to decide which tables load first.

Example

ALTER TABLE sales INMEMORY PRIORITY HIGH;

Priority Levels

• CRITICAL

• HIGH

• MEDIUM

• LOW

• NONE

Behavior

• CRITICAL → loaded immediately

• NONE → loaded only when accessed

Step 7: Compression Configuration

Compression affects both performance and memory usage.

Example

ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW;

Compression Options

Step 8: Automatic In-Memory (AIM)

Oracle can manage memory automatically.

Enable AIM

ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = HIGH;

How It Works

• Tracks data access patterns

• Loads frequently used data

• Removes unused data

Step 9: Manual Population

If priority is NONE, manual population is required.

Populate Table

EXEC DBMS_INMEMORY.POPULATE('SALES');

Force Population Wait

EXEC DBMS_INMEMORY_ADMIN.POPULATE_WAIT('SALES');

Step 10: Monitor In-Memory Usage

Check Segments

SELECT segment_name, bytes, populate_status
FROM v$im_segments;

Check Compression

SELECT segment_name, inmemory_compression
FROM v$im_segments;

Common Configuration Mistakes

Allocating Too Much Memory

• Causes eviction issues

• Impacts other SGA components

Enabling Too Many Tables

• Reduces efficiency

• Increases memory pressure

Ignoring Compression

• Wastes memory

• Reduces performance

Not Monitoring Population

• Tables may not be fully loaded

Best Practices

• Start small and scale gradually

• Focus on high-value tables

• Use appropriate compression

• Monitor continuously

• Combine automatic and manual strategies

Next Article

Automatic In-Memory (AIM): Heat Map, ADO, and Smart Memory Management