You can avoid performance problems with 19c In-Memory options. Read more for information on an introduction to In Memory, Configuring IMCS, Populating IMCS, IMCS Advisors, and In Memory Features.

19c In-Memory Introduction

After applying the first patch option for Database 12c Release 1, several features become available. Key features include:

• Advanced Index Compression
• Approximate Count Distinct
• Automatic Big Table Caching
• Full Database Caching
• JSON Support
• PDB CONTAINERS Clause
• PDB Subset Cloning
• Rapid Home Provisioning
• Partitioned External Tables

However, the biggest feature to arrive in the first patch is In-Memory Option.

19c In-Memory Option

Traditionally data in tables has been stored in row format. This is good for operations in which the whole row and many columns are accessed—as long as there are few rows. It’s not ideal for many rows. This can be an issue for OLTP operations like Insert/Update.

Analytical queries usually access fewer columns but multiple rows. Data stored in columnar format can do wonders. The column data is tightly packed together, improves access efficiency, and reduces memory traffic.

Before the 12.1 In-Memory patch, you had to choose between row and column format. Now, you can have both. Oracle will transparently manage transactional consistency of IMCS data.

IMCS Facts

• IMCS do not require any Applications change
• CBO knows if IMCS or Buffer Cache is more efficient
• Oracle maintains two copies of data simultaneously—Buffer Cache and IMCS
• ora_w001_orcl new BG Process populates in IMCS
• INMEMORY_MAX_POPULATE_SERVERS defines how many are processed. By default, it is half of the effective CPU thread count.

Configuring IMCS

Installation

There is no specific installation step for 19c In-Memory option.

If you are running Oracle Database 12c Release 1, you’ll apply the first patch set 1 (12.1.0.2).

If you are on 12c to 21c, In-Memory is automatically installed. However, you do need to activate it.

Initialize In-Memory by setting a parameter to INMEMORY_SIZE. This defines the size of In-Memory Column Store.

Ideally you should not be taking memory from SGA to allocate IMCS. SGA_TARGET should be big enough to accommodate SGA+IMCS.

In-Memory is a separate pool.

If you dissect it further, there are two sub-pools. The IMCU pool stores In Memory Compression Units (IMCUs) as column processed data. The SMU pool for Snapshot Metadata Units (SMUs) stores metadata and transactional information.

Configuration

If you decide to implement IMCS, make sure to provision additional memory. This cannot be stressed enough—do not take it from the existing SGA. The minimum size is 100MB. If you’re using Oracle RAC, add 10% more.

The 19c In-Memory is not enabled by default. You’ll need to set INMEMORY_SET parameter.

Below are some target recommendations:

• SGA_TARGET = Existing SGA_TARGET + INMEMORY_SIZE (for RAC add 10%)
• PGA_TARGET = PARALLEL_MAX_SERVERS + 2GB

Here are some parameters regarding In-Memory columns:

*The key parameter is inmemory force with a base level value.

If you’re not using In-Memory, you should start experimenting with it. There are several benefits to it.

Starting 12.1, you could dynamically resize. This is still true. You can increase it live, but you cannot decrease it.

If you’re using Oracle Multitenant, you can use In Memory with it. You’ll need to define In-MEMORY_SIZE parameters on CDB level and PDB levels. You should be able to set priority on PDB level. Sum of INMEMORY_SIZE on  all PDBs cab be more than INMEMORY_SIZE parameter of CDB, and oversubscription is possible.

This shows that the parameter is modifiable and the PBD delivered:

Benefits of 19c In-Memory Operation

• Efficient Joins – Only needed columns are scanned and filtered
• Use Bloom Filters Scan happens on compressed data
• Columns In-Memory have a smaller footprint as they are compressed
• Uses SIMD so your CPU cycles will read data more efficiently
• Creates In-Memory Aggregation using the “vector group by” clause.

Populating 19c In-Memory Column Store

At Create time:

CREATE TABLE SOE (xxxx number, xxxx varchar(20), xxxx number) INMEMORY PRIORITY CRITICAL;

CRITICAL > HIGH > MEDIUM > LOW > NONE

Note: The priority here is not speed. Speed is defined by INMEMORY_MAX_POPULATE_SERVERS

You can use selected partitions:

At Create time <Selected Columns>

CREATE TABLE SOE (xxxx number, xxyy varchar(20), xxzz number) INMEMORY PRIORITY CRITICAL NO INMEMORY (xxyy);

The following query will check to see that it’s populated. It will not start populating until IMCS’s first access.

With alter statement:

SQL> alter table SOE.ORDER_ITEMS INMEMORY MEMCOMPRESS FOR QUERY LOW; Table altered.

SQL> select segment_name, populate_status, inmemory_priority, inmemory_size, bytes_note_populated from v$im_segments; 2

no rows selected

After running the query on the table, it will start populating:

Removing In-Memory Column Store

You do not have to make a change in your query. If the table is In-Memory Column Store, it will be used. You can see in the explain plan:

If, for some reason, you have a problem, then you can disable the In-Memory:

Then you can enable it again:

IMCS Advisors

The Oracle Database IN-Memory Advisor helps you to identify which tables should go into IMCS.

In-Memory Advisor (see OTN or MOS Note 196543.1)

• Analyzes existing DB workload via AWR and ASH repositories
• Provides list of objects that would benefit most from being populated into the IM column

Download the zip file from MOS Note 196543.1 and uncompress.

Install with @instimadv. It will ask you for a table space to store the information. Be ready for that. It’s a very simple install.

To uninstall, use SQL>@catnoimadv.sql

This will make your life easier by returning reports such as:

You’ll receive enough data to make a good decision.

Oracle Database Compression Advisory for In-Memory returns a compressed value.

Use DBMS_COMPRESSION to return an estimated compression ratio.

Installation and configuration are very easy for IMCS.

19c In-Memory Features

Oracle In-Memory Column Store Storage Index works based on elimination instead of sequences.

IMCS FastStart optimizes the population of IMCS objects by storing IMCUs directly on disk. The database can read from the IM FastStart area after crash and recovery, or during duplication to a different Oracle RAC instance. It needs a tablespace to store data—approximately twice the size of IMCS.

DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE procedure enables the package.

V$INMEMORY_FASTSTART_AREA view stores information about segments in IMCS.

In-Memory for ADG

If you’re using 12.2, you can use In-Memory for ADG.

You can have IMCS on ADG side. You’ll also need to set INMEMORY_SIZE parameter on ADG. You can have different IMCS on primary and ADG:

• Create Service to access Primary
• Create Service to access Standby
• Define IMCS using new syntax
  • INMEMORY DISTRIBUTE FOR SERVICE

• You can also have IMCS only on the ADG side.

Heat maps were implemented in 12.2. Data access frequency is tracked. Data is added or removed from IMCS based on temperature and memory pressure using Heat Maps. It can be user defined.

Set the following parameters to use it:

• Heat_map=on
• Memory_ado_enabled=true
• Automatic mode works when IMCS is under pressure. Default value is 90% used.
• _imado_mem_thresholds

After enabling Heat Maps, you can set policies.  For example:

• Alter table anuj ILM add policy no inmemory after 10 days of no modification

You need to set init.ora parameter heat_map=on.

You can get great benefits using Join Operation by applying Bloom Filters. This involves joining two columns with where clause on a small table. The where clause will be evaluated first, and the result set of the first operation will be transferred to a bigger table for SCAN. In summary, the Bloom filter converts your multi-table join to a faster SCAN operation.

Hash Joins will take the following steps:

1. Scan vehicle table and decompress rows.
2. Build a hash table based on above data.
3. Scan sales table, decompress matching rows.
4. Probe the hash table using the join column.

This operation is very fast, but it can be even faster with a Join Group:

The database operates on compressed data. In a hash join based on a join group, the database uses an array instead of building a hash table.

CREATE INMEMORY JOIN GROUP join_group_name ( table1(col1), table2(col2) );

Another 12.2 feature is In Memory Expressions.

IMCS can store virtual columns.

If you’re using Exadata, you can use In Memory to format in Exadata Flash. Smart scans receive In Memory benefits. The availability of Flash is much more than memory, meaning you’ll have a bigger In Memory.

An easy implementation example is:

• Alter table Anuj cellmemory
• Alter table Anuj no cellmemory
• Create table mohan (name varchar2(32)) cellmemory memcompress for query low

You can utilize these for tables, partitions, MV if they are using EHCC. You can also monitor usage with “cellmemory IM scan%”.

In 18c, IMCS is automatically managed based on Heat Map. You’ll need to enable it. If memory pressure is identified, then the least active object(s) will be evicted.

You also have the option to use In-Memory in external tables. External Tables and Big Data SQL is already supported by Oracle. You can use In-Memory External Tables to populate any type of data in the IMCS.

In-Memory Dynamic Scans use additional available CPU to parallelize IM scans. This is controlled by the Resource Manager.

FREE In Memory 19c : base_level

In Memory capabilities at the base level are slightly limited. However, you can get most of the benefits of In Memory, and it’s free. One thing to note is that you will have a max column limit.

• Make sure you are at 19.8
• Set inmemory_force=base_level***
• Set inmemory_size=6G
• Set sga_target=12G (existing + 6G_
• Restart database

This will allow inmemory size only up to 16G with no accidental violation. Automatic In-Memory (AIM) is not allowed, but that’s not difficult.

Additional Resources

For more information on 19c In-Memory, check out these links:

• Oracle Database 19c Learning Library
• In-Memory Experts

You can also view a recorded explanation of the information above, by watching this recorded session from Oracle Ace, Anuj Mohan, as a part of the Quest Oracle Community’s Database & Technology Week.