Oracle DBMS_INMEMORY_ADMIN
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
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
DBMS_INMEMORY
DBMS_MEMOPTIMIZE
In Memory Database
Packages
What's New In 19c
What's New In 20c-21c

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