Oracle DBMS_INMEMORY_ADMIN
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Provides interfaces for managing In-Memory Expressions (IM expressions) and the In-Memory FastStart (IM FastStart) area.

Analytic queries often contain complex expressions or calculations that can consume significant CPU and memory during execution. Use DBMS_INMEMORY_ADMIN procedure to identify these frequently used (“hot”) expressions and populate them in the IM column store. In this way, the database avoids repeated computations and improves performance.
AUTHID CURRENT_USER
Dependencies
ALL_IM_EXPRESSIONS DBMS_INMEMORY USER_IM_EXPRESSIONS
DBA_IM_EXPRESSIONS DBMS_INMEMORY_LIB  
Documented Yes
First Available 12.2.0.1
Security Model Owned by SYS with EXECUTE granted to the DBA role
Source {ORACLE_HOME}/rdbms/admin/dbmsinmemadmin.sql
Subprograms
 
DEALLOCATE_VERSIONS (new 12.2)
Disables the In-Memory FastStart (IM FastStart) feature dbms_inmemory_admin.deallocate_version(spcpressure IN BOOLEAN DEFAULT FALSE);
exec dbms_inmemory_admin.deallocate_version(TRUE);
 
FASTSTART_CHECKPOINT (new 12.2)
Checkpoint all deferred write pending tasks immediately dbms_inmemory_admin.faststart_checkpoint(global IN BOOLEAN DEFAULT TRUE);
exec dbms_inmemory_admin.faststart_checkpoint(TRUE);
 
FASTSTART_DISABLE (new 12.2)
Disables the faststart mechanism. Relocated from DBMS_INMEMORY in 12.1. dbms_inmemory_admin.fasttart_disable;
See FASTSTART_ENABLE demo below
 
FASTSTART_ENABLE (new 12.2)
Enables IM FastStart and assigns a tablespace. Relocated from DBMS_INMEMORY in 12.1. dbms_inmemory_admin.faststart_enable(
tbs_name  IN VARCHAR2,
nologging IN BOOLEAN DEFAULT TRUE);
CREATE TABLESPACE fs_tbs DATAFILE 'fs_tbs.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

exec dbms_inmemory_admin.faststart_enable('fs_tbs');

The following query shows that the IM FastStart LOB was created (sample output included):
col owner format a5
col segment_name format a20 a20

SELECT l.owner, l.segment_name, SUM(s.bytes)/1024/1024 MB
FROM dbs_lobs l, dba_segments s
WHERE l.segment_name = s.segment_name
AND l.tablespace_name = 'FS_TBS'
GROUP BY l.owner, l.segment_name;

OWNER  SEGMENT_NAME                 MB
------ -------------------- ----------
SYS    SYSDBIMFS_LOBSEG$          .125

drop tablespace fs_tbs including contents and datafiles;
drop tablespace fs_tbs including contents and datafiles
*
ERROR at line 1:
ORA-64379: Action cannot be performed on the tablespace assigned to FastStart while the feature is enabled


exec dbms_inmemory_admin.faststart_disable;

drop tablespace fs_tbs including contents and datafiles;
 
FASTSTART_MIGRATE_STORAGE (new 12.2)
Moves all IM FastStart data and metadata from the existing tablespace to the specified new tablespace. dbms_inmemory_admin.faststart_migrate_storage(tbs_name IN VARCHAR2);
exec dbms_inmemory_admin.faststart_migrate_storage('UWFS');
 
GET_FASTSTART_TABLESPACE (new 12.2)
Returns the name of the tablespace that is currently designated for IM FastStart. dbms_inmemory_admin.get_faststart_tablespace RETURN VARCHAR;

-- seriously ... this is not an error ... Oracle actually used VARCHAR rather than VARCHAR2 ... we are not impressed.
SELECT dbms_inmemory_admin.get_faststart_tablespace
FROM dual;
 
IME_CAPTURE_EXPRESSIONS (new 12.2)
Captures and populates the 20 most frequently accessed (“hottest”) expressions in the database in the specified time range. dbms_inmemory_admin.ime_capture_expressions(snapshot IN VARCHAR2);

Valid values are CUMULATIVE and CURRENT (most recent 24 hours)
exec dbms_inmemory_admin.ime_capture_expressions('CURRENT');
 
IME_DROP_ALL_EXPRESSIONS (new 12.2)
Allows a DBA to drop all SYS_IME hidden VCs across all tables in the database whether they are marked for in-memory or not. dbms_inmemory_admin.ime_drop_all_expressions;
exec dbms_inmemory_admin.ime_drop_all_expressions;
 
IME_POPULATE_EXPRESSIONS (new 12.2)
Allows a DBA to populate all hot expressions that were captured in the latest iteration, into the IM column store. Without a call to this procedure, the expressions will be gradually populated as and when the IMCUs for the involved segments are repopulated. dbms_inmemory_admin.ime_populate_expressions;
exec dbms_inmemory_admin.ime_populate_expressions;

Related Topics
DBMS_INMEMORY
DBMS_MEMOPTIMIZE
In Memory Database
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved