Avoid Performance Problems With 19c In-Memory Options

May 11, 2023

You can avoid performance problems with 19c In-Memory options available from Oracle. Oracle Ace Anuj Mohan, as a part of the Quest Oracle Community’s Database & Technology Week, offered a deep dive on features including In-Memory, Configuring IMCS, Populating IMCS, IMCS Advisors, and In-Memory Features.

In-Memory Introduction

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

Here are some of the key ones:

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 the In-Memory Option.

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 Application 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 the 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 the 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 Database In-Memory is not enabled by default. You’ll need to set the 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

If you’re not using In-Memory, you should start experimenting with it, as there are several benefits to it. Starting with 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 the CDB level and PDB levels. You should be able to set priority on the PDB level. Sum of INMEMORY_SIZE on all PDBs can be more than the INMEMORY_SIZE parameter of CDB, and oversubscription is possible.

Here are some benefits of In-Memory Operation:

Efficient Joins mean only needed columns are scanned and filtered.
Use Bloom Filters Scan on compressed data.
Columns In-Memory have a smaller footprint as they are compressed.
The use of SIMD lets your CPU cycles read data more efficiently.
Memory Aggregation is created using the “vector group by” clause.

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) does the following:

Analyzes existing DB workload via AWR and ASH repositories
Provides a 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. You’ll receive enough data to make a good decision. The 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.

In-Memory Features

The Oracle In-Memory Column Store Storage Index works based on elimination instead of sequences. MCS 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.

The DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE procedure enables the package. The V$INMEMORY_FASTSTART_AREA view stores information about segments in IMCS. Another 12.2 feature is the 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.

Here is an easy implementation example:

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, or 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 are 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.