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 is now availble. If you haven't you downloaded already: Why?

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.