Oracle DBMS_INMEMORY_ADMIN
Version 19.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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
Constants
Name Data Type Value
AIM Constants
AIM_SERIALIZATION NUMBER 0
AIM_STATWINDOW_DAYS NUMBER 1
AIM_STATWINDOW_DAYS_DEFAULT NUMBER 2
Populate Wait Return Codes
POPULATE_TIMEOUT NUMBER -1
POPULATE_SUCCESS NUMBER 0
POPULATE_OUT_OF_MEMORY NUMBER 1
POPULATE_NO_INMEMORY_OBJECTS NUMBER 2
POPULATE_INMEMORY_SIZE_ZERO NUMBER 3
Dependencies
ADO_IMPARAM$ DBMS_INMEMORY DBMS_STANDARD
DBA_IM_EXPRESSIONS DBMS_INMEMORY_LIB IM_IME$
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
 
AIM_GET_PARAMETER (new 19c)
Returns the current value of an AIM parameter dbms_inmemory_admin.aim_get_parameter(
parameter IN  NUMBER,
value     OUT NUMBER);
DECLARE
 outVal NUMBER;
BEGIN
  dbms_inmemory_admin.aim_get_parameter(dbms_inmemory_admin.aim_statwindow_days, outVal);
  dbms_output.put_line(TO_CHAR(outVal));
END;
/
 
AIM_SET_PARAMETER (new 19c)
Customizes the AIM execution environment dbms_inmemory_admin.aim_set_parameter(
parameter IN NUMBER,
value     IN NUMBER);
exec dbms_inmemory_admin.aim_set_parameter(dbms_inmemory_admin.aim_statwindow_days, 30);
 
DEALLOCATE_VERSIONS
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
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
Disables the faststart mechanism dbms_inmemory_admin.fasttart_disable;
See FASTSTART_ENABLE demo below
 
FASTSTART_ENABLE
Enables IM FastStart and assigns a tablespace 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

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
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
Returns the name of the tablespace that is currently designated for IM FastStart dbms_inmemory_admin.get_faststart_tablespace RETURN VARCHAR2;

-- 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
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_CLOSE_CAPTURE_WINDOW (new 19c)
Closes an expression monitoring window dbms_inmemory_admin.ime_close_capture_window;
exec dbms_inmemory_admin.ime_close_capture_window;
 
IME_DROP_ALL_EXPRESSIONS
Drops 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_GET_CAPTURE_STATE (new 19c)
Returns the current state of the expression monitoring window dbms_inmemory_admin.ime_get_capture_state(
p_capture_state OUT VARCHAR2,
p_last_modified OUT TIMESTAMP);
DECLARE
 stateVal VARCHAR2(60);
 lastMod  TIMESTAMP;
BEGIN
  dbms_inmemory_admin.ime_get_capture_state(stateVal, lastMod);
  dbms_output.put_line(stateVal);
  dbms_output.put_line(lastMod);
END;
/
 
IME_OPEN_CAPTURE_WINDOW (new 19c)
Opens an expression monitoring window dbms_inmemory_admin.ime_open_capture_window;
exec dbms_inmemory_admin.ime_open_capture_window;
 
IME_POPULATE_EXPRESSIONS
Populates all hot expressions that were captured in the latest iteration, into the IM column store dbms_inmemory_admin.ime_populate_expressions;
exec dbms_inmemory_admin.ime_populate_expressions;
 
POPULATE_WAIT (new 19c)
Wrapper around populate() that waits for population to finish for all inmemory objects with priority greater than or equal to the priority specified (default LOW) dbms_inmemory_admin.populate_wait(
priority   IN VARCHAR2 DEFAULT 'LOW',
percentage IN NUMBER   DEFAULT 100,
timeout    IN NUMBER   DEFAULT 9999999,
force      IN BOOLEAN  DEFAULT FALSE)
RETURN NUMBER;
SELECT dbms_inmemory_admin.populate_wait(force=>TRUE)
FROM dual;

Related Topics
DBMS_INMEMORY
DBMS_MEMOPTIMIZE
In Memory Database
Packages
What's New In 18cR3
What's New In 19cR2

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-2019 Daniel A. Morgan All Rights Reserved