Oracle | DBMS_INMEMORY

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	FASTSTART_ENABLE	IME_DROP_ALL_EXPRESSIONS
AIM_SET_PARAMETER	FASTSTART_MIGRATE_STORAGE	IME_GET_CAPTURE_STATE
DEALLOCATE_VERSIONS	GET_FASTSTART_TABLESPACE	IME_OPEN_CAPTURE_WINDOW
FASTSTART_CHECKPOINT	IME_CAPTURE_EXPRESSIONS	IME_POPULATE_EXPRESSIONS
FASTSTART_DISABLE	IME_CLOSE_CAPTURE_WINDOW	POPULATE_WAIT

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