Oracle In-Memory Database
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose The licensable "In-Memory Database" option allows a portion of the SGA to be set aside to support an in-memory columnar store and in-memory database caching.
Dependencies
ADO_IMCSEQ$ DBMS_FEATURE_IM_JOINGROUPS V$IM_GLOBALDICT
ADO_IMSEGSTAT$ GV$INMEMORY_AREA V$IM_GLOBALDICT_PIECEMAP
ADO_IMSEGTASKDETAILS$ GV$INMEMORY_FASTSTART_AREA V$IM_GLOBALDICT_SORTORDER
ADO_IMSTAT$ GV$INMEMORY_XMEM_AREA V$IM_GLOBALDICT_VERSION
ADO_IMTASKS$ IMORDERBY$ TABLE V$IM_HEADER
AWR_CDB_IM_SEG_STAT IMSVC$ V_$IM_IMECOL_CU
AWR_CDB_IM_SEG_STAT_OBJ IMSVCTS$ V$IM_SEGMENTS
AWR_PDB_IM_SEG_STAT IM_DOMAIN$ V$IM_SEGMENTS_DETAIL
AWR_PDB_IM_SEG_STAT_OBJ IM_DOMAINSEQ$ V$IM_SEG_EXT_MAP
AWR_ROOT_IM_SEG_STAT IM_IME$ TABLE V$IM_SMU_CHUNK
AWR_ROOT_IM_SEG_STAT_OBJ IM_JOINGROUP$ V$IM_SMU_DELTA
CDB_HIST_IM_SEG_STAT KU$_IM_COLSEL_LIST_T V$IM_SMU_HEAD
CDB_HIST_IM_SEG_STAT_OBJ KU$_IM_COLSEL_T V$IM_TBS_EXT_MAP
CDB_INMEMORY_AIMTASKDETAILS KU$_IM_COLSEL_VIEW V$IM_USER_SEGMENTS
CDB_INMEMORY_AIMTASKS V_$IMHMSEG V$INMEMORY_AREA
DBA_HIST_IM_SEG_STAT V_$IM_ADOELEMENTS V$INMEMORY_FASTSTART_AREA
DBA_HIST_IM_SEG_STAT_OBJ V_$IM_ADOTASKDETAILS V$INMEMORY_XMEM_AREA
DBA_INMEMORY_AIMTASKDETAILS V_$IM_ADOTASKS V$PARAMETER
DBA_INMEMORY_AIMTASKS V$IM_COLUMN_LEVEL V$SGA
DBMS_FEATURE_IM_ADO V$IM_COL_CU _INMEMORY_AIMTASKDETAILS
DBMS_FEATURE_IM_EXPRESSIONS V$IM_DELTA_HEADER _INMEMORY_AIMTASKS
DBMS_FEATURE_IM_FORSERVICE    
Initialization Parameters  
Initialization Parameters In-Memory Database is not enabled if the INMEMORY_SIZE is set to zero (0) as shown in listing below.

Add space to the SGA_TARGET parameter to accommodate the requirements of both the existing SGA and the In-Memory Area. the In-Memory area is separate from the Buffer Cache and other SGA caches.

Use INMEMORY_VIRTUAL_COLUMNS=ENABLE to put virtual columns into the In-Memory area.

Also check into the MEMOPTIMIZE_POOL_SIZE initalization parameter and how the Memoptimize Pool works.
SQL> show parameter inmemory

NAME                                         TYPE         VALUE
-------------------------------------------- ------------ ----------
inmemory_adg_enabled                         boolean      TRUE
inmemory_automatic_level                     string       OFF
inmemory_clause_default                      string
inmemory_deep_vectorization                  boolean      TRUE        <-- new 21c parm
inmemory_expressions_usage                   string       ENABLE
inmemory_force                               string       DEFAULT
inmemory_max_populate_servers                integer      0
inmemory_optimized_arithmetic                string       DISABLE
inmemory_prefer_xmem_memcompress             string
inmemory_prefer_xmem_priority                string
inmemory_query                               string       ENABLE
inmemory_size                                big integer  0
inmemory_trickle_repopulate_servers_percent  integer      1
inmemory_virtual_columns                     string       MANUAL
inmemory_xmem_size                           big integer  0
optimizer_inmemory_aware                     boolean      TRUE
ILM Policy Clause 1 CREATE TABLE [schema_name.]table_name(
<column_specification>
TABLE COMPRESSION <GROUP | ROW | SEGMENT>
AFTER <integer> <DAY | DAYS | MONTH | MONTHS | YEAR | YEARS>
OF <CREATION | LOW ACCESS | NO ACCESS | NO MODIFICATION>;
TBD
ILM Policy Clause 2 CREATE TABLE [schema_name.]table_name(
<column_specification>
TABLE COMPRESSION <GROUP | ROW | SEGMENT>
ON <function_name>;
TBD
ILM Policy Clause 3 CREATE TABLE [schema_name.]table_name(
<column_specification>
<tiering clause> <GROUP | ROW | SEGMENT>
[AFTER <integer> <DAY | DAYS | MONTH | MONTHS | YEAR | YEARS>
OF <CREATION | LOW ACCESS | NO ACCESS | NO MODIFICATION]
TBD
ILM Policy Clause 4 CREATE TABLE [schema_name.]table_name(
<column_specification>
<tiering clause> <GROUP | ROW | SEGMENT>
ON <function_name>;
TBD
Tiering CREATE TABLE [schema_name.]table_name(
<column_specification>
TIER TO <tablespace_name>  [READ ONLY];
TBD
 
Enable In-Memory Column Store
Create Column Store at root level ALTER SYSTEM SET inmemory_size = <integer> <M | G | T> SCOPE = spfile;
SQL> ALTER SYSTEM SET inmemory_size = 500M SCOPE = spfile;

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

Total System Global Area 2097152000 bytes
Fixed Size 3047568 bytes
Variable Size 1140854640 bytes
Database Buffers 402653184 bytes
Redo Buffers 13725696 bytes
In-Memory Area 536870912 bytes
Database mounted.
Database opened.
Set the maximum value of the column store at PDB level ALTER SYSTEM SET inmemory_size=<integer> <M | G | T>
SQL> ALTER SESSION SET CONTAINER = pdbdev;
SQL> ALTER PLUGGABLE DATABASE pdbdev OPEN;
SQL> ALTER SYSTEM SET inmemory_size=400M;

SQL> col value format 9999999999
SQL> SELECT * FROM v$sga;

NAME             VALUE       CON_ID
---------------- ----------- ----------
Fixed Size           3047568 0
Variable Size     1086328688 0
Database Buffers   469762048 0
Redo Buffers        13725696 0
In-Memory Area     536870912 0
Specify a segment for in-memory population ALTER TABLE <table_name> INMEMORY [PRIORITY <CRITICAL | HIGH | MEDIUM | LOW | NONE>];
conn uwclass/uwclass@pdbdev

ALTER TABLE airplanes INMEMORY PRIORITY high;

SELECT table_name,  inmemory_priority, inmemory_distribute, inmemory_compression
FROM user_tables
WHERE table_name = 'AIRPLANES'
ORDER BY 1;

CREATE TABLE airplanes1 AS SELECT * FROM airplanes;
INSERT INTO airplanes SELECT * FROM airplanes;
INSERT INTO airplanes SELECT * FROM airplanes;
INSERT INTO airplanes SELECT * FROM airplanes;
INSERT INTO airplanes SELECT * FROM airplanes;
INSERT INTO airplanes SELECT * FROM airplanes;
INSERT INTO airplanes SELECT * FROM airplanes;
INSERT INTO airplanes SELECT * FROM airplanes;
COMMIT;

ALTER TABLE airplanes1 INMEMORY PRIORITY low;

-- execute this SQL statement very quickly and immediately repeat with slashes to monitor progress
SELECT segment_name, populate_status, bytes_not_populated
FROM v$im_segments;

/

/

-- if servers were a partitioned table
SELECT partition_name, inmemory_priority, inmemory_distribute, inmemory_compression
FROM user_tab_partitions
WHERE table_name = 'SERVERS'
ORDER BY partition_position;
Specify a virtual column be placed inmemory ALTER TABLE <table_name> INMEMORY (<column_name>);
conn uwclass/uwclass@pdbdev

ALTER TABLE servers INMEMORY (vcolumn);

-- you must enable the init parameter INMEMORY_VIRTUAL_COLUMNS for this to work
 
Enable In-Memory Compression
As of 12.2 InMemory data can be compressed

Compression ratios are typically 2-20X but can achieve >50X depending upon data		 CREATE TABLE <schema_name.table_name>(
<column_definitions>)
<NO MEMCOMPRESS | MEMCOMPRESS FOR DML | MEMCOMPRESS FOR QUERY <LOW | HIGH> | MEMCOMPRESS FOR CAPACITY <LOW | HIGH>>
CREATE TABLE inmemcomp1(
empno  NUMBER,
ename  VARCHAR2(30),
deptno NUMBER(2))
INMEMORY MEMCOMPRESS FOR QUERY HIGH;

CREATE TABLE inmemcomp2(
empno  NUMBER,
ename  VARCHAR2(30),
deptno NUMBER(2))
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

Related Topics
Built-in Functions
Built-in Packages
DBMS_INMEMORY
DBMS_INMEMORY_ADMIN
DBMS_MEMOPTIMIZE
Full Database Caching
Processes
Startup Parameters
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx