Database In-Memory (IM) FastStart
APPLIES TO:
Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.
GOAL
During database startup, enable data to be repopulated into the IM Column Store at a much faster rate then previously possible.
SOLUTION
Prerequisites
- Empty ASSM tablespace: recommend creating 2x size of inmemory_size (if maxsize is hit, then it will automatically age out the oldest segment).
Documentation
Database In-Memory Guide: Managing IM FastStart for the IM Column Store
Example
1. Create the tablespace (must be ASSM)
2. Designate the new tablespace as the faststart area
3. Check that the lob exists (note it is nologging).
COL l_owner FORMAT a10
COL l_seg FORMAT a20
COL l_MB FORMAT 999999
SELECT l.OWNER l_owner, l.SEGMENT_NAME l_seg,
SUM(s.BYTES)/1024/1024 l_MB
FROM DBA_LOBS l, DBA_SEGMENTS s
WHERE l.SEGMENT_NAME = s.SEGMENT_NAME
AND l.TABLESPACE_NAME = ‘IM_FAST_START’
GROUP BY l.OWNER, l.SEGMENT_NAME;
L_OWNER L_SEG L_MB
———- ——————– ——-
SYS SYSDBIMFS_LOBSEG$ 0
4. Force IM column store to re-populate any currently populated objects.
select /* full(i2) */ count(*) from imtest2 i2;
5. Check that objects are in the IM column store.
col owner for a10
col segment_name for a25
col MB for 9999999.99
select owner, segment_name, bytes/1048576 MB from v$im_segments;
OWNER SEGMENT_NAME MB
———- ————————- ———–
SGSTEST IMTEST2 22.20
SGSTEST IMTEST 22.08
6. Now see if the fast start area has been populated.
COL l_owner FORMAT a10
COL l_seg FORMAT a20
COL l_MB FORMAT 999999
SELECT l.OWNER l_owner, l.SEGMENT_NAME l_seg,
SUM(s.BYTES)/1024/1024 l_MB
FROM DBA_LOBS l, DBA_SEGMENTS s
WHERE l.SEGMENT_NAME = s.SEGMENT_NAME
AND l.TABLESPACE_NAME = ‘IM_FAST_START’
GROUP BY l.OWNER, l.SEGMENT_NAME;
L_OWNER L_SEG L_MB
———- ——————– ——-
SYS SYSDBIMFS_LOBSEG$ 25
7. If not populated, checkpoint the IM FastStart.
8. Check the last time the IM FastStart was populated and checkpointed.
9. Disable IM FastStart