Question: How does
the inmemory parameter work in Oracle? How does
the inmemory column store work as an alternative to the KEEP pool?
How does the new Oracle 12c inmemory option work?
Answer: Oracle has long provided the
ability to fully cache your database, either by allocating
enough RAM to the data buffers to fully cache the database
or by employing the KEEP pool. Now in release 12c release
1, we see the ability for many new ways to cache data blocks
from the physical data files on disk. This introduction of
column-level caching is a direct result in the falling costs
of RAM storage.
See this
important note on the
inmemory_query parameter for
setting the inmemory system-wide and
these notes in setting
inmemory at the table level.
There are several new areas of
inmemory options in Oracle 12cr1 and Oracle now has
this bewildering suite of tools for data block caching:
Once
this extra-cost option is enabled, the Oracle in-memory region is displayed as part of
the startup command and show SGA commands:
Total System Global Area nnn bytes
Fixed Size
nnn bytes
Variable Size
nnn bytes
Database Buffers
nnn bytes
Redo Buffers
nnn bytes
In-Memory Area
nnn bytes
There is also a set of inmemory parameters that
are used to enable the inmemory option:
SQL> show parameter inmemory
NAME
TYPE
VALUE
------------------------------------
--------------------------------- -----
inmemory_clause_default
string
inmemory_force
string
DEFAULT
inmemory_max_populate_servers
integer
1
inmemory_query
string
ENABLE
inmemory_size
big integer
256G
inmemory_trickle_repopulate_servers_ integer
1
percent
optimizer_inmemory_aware
boolean
TRUE
There is also a v$im_segments
view to display details on the inmemory options.
There are also new columns within the dba_tables view for
inmemory options:
select
table_name,
cache,
inmemory_compression,
inmemory_priority,
inmemory_distribute
from
dba_tables;
There are
also new views in the 12cr1 inmemory option:
select
view_name
from
dba_views
where
view_name like 'V_$IM%';
VIEW_NAME
----------------------------------------
V_$IM_SEGMENTS_DETAIL
V_$IM_SEGMENTS
V_$IM_USER_SEGMENTS
V_$IM_TBS_EXT_MAP
V_$IM_SEG_EXT_MAP
V_$IM_HEADER
V_$IM_COL_CU
V_$IM_SMU_HEAD
V_$IM_SMU_CHUNK
V_$IM_COLUMN_LEVEL
