Oracle DBMS_INMEMORY_ADMIN
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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_LIB IM_IME$
DBA_IM_EXPRESSIONS DBMS_STANDARD PRVT_DBMS_INMEMORY_ADMIN
DBMS_INMEMORY    
Documented Yes: Packages and Types Reference
First Available 12.2
Security Model Owned by SYS with EXECUTE granted to the DBA role
Source {ORACLE_HOME}/rdbms/admin/dbmsinmemadmin.sql
Subprograms
 
AIM_GET_PARAMETER
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
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;

GET_FASTSTART_TABLESPACE
-------------------------
NOT ENABLED
 
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');

PL/SQL procedure successfully completed.
 
IME_CLOSE_CAPTURE_WINDOW
Closes an expression monitoring window dbms_inmemory_admin.ime_close_capture_window;
exec dbms_inmemory_admin.ime_close_capture_window;

PL/SQL procedure successfully completed.
 
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;

PL/SQL procedure successfully completed.
 
IME_GET_CAPTURE_STATE
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
Opens an expression monitoring window dbms_inmemory_admin.ime_open_capture_window;
exec dbms_inmemory_admin.ime_open_capture_window;

PL/SQL procedure successfully completed.
 
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;

PL/SQL procedure successfully completed.
 
POPULATE_WAIT
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;
DECLARE
 retVal NUMBER;
BEGIN
  retVal := dbms_inmemory_admin.populate_wait(force=>TRUE);
  dbms_output.put_line(TO_CHAR(retVal));
END;
/
POPULATE ERROR, INMEMORY_SIZE=0
3

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_INMEMORY
DBMS_MEMOPTIMIZE
In Memory Database
What's New In 21c
What's New In 23c

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