Oracle | In-Memory Database

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

TBD

ILM Policy Clause 2 CREATE TABLE [schema_name.]table_name( <column_specification> TABLE COMPRESSION <GROUP | ROW | SEGMENT> ON <function_name>;

TBD

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;