Learn Oracle database 26ai Automatic In-Memory (AIM) Deep Dive: Heat Map, ADO Policies, and Intelligent Memory Management

Why Automatic In-Memory Matters

Are you manually selecting tables for In-Memory and struggling with memory limits?

In real environments, workloads constantly change. A table that is frequently accessed today may become irrelevant tomorrow.

Oracle Automatic In-Memory (AIM) solves this problem by automatically managing:

• Which objects should be in memory

• When to populate data

• When to evict unused objects

This allows DBAs to move from manual tuning to intelligent automation.

If you have not configured In-Memory yet, read:

https://oracledbexpert.com/blog/how-to-enable-and-size-oracle-in-memory-column-store-configuration-memory-planning-and-best-practices

What Is Automatic In-Memory (AIM)

Automatic In-Memory is a feature that dynamically manages the In-Memory Column Store based on workload patterns.

Instead of manually controlling everything, Oracle:

• Tracks usage

• Identifies hot objects

• Automatically populates or evicts data

Step 1: Enable Automatic In-Memory

Enable AIM

ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = HIGH;

Verify Setting

SHOW PARAMETER INMEMORY_AUTOMATIC_LEVEL;

How AIM Works Internally

Automatic In-Memory relies on two key components:

• Heat Map

• ADO (Automatic Data Optimization)

Heat Map: Tracking Data Usage

Heat Map tracks how data is accessed over time.

Types of Tracking

• Read activity

• Write activity

• Frequency of access

Types of Tracking

• Read activity

• Write activity

• Frequency of access

Enable Heat Map

ALTER SYSTEM SET HEAT_MAP = ON;

ADO (Automatic Data Optimization)

ADO uses Heat Map statistics to automate decisions.

ADO (Automatic Data Optimization)

ADO uses Heat Map statistics to automate decisions.

Create ADO Policy

ALTER TABLE sales ILM ADD POLICY
INMEMORY
AFTER 1 DAY OF ACCESS;

Eviction Policy Example

ALTER TABLE sales ILM ADD POLICY
NO INMEMORY
AFTER 30 DAYS OF NO ACCESS;

Automatic Population

When AIM is enabled:

• Frequently accessed objects are automatically loaded

• Less-used objects are removed

Population Priority

Oracle internally assigns priority based on usage patterns.

You can still override manually:

ALTER TABLE sales INMEMORY PRIORITY HIGH;

Automatic Eviction

When memory is full:

• Cold objects are removed

• Hot objects remain

Benefit

• No manual cleanup

• Efficient memory usage

Controlling AIM Behavior

You can fine-tune AIM using parameters.

Control Interval

ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = HIGH;

Disable AIM

ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = OFF;

Using DBMS_AUTOIM API

Oracle provides APIs to manage Automatic In-Memory.

Example

BEGIN
  DBMS_AUTOIM.ENABLE;
END;
/

Monitor AIM Activity

SELECT * FROM v$im_segments;

Monitor AIM Activity

SELECT * FROM v$im_segments;

Solution

Enable AIM:

ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = HIGH;

Oracle will:

• Keep frequently accessed tables

• Remove unused ones

Performance Benefits

Automatic In-Memory provides:

• Optimized memory usage

• Reduced manual effort

• Faster query execution

• Adaptive performance

Common Mistakes

Not Enabling Heat Map

Without Heat Map, AIM cannot function properly.

Overriding AIM Too Much

Manual control may conflict with automation.

Ignoring Monitoring

Always monitor how AIM behaves.

Best Practices

• Enable Heat Map before AIM

• Use ADO policies carefully

• Combine manual and automatic strategies

• Monitor regularly

• Test in staging environment

Next Article

Manual In-Memory Configuration: INMEMORY Clause Deep Dive