In-Memory Area

🧠 Introduction
Oracle In-Memory sounds complex, right? But it doesn’t have to be.

It’s simply a smart way to speed up analytics and reporting without slowing down regular transactions (OLTP – Online Transaction Processing).

Let’s make this easy to digest.

🧩 What Is the In-Memory Area?
The In-Memory Area is an optional part of the SGA (System Global Area).

It contains the In-Memory Column Store (IM Column Store) — a special memory space where Oracle stores your data in column format for super-fast scanning.

🧠 Key point:
Oracle stores data in two formats at the same time:
➤ Row format in Buffer Cache (for fast OLTP)
➤ Column format in IM Column Store (for fast analytics)

📌 This smart design doesn’t double memory usage!
The buffer cache stays small, while the IM column store handles large scans.

🧠 What’s Inside the In-Memory Column Store?

1. IMCU (In-Memory Compression Unit)
   ➤ Compressed, read-only blocks
   ➤ Hold columnar data for one or more columns
2. SMU (Snapshot Metadata Unit)
   ➤ Stores metadata and transaction info for its IMCU
3. IMEU (In-Memory Expression Unit)
   ➤ Stores virtual columns and expression results
   ➤ Linked one-to-one with its parent IMCU
4. ESS (Expression Statistics Store)
   ➤ Tracks usage of expressions
   ➤ Helps Oracle decide which expressions to optimize
   ➤ Lives in memory and persists on disk
   📌 ESS is always enabled — not tied to In-Memory Column Store

🔍 Example:
Table EMPLOYEES:

• IMCU holds: weekly_salary
• IMEU stores:
  • Auto-calculated: weekly_salary * 52
  • User-defined virtual column: weekly_salary * 12
  • Row 3 in IMCU = Row 3 in IMEU (1:1 mapping)

💾 In-Memory Area Breakdown

📌 Most of the memory goes to the 1MB pool.

Controlled by: INMEMORY_SIZE (default is 0)
➤ Minimum required: 100MB
➤ Can be increased on the fly (from Oracle 12.2 onwards)
➤ Must increase by at least 128MB
➤ ❌ Cannot be decreased dynamically

⚙️ How and When Does Data Go In-Memory?

➤ When a table with the INMEMORY attribute is queried
➤ Or automatically at database startup

🔄 Data is converted from on-disk row format to columnar format
📌 This conversion happens every time the instance restarts — because the IM Column Store exists only in memory

✅ Even if a table is partially loaded, Oracle can run queries using what’s in memory and fetch the rest from disk
(This is called a hybrid scan)

🤖 Automatic In-Memory Magic

Set INMEMORY_AUTOMATIC_LEVEL = HIGH to let Oracle:
✔️ Decide which objects to store
✔️ Populate objects based on usage
✔️ Recompress or evict segments as needed
✔️ Maximize memory usage automatically

⚙️ Background Processes That Help

• IMCO (In-Memory Coordinator): Triggers population
• SMCO (Space Management Coordinator): Manages memory
• Wnnn (Worker Processes): Do the actual work

🔁 What happens on DML (Data Manipulation Language)?
➤ Changes go to buffer cache → disk
➤ Then background processes repopulate the IM Column Store

🚀 In-Memory FastStart
Want faster startup?

Enable In-Memory FastStart to write the columnar version of your data back to disk (in compressed format) at shutdown — making future startups much faster.

📌 But note: IMEUs (expression results) are not saved — they’re always repopulated from IMCUs

⚡ Performance Boosters to Know

1. In-Memory Hybrid Scans
   ➤ Combine row + column store access
   ➤ Use in-memory data even if not all columns are loaded
2. Deep Vectorization
   ➤ Uses SIMD (Single Instruction, Multiple Data) to boost complex SQL execution
   ➤ Enabled by default
   ➤ Can be turned off with:
   ALTER SYSTEM SET INMEMORY_DEEP_VECTORIZATION = FALSE
3. Base Level Feature
   ➤ Use up to 16GB of column store without licensing impact
   ➤ Great for test/dev or small workloads

🛠️ DBA Tip of the Day
Don’t put everything in memory — just your hot, performance-critical tables or columns.

Start small, monitor usage, and let Oracle automate where possible using INMEMORY_AUTOMATIC_LEVEL.