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
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>>