ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
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>>
