In-Memory Column Store in Oracle Database 12c

The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patchset. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The advantage of having data in memory is obvious, but the columnar storage lends itself extremely well to analytic queries found in business intelligence products.

 The IM column store is a separately licensed option of Oracle Database Enterprise Edition.

• Introduction
• Enable In-Memory Column Store
• Disable In-Memory Column Store
• Managing Tables
• Managing Columns
• Managing Materialized Views
• Managing Tablespaces
• Views

Introduction

The In-Memory column store is a new section of the SGA, sized using the INMEMORY_SIZE initialization parameter. You can choose to store specific groups of columns, whole tables, materialized views or table partitions in the store. Alternatively, you can enable IM column store at the tablespace level, so all tables and materialized views in the tablespace are automatically enabled for the IM column store. The following commands have been modified to include additional in-memory clauses.

CREATE TABLE
ALTER TABLE
CREATE TABLESPACE
ALTER TABLESPACE
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW

Basic examples of their use will be shown below.

The documentation claims the IM column store is good for the following.

• Large scans that apply "=", "<", ">" and "IN" filters.
• Queries that return a small number of columns from a table with a large number of columns.
• Queries that join small tables to large tables.
• Queries that aggregate data.

It also states it is not designed for the following.

• Queries with complex predicates.
• Queries that return a large number of columns.
• Queries that return large numbers of rows.
• Queries with multiple large table joins.

The important thing to remember here is *you* will be responsible for deciding which objects will benefit the most from inclusion in the IM column store. If you choose wisely you will see big improvements in performance. If you choose badly, you will waste a lot of memory that could be used by the buffer cache.

Full lists of initialization parameters, compression methods and population options are available from the documentation. I'm not going to repeat them here.

• Initialization Parameters Related to the IM Column Store
• IM Column Store Compression Methods
• IM Column Store Data Population Options

Enable In-Memory Column Store

Remember, the IM column store is part of the SGA, so the SGA must be capable of containing the amount of memory you want to assign to theINMEMORY_SIZE parameter. In a multitenant environment, the INMEMORY_SIZE parameter must be set in the CDB if any of the PDBs need access to the IM column store.

• If you are using AMM (MEMORY_TARGET), you will need to extend this to account for the INMEMORY_SIZE parameter value.
• If you are using ASMM (SGA_TARGET), you will need to extend this to account for the INMEMORY_SIZE parameter value.

Assuming the COMPATIBLE parameter is set to 12.1.0 or higher and there is enough room in the SGA to hold the IM column store, the following process will enable the IM column store. In this case I am setting the INMEMORY_SIZE parameter to 2G.

ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE;
ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2929552 bytes
Variable Size             419433584 bytes
Database Buffers          637534208 bytes
Redo Buffers               13844480 bytes
In-Memory Area           2147483648 bytes
Database mounted.
Database opened.
SQL>

Notice the "In-Memory Area" line produced during the startup.

The current IM settings are shown below. With the exception of the size, all others are default values.

SQL> SHOW PARAMETER INMEMORY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 2G
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SQL>

The INMEMORY_SIZE parameter setting is inherited by all PDBs unless it is explicitly set at the PDB level. Changing the INMEMORY_SIZE parameter value at the PDB level does not require a restart of the instance or PDB.

CONN sys@pdb1 AS SYSDBA
-- Disable IM column store in the PDB
ALTER SYSTEM SET INMEMORY_SIZE=0;
-- OR
ALTER SYSTEM RESET INMEMORY_SIZE;

-- Assign a PDB-specific size.
ALTER SYSTEM SET INMEMORY_SIZE=1G;

Disable In-Memory Column Store

There are several ways to disable the IM column store, depending on what you are trying to achieve.

Setting the INMEMORY_FORCE parameter to "OFF" means objects will not be maintained in the IM column store. Switching it back to "DEFAULT" returns to the default behaviour.

-- System level
ALTER SYSTEM SET INMEMORY_FORCE=OFF;
ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;

Setting the INMEMORY_QUERY parameter to "DISABLE" means the optimiser will not consider the IM column store to optimise queries. Switching it back to "ENABLE" reverts it to the default functionality.

-- System level
ALTER SYSTEM SET INMEMORY_QUERY=DISABLE;
ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;

-- Session level
ALTER SESSION SET INMEMORY_QUERY=DISABLE;
ALTER SESSION SET INMEMORY_QUERY=ENABLE;

To disable the IM column store completely and release the memory, reset the INMEMORY_SIZE parameter.

ALTER SYSTEM RESET INMEMORY_SIZE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

As described in the previous section, the PDB-specific settings can be altered without an instance or PDB restart.