Oracle In-Memory Database
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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
V$IM_COLUMN_LEVEL V$IM_SEGMENTS_DETAIL V$IM_TBS_EXT_MAP
V$IM_COL_CU V$IM_SEG_EXT_MAP V$IM_USER_SEGMENTS
V$IM_HEADER V$IM_SMU_CHUNK V$PARAMETER
V$IM_SEGMENTS V$IM_SMU_HEAD V$SGA
Initialization Parameters In-Memory Database is not enabled if the INMEMORY_SIZE is set to zero (0).

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 InMemory area.

Also check into the MEMOPTIMIZE_POOL_SIZE initalization parameter.
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
New in 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 12cR2
What's New In 18cR3

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-2017 Daniel A. Morgan All Rights Reserved