Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

In-Memory Column Store in Oracle Database 12c Release 1 (12.1.0.2)

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.

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

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.

It also states it is not designed for the following.

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.

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 the INMEMORY_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.

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.

Managing Tables

The CREATE TABLE and ALTER TABLE commands have been amended to allow you to determine if the table should be stored in the IM column store. Creating a table with the NO INMEMORY clause is the same as not specifying the clause at all. The example below shows the creation of three tables using three variations on the syntax. The [DBA|ALL|USER]_TABLES views have been amended to include IM-related information.

CONN test/test@pdb1

CREATE TABLE im_tab (
  id  NUMBER
) INMEMORY;

CREATE TABLE noim_tab (
  id  NUMBER
) NO INMEMORY;

CREATE TABLE default_tab (
  id  NUMBER
);

COLUMN table_name FORMAT A20

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
ORDER BY table_name;

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
DEFAULT_TAB          DISABLED
IM_TAB               ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE
NOIM_TAB             DISABLED

3 rows selected.

SQL>

The ALTER TABLE command can change the IM status of the objects. The following example flips the status.

ALTER TABLE IM_TAB NO INMEMORY;
ALTER TABLE NOIM_TAB INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
ALTER TABLE DEFAULT_TAB INMEMORY PRIORITY HIGH;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
ORDER BY table_name;

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
DEFAULT_TAB          ENABLED  HIGH     AUTO            FOR QUERY LOW     NO DUPLICATE
IM_TAB               DISABLED
NOIM_TAB             ENABLED  NONE     AUTO            FOR CAPACITY LOW  NO DUPLICATE

3 rows selected.

SQL>

Managing Columns

The following example shows the syntax for including a subset of columns in the IM column store.

CREATE TABLE im_col_tab (
  id   NUMBER,
  col1 NUMBER,
  col2 NUMBER,
  col3 NUMBER,
  col4 NUMBER
) INMEMORY
INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1, col2)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
NO INMEMORY (id, col4);

The column settings are displayed using the V$IM_COLUMN_LEVEL view.

CONN sys@pdb1 AS SYSDBA

SELECT table_name,
       segment_column_id,
       column_name,
       inmemory_compression
FROM   v$im_column_level
WHERE  owner = 'TEST'
and    table_name = 'IM_COL_TAB'
ORDER BY segment_column_id;

TABLE_NAME           SEGMENT_COLUMN_ID COLUMN_NAME                     INMEMORY_COMPRESSION
-------------------- ----------------- ------------------------------- --------------------------
IM_COL_TAB                           1 ID                              NO INMEMORY
IM_COL_TAB                           2 COL1                            FOR QUERY HIGH
IM_COL_TAB                           3 COL2                            FOR QUERY HIGH
IM_COL_TAB                           4 COL3                            FOR CAPACITY HIGH
IM_COL_TAB                           5 COL4                            NO INMEMORY

5 rows selected.

SQL>

The IM settings can be changed using the ALTER TABLE command.

CONN test/test@pdb1

ALTER TABLE im_col_tab 
NO INMEMORY (col1, col2)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
NO INMEMORY (id, col4);

CONN sys@pdb1 AS SYSDBA

SELECT table_name,
       segment_column_id,
       column_name,
       inmemory_compression
FROM   v$im_column_level
WHERE  owner = 'TEST'
and    table_name = 'IM_COL_TAB'
ORDER BY segment_column_id;

TABLE_NAME           SEGMENT_COLUMN_ID COLUMN_NAME                     INMEMORY_COMPRESSION
-------------------- ----------------- ------------------------------- --------------------------
IM_COL_TAB                           1 ID                              NO INMEMORY
IM_COL_TAB                           2 COL1                            NO INMEMORY
IM_COL_TAB                           3 COL2                            NO INMEMORY
IM_COL_TAB                           4 COL3                            FOR CAPACITY HIGH
IM_COL_TAB                           5 COL4                            NO INMEMORY

5 rows selected.

SQL>

Managing Materialized Views

The CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW commands work in a similar was to the CREATE TABLE and ALTER TABLE commands.

CONN test/test@pdb1

CREATE TABLE t1 AS
  SELECT * FROM all_objects;

CREATE MATERIALIZED VIEW t1_mv INMEMORY 
  AS SELECT * FROM t1;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
WHERE  table_name = 'T1_MV';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
T1_MV                ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE

1 row selected.

SQL>


ALTER MATERIALIZED VIEW t1_mv
  INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY HIGH;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
WHERE  table_name = 'T1_MV';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
T1_MV                ENABLED  HIGH     AUTO            FOR CAPACITY HIGH NO DUPLICATE

1 row selected.

SQL>


ALTER MATERIALIZED VIEW t1_mv NO INMEMORY;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
WHERE  table_name = 'T1_MV';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
T1_MV                DISABLED

1 row selected.

SQL>

Managing Tablespaces

Setting the default IM column store parameters for a tablespace means all tables and materialized views in that tablespace will use those setting unless explicitly overridden.

The following example shows how to set the IM column store parameters during a tablespace creation. The keyword DEFAULT is part of the IM clause. The default IM settings are displayed using the DBA_TABLESPACES view.

CONN sys@pdb1 AS SYSDBA

CREATE TABLESPACE new_ts
   DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf' SIZE 10M 
   DEFAULT INMEMORY;

SELECT tablespace_name, 
       def_inmemory,
       def_inmemory_priority,
       def_inmemory_distribute,
       def_inmemory_compression,
       def_inmemory_duplicate
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ -------- -------- --------------- ----------------- -------------
NEW_TS                         ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TEMP                           DISABLED
USERS                          DISABLED

5 rows selected.

SQL>

The ALTER TABLESPACE command is used to change the IM column store parameters.

ALTER TABLESPACE new_ts
  DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

SELECT tablespace_name, 
       def_inmemory,
       def_inmemory_priority,
       def_inmemory_distribute,
       def_inmemory_compression,
       def_inmemory_duplicate
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ -------- -------- --------------- ----------------- -------------
NEW_TS                         ENABLED  NONE     AUTO            FOR CAPACITY HIGH NO DUPLICATE
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TEMP                           DISABLED
USERS                          DISABLED

SQL>


ALTER TABLESPACE new_ts
  DEFAULT NO INMEMORY;

SELECT tablespace_name, 
       def_inmemory,
       def_inmemory_priority,
       def_inmemory_distribute,
       def_inmemory_compression,
       def_inmemory_duplicate
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ -------- -------- --------------- ----------------- -------------
NEW_TS                         DISABLED
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TEMP                           DISABLED
USERS                          DISABLED

5 rows selected.

SQL>

The default settings can be overridden by specific object settings shown above.

Views

We've already seen some of the existing views have been modified to include IM column store information. The following V$ views have been added in to support the IM column store.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.