Oracle | DBMS_INMEMORY

Oracle DBMS_INMEMORY_ADVISE Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Provide guidance on whether the database will benefit from In Memory by analyzing the workload and determining the percentage of analytics.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
conf_analytic_threshold_pct	NUMBER	20
conf_ash_analytic_count_wght	NUMBER	47
conf_awr_analytic_dbtime_wght	NUMBER	47
conf_awr_analytic_opertn_wght	NUMBER	6
conf_max_accptd_idle_time_pct	NUMBER	5

Data Types TYPE inmemory_adv_object IS RECORD ( owner VARCHAR2(ora_max_name_len, object_name VARCHAR2(ora_max_name_len, subobject_name VARCHAR2(ora_max_name_len); TYPE inmemory_adv_obj_tab IS TABLE OF inmemory_adv_object; TYPE inmemory_advisor_recommendation IS RECORD( inmemory_size NUMBER, db_time_baseline NUMBER, db_time_baseline_analytics NUMBER, db_time_high NUMBER, db_time_low NUMBER, db_time_analytics_high NUMBER, db_time_analytics_low NUMBER recommended_obj_list inmemory_adv_obj_tab);

Dependencies

DBMS_INMEMORY_LIB	IM_ADVISOR_REC_OBJECTS$	PLITBLM
DBMS_STANDARD	IM_ADVISOR_RESULTS$	PRVT_DBMS_INMEMORY_ADVISOR
IM_ADVISOR_COMPUTATION$	IM_ADVISOR_TASKS$

Documented Yes: PL/SQL Packages and Types Reference

Exceptions

Error Code	Reason
ORA-20000	invalid_argument

First Available 23ai

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to the DBA role

Source {ORACLE_HOME}/rdbms/admin/dbmsinmemadvisor,sql
{ORACLE_HOME}/rdbms/admin/prvtinmemadvisor.plb

Subprograms

DROP_OLD_TASKS	IS_INMEMORY_ELIGIBLE	START_TRACKING
DROP_TASK	LIST_ADVISE	STOP_TRACKING
GENERATE_ADVISE

DROP_OLD_TASK (new 23ai)

Deletes all the completed tasks which were created and ended the midnight prior to the Nth day dbms_inmemory_advise.drop_old_task(days IN NUMBER);

exec dbms_inmemory_advise.drop_old_task(7);

      

      PL/SQL procedure successfully completed.

DROP_TASK (new 23ai)

Drops the IM Advisor task details for the given task id dbms_inmemory_advise.drop_task(task_id IN NUMBER);

TBD

GENERATE_ADVISE (new 23ai)

Generate Advisor advice dbms_inmemory_advise.generate_advise;

exec dbms_inmemory_advise.generate_advise;

      

      PL/SQL procedure successfully completed.

IS_INMEMORY_ELIGIBLE (new 23ai)

Analyzes snapshot ranges

Overload 1 dbms_inmemory_advise.is_inmemory_eligible( start_snap_id IN NUMBER, end_snap_id IN NUMBER, inmem_eligible OUT BOOLEAN, analysis_summary OUT VARCHAR2, snap_dbid IN NUMBER DEFAULT NULL);

TBD

Analyzes time window ranges

Overload 2 dbms_inmemory_advise.is_inmemory_eligible( start_time IN TIMESTAMP, end_time IN TIMESTAMP, inmem_eligible OUT BOOLEAN, analysis_summary OUT VARCHAR2, snap_dbid IN NUMBER DEFAULT NULL);

TBD

Analyzes number of days

Overload 3 dbms_inmemory_advise.is_inmemory_eligible( time_window_days IN NUMBER, inmem_eligible OUT BOOLEAN, analysis_summary OUT VARCHAR2, snap_dbid IN NUMBER DEFAULT NULL);

TBD

LIST_ADVISE (new 23ai)

List Advisor advise dbms_inmemory_advise.list_advise( task_id IN NUMBER, inmemory_size IN NUMBER) RETURN inmemory_advisor_recommendation;

TBD

START_TRACKING (new 23ai)

Start utilization tracing dbms_inmemory_advise.start_tracking;

exec dbms_inmemory_advise.start_tracking;

      

      PL/SQL procedure successfully completed.

STOP_TRACKING (new 23ai)

Stop utilization tracing dbms_inmemory_advise.stop_tracking;

exec dbms_inmemory_advise.stop_tracking;

      

      PL/SQL procedure successfully completed.