Skip to content

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)

create tablespace im_fast_start datafile ‘/ade/b/3323145358/oracle/dbs/cdb1_pdb1_imfaststart.f’ size 200M autoextend on maxsize 1G segment space management auto;

2. Designate the new tablespace as the faststart area

EXEC DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE(‘im_fast_start’);

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(i) */ count(*) from imtest i;
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.

exec dbms_inmemory_admin.faststart_checkpoint;

8. Check the last time the IM FastStart was populated and checkpointed.

select * from v$inmemory_faststart_area;

9. Disable IM FastStart

exec DBMS_INMEMORY_ADMIN.FASTSTART_DISABLE();