Oracle In-Memory Database
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers? Software Defined Servers can improve performance and security while lowering complexity and cost. With Software Defined Servers you define servers with an optimum configuration to your environment rather than being memory starved and cpu over-provisioned. Find out how you can improve your computing environment with TidalScale. your friends.
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
CDB_INMEMORY_AIMTASKDETAILS V$IM_COL_CU V$IM_USER_SEGMENTS
CDB_INMEMORY_AIMTASKS V$IM_HEADER V$INMEMORY_AREA
DBA_INMEMORY_AIMTASKDETAILS V$IM_SEGMENTS V$INMEMORY_FASTSTART_AREA
DBA_INMEMORY_AIMTASKS V$IM_SEGMENTS_DETAIL V$INMEMORY_XMEM_AREA
GV$INMEMORY_AREA V$IM_SEG_EXT_MAP V$PARAMETER
GV$INMEMORY_FASTSTART_AREA V$IM_SMU_CHUNK V$SGA
GV$INMEMORY_XMEM_AREA V$IM_SMU_HEAD _INMEMORY_AIMTASKDETAILS
V$IM_COLUMN_LEVEL V$IM_TBS_EXT_MAP _INMEMORY_AIMTASKS
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