Configure and manage “In Memory” features

Go back to OCM 12c Preparation Project 

Hands On Lab

6 Managing Memory
6.7 Using the In-Memory Column Store

The In-Memory Column Store (IM column store) is an optional portion of 
the system global area (SGA) that stores copies of tables, table partitions, 
and other database objects. In the IM column store, data is populated by 
column rather than row as it is in other parts of the SGA, and data is 
optimized for rapid scans. The IM column store is included with the 
Oracle Database In-Memory option.


6.7.1 About the IM Column Store
You can choose from various compression methods and data population options 
for the IM column store.


6.7.1.1 Overview of the IM Column Store

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.

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.


Typically, multi-column indexes are created to improve the performance 
of analytic and reporting queries. These indexes can impede the performance 
of data manipulation language (DML) statements. When a database object is 
populated in the IM column store, indexes used for analytic or reporting 
queries can be reduced or eliminated without affecting query performance. 

Eliminating these indexes can improve the performance of transactions 
and data loading operations.

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

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.

Note:
Also, a database object cannot be populated in the IM column store if it 
is owned by the SYS user and it is stored in the SYSTEM or SYSAUX tablespace.


Note:
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.


6.7.1.2 IM Column Store Compression Methods
In the IM column store, data can be compressed, and SQL queries execute 
directly on compressed data.

Table 6-5 IM Column Store Compression Methods

Compression Method in CREATE/ALTER Syntax

NO MEMCOMPRESS 
-> The data is not compressed.
MEMCOMPRESS FOR DML 
-> This method optimizes the data for DML operations and compresses 
IM column store data the least (excluding NO MEMCOMPRESS).
MEMCOMPRESS FOR QUERY LOW 
-> This method results in the best query performance.
-> This method is the default when the INMEMORY clause is specified 
   without a compression method.
   or when MEMCOMPRESS FOR QUERY is specified without including 
   either LOW or HIGH.

MEMCOMPRESS FOR QUERY HIGH 
-> This method results in excellent query performance.

MEMCOMPRESS FOR CAPACITY LOW 
-> This method results in good query performance.
-> This method is the default when MEMCOMPRESS FOR CAPACITY is specified 
   without including either LOW or HIGH.

MEMCOMPRESS FOR CAPACITY HIGH  
-> This method results in fair query performance.
-> This method compresses IM column store data the most.

6.7.1.3 IM Column Store Data Population Options

IM Column Store Data Population Options
Table 6-6 Priority Levels for Populating a Database Object in the IM 
Column Store

Specify Priority Levels during CREATE/ALTER Syntax

PRIORITY NONE
Oracle Database controls when the database object's data is populated in 
the IM column store. A scan of the database object triggers the population 
of the object into the IM column store. This is the default level when 
PRIORITY is not included in the INMEMORY clause.

PRIORITY LOW
The database object's data is populated in the IM column store before 
database objects with the following priority level: NONE.
The database object's data is populated in the IM column store after 
database objects with the following priority levels: MEDIUM, HIGH, or 
CRITICAL.

PRIORITY MEDIUM
The database object's data is populated in the IM column store before 
database objects with the following priority levels: NONE or LOW.
The database object's data is populated in the IM column store after 
database objects with the following priority levels: HIGH or CRITICAL.

PRIORITY HIGH
The database object's data is populated in the IM column store before 
database objects with the following priority levels: NONE, LOW, or MEDIUM.
The database object's data is populated in the IM column store after 
database objects with the following priority level: CRITICAL.

PRIORITY CRITICAL
The database object's data is populated in the IM column store before 
database objects with the following priority levels: NONE, LOW, MEDIUM, 
or HIGH.

------
Notes
------
1. When more than one database object has a priority level other than NONE, 
Oracle Database queues all of the data for the database objects to be 
populated in the IM column store based on priority level. 
Data for database objects with the CRITICAL priority level are populated 
first, data for database objects with the HIGH priority level are populated 
next, and so on. If there is no space remaining in the IM column store, 
then no additional objects are populated in it until sufficient space becomes 
available.

2. When a database is restarted, all of the data for database objects with 
a priority level other than NONE are populated in the IM column store 
during startup.

3. For a database object with a priority level other than NONE, an 
ALTER TABLE or ALTER MATERIALIZED VIEW DDL statement involving the database 
object does not return until the DDL changes are recorded in the 
IM column store.

4. The priority level setting must apply to an entire table or to a 
table partition. Specifying different IM column store priority levels for 
different subsets of columns in a table is not allowed.

5. If a segment on disk is 64 KB or less, then it is not populated in the 
IM column store. Therefore, some small database objects that were enabled 
for the IM column store might not be populated in it.

6.7.2 Initialization Parameters Related to the IM Column Store

A set of initialization parameters are related to the IM column store.

-------------
INMEMORY_SIZE
-------------
-> The default value is 0, which means that the IM column store is not used.
-> Must be set to a non-zero value to enable the IM column store. 
If the parameter is set to a non-zero value,then the minimum setting is 
100M.

Example: Configure the database to use IM Column Store;

$ sql
SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 4 17:03:49 2019

SQL> show parameter inmemory_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_size big integer 0
SQL>

SQL> alter system set inmemory_size=100M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 708837480 bytes
Database Buffers 251658240 bytes
Redo Buffers 5455872 bytes
In-Memory Area 104857600 bytes
Database mounted.
Database opened.

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


SQL> show parameter inmemory_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_size big integer 100M

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.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
SQL>
SQL> alter session set container=pdb2;

Session altered.

SQL> show parameter inmemory_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_size big integer 100M
SQL>

-- Disable IM column store in the PDB
ALTER SYSTEM SET INMEMORY_SIZE=0;
-- OR
ALTER SYSTEM RESET INMEMORY_SIZE;

-- Assign a PDB-specific size.

SQL> alter system set inmemory_size=10M;

System altered.

--------------- 
INMEMORY_FORCE
---------------
-> The default value is DEFAULT to allow the INMEMORY or NO INMEMORY 
attributes on the individual database objects determine if they will 
be populated in the IM column store for tables and materialized views
-> Setting the parameter to "OFF" means objects will not be maintained 
in the IM column store.

Example : System level

ALTER SYSTEM SET INMEMORY_FORCE=OFF;
ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;

-----------------------
INMEMORY_CLAUSE_DEFAULT
-----------------------
-> This parameter enables you to specify a default IM column store clause 
for new tables and materialized views.
-> Leave this parameter unset or set it to an empty string or NO INMEMORY 
to specify that there is no default IM column store clause for new tables 
and materialized views.

-> If the clause starts with INMEMORY, then all new tables and materialized 
views, including those without an INMEMORY clause, are populated in the IM 
column store. If the clause omits INMEMORY, then it only applies 
to new tables and materialized views that are enabled for the IM column 
store with an INMEMORY clause during creation.

The following statement causes no tables to populate the IM column store:
alter system set INMEMORY_CLAUSE_DEFAULT='NO INMEMORY' scope=spfile;

The following statement causes new tables and materialized views 
(except those specified as NO INMEMORY) to populate the IM column store 
at the high capacity compression level:

alter system set 
INMEMORY_CLAUSE_DEFAULT='INMEMORY MEMCOMPRESS FOR CAPACITY HIGH' scope=spfile;

The following statement causes new tables (even those specified as NO INMEMORY) 
to populate the IM column store at the highest performance compression level 
at LOW priority:

alter system set 
INMEMORY_CLAUSE_DEFAULT='INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY LOW' 
scope=spfile;

The following statement causes new tables (even those specified as NO INMEMORY) 
to populate the IM column store without any in-memory compression:

alter system 
set INMEMORY_CLAUSE_DEFAULT='INMEMORY NO MEMCOMPRESS' scope=spfile;

The following statement causes tables in the IM column store to be duplicated 
on every Oracle RAC instance, unless on a non-engineered system. 
For a non-engineered system, the duplicate-clause (DUPLICATE ALL) will be 
ignored and tables in the column store will be automatically distributed 
across the Oracle RAC instance, with only one copy of each IMCU in the 
tables in the IM column store:

alter system set 
INMEMORY_CLAUSE_DEFAULT='INMEMORY MEMCOMPRESS FOR QUERY DISTRIBUTE AUTO DUPLICATE ALL'
scope=spfile;

The following statement sets the value of the INMEMORY_CLAUSE_DEFAULT 
parameter back to its default value, the empty string:

alter system set INMEMORY_CLAUSE_DEFAULT='' scope=spfile;

---------------
INMEMORY_QUERY
---------------
-> This parameter specifies whether in-memory queries are allowed. 
Set this parameter to ENABLE, the default value, 
to allow queries to access database objects populated in the IM column store, 
-> set this parameter to DISABLE to disable access to the database objects 
populated in the IM column store.

Example:

-- 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;

-----------------------------
INMEMORY_MAX_POPULATE_SERVERS
-----------------------------
This parameter specifies the maximum number of background populate servers 
to use for IM column store population, so that these servers do not overload 
the rest of the system. Set this parameter to an appropriate value based on 
the number of cores in the system.

When INMEMORY_MAX_POPULATE_SERVERS is set to 0, objects cannot be populated 
in the IM column store.

-> This parameter has meaning only if the INMEMORY_SIZE parameter is also 
set to a positive value.

SQL> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_max_populate_servers integer 0
inmemory_size big integer 0

SQL> alter system set inmemory_size=100m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
startup

ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 339741808 bytes
Database Buffers 352321536 bytes
Redo Buffers 5455872 bytes
In-Memory Area 104857600 bytes
Database mounted.
Database opened.
SQL> SQL>
SQL>
SQL> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_max_populate_servers integer 1
inmemory_size big integer 100M

SQL> show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 2
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 2

-> Default value is Half the effective CPU thread count or 
the PGA_AGGREGATE_TARGET value divided by 512M, whichever is less.

INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT

-> This parameter limits the maximum number of background populate servers 
used for In-Memory Column Store repopulation, as trickle repopulation is 
designed to use only a small percentage of the populate servers. 
The value for this parameter is a percentage of the 
INMEMORY_MAX_POPULATE_SERVERS parameter value.

-> This parameter has meaning only if the INMEMORY_SIZE parameter is also 
set to a positive value.
For example, if this parameter is set to 5 and INMEMORY_MAX_POPULATE_SERVERS 
is set to 10, then on average half of a core is used for trickle repopulation.

-> The default value of 1 is good in most cases. In some cases, if you 
want to disable trickle repopulate, this parameter can be set to 0. 
If you want to keep the system more aggressively up to date (at the expense 
of more background CPU), you can set the parameter to higher values 
such as 5 or 10.

------------------------
OPTIMIZER_INMEMORY_AWARE
------------------------
-> Default value is true.
-> This parameter enables or disables all of the optimizer cost model 
enhancements for in-memory. 
-> Setting the parameter to FALSE causes the optimizer to ignore the 
in-memory property of tables 
during the optimization of SQL statements.

Example : Disable In-Memory Column Store

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

1. Setting the INMEMORY_FORCE parameter to "OFF" means objects will not 
be maintained in the IM column store.

ALTER SYSTEM SET INMEMORY_FORCE=OFF;

2. 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;

-- Session level
ALTER SESSION SET INMEMORY_QUERY=DISABLE;

3. 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; 

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

6.7.4 Enabling and Disabling Tables for the IM Column Store

#Managing Tables

You enable a table for the IM column store by including an INMEMORY 
clause in a CREATE TABLE or ALTER TABLE statement. 
You disable a table for the IM column store by including a NO INMEMORY 
clause in a CREATE TABLE or ALTER TABLE statement.

SQL> connect test/test@racnode2/pdb3
Connected.
SQL>
SQL> CREATE TABLE im_tab (
id NUMBER
) INMEMORY; 2 3

Table created.

SQL> CREATE TABLE noim_tab (
id NUMBER
) NO INMEMORY;

Table created.

SQL> CREATE TABLE default_tab (
id NUMBER
);
Table created.

SQL> 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


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

SQL> ALTER TABLE IM_TAB NO INMEMORY;

Table altered.

SQL> ALTER TABLE NOIM_TAB INMEMORY MEMCOMPRESS FOR CAPACITY LOW;

Table altered.

SQL> ALTER TABLE DEFAULT_TAB INMEMORY PRIORITY HIGH;

Table altered.

SQL> 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


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

SQL> 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.

SQL> 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; 2 3 4 5 6 7 8

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

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

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

6.7.6 Enabling and Disabling Materialized Views for the IM Column Store

#Managing Materialized Views
CREATE TABLE t1 AS
SELECT * FROM all_objects; 2

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

alter materialized view t1_mv
inmemory memcompress for capacity high priority high;

Materialized view altered.

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


ALTER MATERIALIZED VIEW t1_mv NO INMEMORY;

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

6.7.5 Enabling and Disabling Tablespaces for the IM Column Store
#Managing Tablespaces

SQL> alter session set container=pdb3;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/cdb2/undotbs01.dbf
/u03/app/oracle/oradata/cdb2/pdb3/system01.dbf
/u03/app/oracle/oradata/cdb2/pdb3/sysaux01.dbf
/tmp/userspdb3.dbf

SQL> create tablespace test_im
2 datafile '/u03/app/oracle/oradata/cdb2/pdb3/test_im.dbf' size 10M autoextend on
3 default INMEMORY;

Tablespace created.

SQL> 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_
----------------- -----------------------------------------------------------------------------------
SYSAUX DISABLED
SYSTEM DISABLED
TEMP DISABLED
TEST_IM ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
USERS DISABLED

SQL> alter tablespace test_im
2 default inmemory memcompress for capacity high;

Tablespace altered.

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


SQL> alter tablespace test_im
2 default no inmemory;

Tablespace altered.


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

#Views
V$IM_COLUMN_LEVEL
V$IM_SEGMENTS
V$IM_USER_SEGMENTS

-> Example about how to create a Table and enable it for the IM column store:

CREATE TABLE oraclelearn (
id NUMBER(5) PRIMARY KEY,
blog_type VARCHAR2(15))
INMEMORY;

-> There are several priorities and each one belongs to a determined SQL-Key as below:

ALTER TABLE oraclelearn INMEMORY PRIORITY NONE;
ALTER TABLE oraclelearn INMEMORY PRIORITY HIGH;
ALTER TABLE oraclelearn INMEMORY PRIORITY LOW;
ALTER TABLE oraclelearn INMEMORY PRIORITY MEDIUM;
ALTER TABLE oraclelearn INMEMORY PRIORITY CRITICAL;

Each priority determines how Oracle will manage and populate the datas of the object in the IM Column Store.

You can easily check whether your table are IN-MEMORY using the following select:

SELECT table_name, inmemory_compression, inmemory_priority , inmemory_distribute FROM user_tables;

OR

SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;

— Disable the oraclelearn for the IM Column Store:
ALTER TABLE oraclelearn NO INMEMORY;

6.7.7 Data Pump and the IM Column Store

-> You can import database objects that are enabled for the IM column 
store using the TRANSFORM=INMEMORY:y option of the impdp command. 
With this option, Data Pump keeps the IM column store clause for all 
objects that have one.

-> When the TRANSFORM=INMEMORY:n option is specified, Data Pump drops 
the IM column store clause from all objects that have one.

Example:

INMEMORY=Y|N

You can also use the TRANSFORM=INMEMORY_CLAUSE:string option to override 
the IM column store clause for a database object in the dump file during 
import. For example, you can use this option to change 
the IM column store compression for a database object being imported.

Example:
INMEMORY_CLAUSE=\"NO INMEMORY\"
INMEMORY_CLAUSE=\"INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY LOW\"


#VIEWS and SQL key statements:

V$IM_SEGMENTS
v$im_column_level
USER_TABLES
USER_TABLESPACES
DBA_DIRECTORIES
SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION 
FROM V$IM_SEGMENTS;

---------------------------
HOL #Test Performance of IM 
---------------------------

15:18:40 SQL> create table test_inmemory
15:18:47 2 (id number,name varchar2(50));

Table created.

SQL>
BEGIN
FOR I IN 1..100000
LOOP
INSERT INTO TEST_INMEMORY
VALUES(I,'Hitesh'||I);
END LOOP;
END;
/

PL/SQL procedure successfully completed.

SQL> insert into test_inmemory select * from test_inmemory;

100000 rows created.

SQL> commit;

Commit complete.

15:22:14 SQL> select count(*) from test_inmemory;

Execution Plan
----------------------------------------------------------
Plan hash value: 1029766195

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 171 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_INMEMORY | 213K| 171 (1)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

15:23:01 SQL> alter table test_inmemory inmemory;

Table altered.

15:23:24 SQL> select count(*) from test_inmemory;

Execution Plan
----------------------------------------------------------
Plan hash value: 1029766195
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS INMEMORY FULL| TEST_INMEMORY | 229K| 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

15:23:38 SQL>

-----------------------------------------------
HOL Test with IM with DataPump TRANSFORM option
-----------------------------------------------

-bash-4.1$ expdp SH/SH@racnode2/pdb2 tables=TEST_INMEMORY directory=PDB2_DIR

Export: Release 12.1.0.2.0 - Production on Thu Sep 5 15:33:12 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SH"."SYS_EXPORT_TABLE_01": SH/********@racnode2/pdb2 tables=TEST_INMEMORY directory=PDB2_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SH"."TEST_INMEMORY" 3.968 MB 200000 rows
Master table "SH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SH.SYS_EXPORT_TABLE_01 is:
/tmp/expdat.dmp
Job "SH"."SYS_EXPORT_TABLE_01" successfully completed at Thu Sep 5 15:33:33 2019 elapsed 0 00:00:19


-bash-4.1$ export ORACLE_SID=cdb1
-bash-4.1$ sql

SQL> startup
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> alter session set container=pdb1;

Session altered.

SQL> grant dba to sh identified by sh;

Grant succeeded.

SQL> create directory PDB1_DIR as '/tmp';

Directory created.

SQL> grant read,write on directory PDB1_DIR to sh;

Grant succeeded.

SQL> create tablespace example datafile '/u03/app/oracle/oradata/cdb1/pdb1/example.dbf' size 50M autoextend on;

Tablespace created.

SQL> alter user sh quota unlimited on example;

#TRANSFORM=INMEMORY:y inherits IM export object properties

-bash-4.1$ impdp sh/sh@racnode2/pdb1 directory=PDB1_DIR TRANSFORM=INMEMORY:Y

Import: Release 12.1.0.2.0 - Production on Thu Sep 5 15:40:03 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SH"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SH"."SYS_IMPORT_FULL_02": sh/********@racnode2/pdb1 directory=PDB1_DIR TRANSFORM=INMEMORY:Y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."TEST_INMEMORY" 3.968 MB 200000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SH"."SYS_IMPORT_FULL_02" successfully completed at Thu Sep 5 15:40:29 2019 elapsed 0 00:00:23

-bash-4.1$ sqlplus sh/sh@racnode2/pdb1

SQL> SELECT table_name, inmemory_compression, inmemory_priority , inmemory_distribute FROM user_tables;

TABLE_NAME INMEMORY_COMPRESS INMEMORY INMEMORY_DISTRI
--------------------------------------------------------------
TEST_INMEMORY FOR QUERY LOW NONE AUTO

SQL> drop table test_inmemory purge;


#TRANSFORM=INMEMORY:n does not inherit IM export object properties

-bash-4.1$ impdp sh/sh@racnode2/pdb1 directory=PDB1_DIR TRANSFORM=INMEMORY:N

Import: Release 12.1.0.2.0 - Production on Thu Sep 5 15:59:50 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SH"."SYS_IMPORT_FULL_01": sh/********@racnode2/pdb1 directory=PDB1_DIR TRANSFORM=INMEMORY:N
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."TEST_INMEMORY" 3.968 MB 200000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SH"."SYS_IMPORT_FULL_01" successfully completed at Thu Sep 5 16:00:09 2019 elapsed 0 00:00:18


-bash-4.1$ sqlplus sh/sh@racnode2/pdb1


SQL> SELECT table_name, inmemory_compression, inmemory_priority , 
inmemory_distribute FROM user_tables where table_name='TEST_INMEMORY';

TABLE_NAME INMEMORY_COMPRESS INMEMORY INMEMORY_DISTRI
----------------- -------- ---------------
TEST_INMEMORY

SQL> drop table TEST_INMEMORY purge;

Table dropped.


#TRANSFORM=INMEMORY_CLAUSE:string Modify the properties of the IM export object about IM

-bash-4.1$ impdp sh/sh@racnode2/pdb1 directory=PDB1_DIR 
TRANSFORM=INMEMORY_CLAUSE:\"INMEMORY MEMCOMPRESS FOR DML\"

Import: Release 12.1.0.2.0 - Production on Thu Sep 5 16:14:01 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SH"."SYS_IMPORT_FULL_01": sh/********@racnode2/pdb1 directory=PDB1_DIR TRANSFORM=INMEMORY_CLAUSE:"INMEMORY MEMCOMPRESS FOR DML"
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."TEST_INMEMORY" 3.968 MB 200000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SH"."SYS_IMPORT_FULL_01" successfully completed at Thu Sep 5 16:14:19 2019 elapsed 0 00:00:17

-bash-4.1$ sqlplus sh/sh@racnode2/pdb1

SQL> SELECT table_name, inmemory_compression, inmemory_priority , 
inmemory_distribute FROM user_tables where table_name='TEST_INMEMORY';

TABLE_NAME INMEMORY_COMPRESS INMEMORY INMEMORY_DISTRI
----------------- --------------------------------- ---------------
TEST_INMEMORY FOR DML NONE AUTO

12.2 New Feature
“In-Memory FastStart (IM FastStart) reduces the time to populate data into 
the IM column store when
a database instance restarts.”

=====================
Path to Documentation:
=====================
Database Administrator’s Guide
6 Managing Memory
6.7 Using the In-Memory Column Store

Thank you for visiting this blog 🙂