Oracle DBMS_INMEMORY_ADMIN
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
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
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