Oracle | PRVT_DBMS_INMEMORY

Oracle PRVT_DBMS_INMEMORY_ADVISOR 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 Internal support for the In-Memory Advisor

AUTHID DEFINER

Dependencies

AWR_PDB_ACTIVE_SESS_HISTORY	DBMS_OUTPUT
AWR_PDB_PARAMETER	DBMS_STANDARD
AWR_PDB_PDB_IN_SNAP	DBMS_WORKLOAD_REPOSITORY
AWR_PDB_SQLSTAT	DIUTIL
AWR_PDB_SQL_PLAN	HEAT_MAP_STAT$
AWR_PDB_SYS_TIME_MODEL	IM_ADVISOR_COMPUTATION$
DBA_HIST_SNAPSHOT	IM_ADVISOR_REC_OBJECTS$
DBA_OBJECTS	IM_ADVISOR_RESULTS$
DBA_USERS	IM_ADVISOR_TASKS$
DBMS_ASSERT	PLITBLM
DBMS_ILM	V$DATABASE
DBMS_INMEMORY_ADVISE	V$PARAMETER

Documented No

Exceptions

Error Code	Reason
-20000	heat map is not enabled.
-20000	start_tracking procedure must be have been invoked prior to calling stop_tracking.

First Available 23ai

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

Source {ORACLE_HOME}/rdbms/admin/prvtinmemadvisor.plb

Subprograms

CONV_SNAP_TIME_TO_SNAP_IDS	SET_MIN_WALLCLOCK_TIME_MINS
CONV_WINDOW_DAYS_TO_SNAP_IDS	SET_PRINT_TO_SCREEN
GENERATE_RECOMMENDATION_SQL	SET_PRINT_TO_TRACE
GENERATE_RESULT	START_TRACKING_INTERNAL
GET_IM_ELIGIBLE_ANALYTIC_PCT	STOP_TRACKING_INTERNAL
GET_LATEST_SNAP_ID	TRACE
GET_LATEST_TASK_INFO	UPDATE_HEAT_MAP_ROW_FLAG
GET_SNAP_ID_INFO	UPDATE_TASK
INSERT_TASK	UPDATE_TASK_ERROR
IS_INMEM_ELIGIBLE_INTERNAL	VALIDATE_SNAPSHOT_IDS
IS_IN_MEM_ENABLED	VALIDATE_TASK
SET_DBID	VERIFY_HEAT_MAP

CONV_SNAP_TIME_TO_SNAP_IDS (new 23ai)

Converts AWR snap times to Snap IDs prvt_dbms_inmemory_advisor.conv_snap_time_to_snap_ids( start_time IN TIMESTAMP, end_time IN TIMESTAMP, snap_dbid IN NUMBER, start_snap_id OUT NUMBER, end_snap_id OUT NUMBER);

TBD

CONV_WINDOW_DAYS_TO_SNAP_IDS (new 23ai)

Converts AWR window days to Snap IDs prvt_dbms_inmemory_advisor.conv_window_days_to_snap_ids( time_window_days IN NUMBER, snap_dbid IN NUMBER, start_snap_id OUT NUMBER, end_snap_id OUT NUMBER);

TBD

GENERATE_RECOMMENDATION_SQL (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.generate_recommenation_sql( p_task_id IN NUMBER, inmemory_size IN NUMBER, recommended_sql OUT CLOB);

TBD

GENERATE_RESULT (new 23ai)

Generate Advisor results

Overload 1 prvt_dbms_inmemory_advisor.generate_result(p_task_id IN NUMBER);

TBD

Overload 2 prvt_dbms_inmemory_advisor.generate_result( p_task_id IN NUMBER, im_size IN NUMBER, p_imadv_rec OUT sys.dbms_inmemory_advise.inmemory_advisor_recommendation);

TBD

GET_IM_ELIGIBLE_ANALYTIC_PCT (new 23ai)

Undocumented

Overload 1 prvt_dbms_inmemory_advisor.get_inmemory_eligible_analytic_pct( start_snap_id IN NUMBER, end_snap_id IN NUMBER, analytic_percent OUT NUMBER, analytic_percent_valid OUT BOOLEAN, task_id IN NUMBER);

TBD

Overload 2 prvt_dbms_inmemory_advisor.get_inmemory_eligible_analytic_pct( analytic_percent OUT NUMBER, im_enabled_snap_pct OUT NUMBER, status_code OUT NUMBER, start_snap_id IN NUMBER, end_snap_id IN OUT NUMBER);

TBD

GET_LATEST_SNAP_ID (new 23ai)

Given the DBID outputs the most recent snap id prvt_dbms_inmemory_advisor.get_latest_snap_id( snap_dbid IN NUMBER, end_snap_id OUT NUMBER);

SELECT dbid FROM v$database;

      

             DBID

      -----------

       1472724728

      

      DECLARE

       outVal NUMBER;

      BEGIN

        prvt_dbms_inmemory_advisor.get_latest_snap_id(1472724728, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      1573

      

      PL/SQL procedure successfully completed.

GET_LATEST_TASK_INFO (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.get_latest_task_info( p_task_id OUT NUMBER, p_start_awr_snap_id OUT NUMBER, p_end_awr_snap_id OUT NUMBER);

DECLARE

       tid   NUMBER;

       sasid NUMBER;

       easid NUMBER;

      BEGIN

        prvt_dbms_inmemory_advisor.get_latest_task_info(tid, sasid, easid);

        dbms_output.put_line(tid);

        dbms_output.put_line(sasid);

        dbms_output.put_line(easid);

      END;

      /

      0

      0

      0

GET_SNAP_ID_INFO (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.get_snap_id_info( p_task_id IN NUMBER, p_start_awr_snap_id OUT NUMBER, p_end_awr_snap_id OUT NUMBER);

TBD

INSERT_TASK (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.insert_task( p_task_id IN NUMBER, p_start_snap_id IN NUMBER, p_start_time IN TIMESTAMP, p_flag IN NUMBER);

TBD

IS_INMEM_ELIGIBLE_INTERNAL (new 23ai)

Undocumented

Overload 1 prvt_dbms_inmemory_advisor.is_inmem_eligible_internal( snap_dbid IN NUMBER, start_snap_id IN NUMBER, end_snap_id IN NUMBER, max_accepted_idle_time_pct IN NUMBER, analytic_threshold_pct IN NUMBER, awr_analytic_operations_weight IN NUMBER, awr_analytic_dbtime_weight IN NUMBER, ash_analytic_count_weight IN NUMBER, inmem_eligible OUT BOOLEAN, analysis_summary OUT VARCHAR2);

TBD

IS_IN_MEM_ENABLED (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.is_in_mem_enabled( snap_dbid IN NUMBER, start_snap_id IN NUMBER, end_snap_id IN NUMBER, in_mem_enabled OUT BOOLEAN);

TBD

SET_DBID (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.set_dbid( snap_dbid IN NUMBER, dbid OUT NUMBER);

TBD

SET_MIN_WALLCLOCK_TIME_MINS (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.set_min_wallclock_time_mins( min_wallclock_time_mins IN NUMBER);

exec prvt_dbms_inmemory_advisor.set_min_wallclock_time_mins(10);

      

      PL/SQL procedure successfully completed.

SET_PRINT_TO_SCREEN (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.set_print_to_screen(val IN BOOLEAN);

exec prvt_dbms_inmemory_advisor.set_print_to_screen(TRUE);

      

      PL/SQL procedure successfully completed.

SET_PRINT_TO_TRACE (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.set_print_to_trace(val IN BOOLEAN);

exec prvt_dbms_inmemory_advisor.set_print_to_trace(TRUE);

      

      PL/SQL procedure successfully completed.

START_TRACKING_INTERNAL (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.start_tracking_internal(task_id OUT NUMBER);

DECLARE

       outVal NUMBER;

      BEGIN

        prvt_dbms_inmemory_advisor.start_tracking_internal(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      *

      ORA-20000: Heat map is not enabled.

STOP_TRACKING_INTERNAL (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.stop_tracing_internal;

exec prvt_dbms_inmemory_advisor.stop_tracing_internal;

      *

      ORA-20000: start_tracking procedure must be have been invoked prior to calling stop_tracking.

TRACE (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.trace(trace_info IN VARCHAR2);

exec prvt_dbms_inmemory_advisor.trace('ZZYZX');

      

      PL/SQL procedure successfully completed.

UPDATE_HEAT_MAP_ROW_FLAG (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.update_heat_map_row_flag(p_flag IN NUMBER);

TBD

UPDATE_TASK (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.update_task( p_task_id IN NUMBER, p_end_snap_id IN NUMBER, p_end_time IN TIMESTAMP, p_flag IN NUMBER);

TBD

UPDATE_TASK_ERROR (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.update_task_error( p_task_id IN NUMBER, p_error IN NUMBER);

TBD

VALIDATE_SNAPSHOT_IDS (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.validate_snapshot_ids( snap_dbid IN NUMBER, start_snap_id IN NUMBER, end_snap_id IN NUMBER, task_id IN NUMBER);

TBD

VALIDATE_TASK (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.validate_task(p_task_id IN NUMBER) RETURN NUMBER;

TBD

VERIFY_HEAT_MAP (new 23ai)

Undocumented prvt_dbms_inmemory_advisor.verify_heat_map(task_id IN NUMBER);

exec prvt_dbms_inmemory_advisor.verify_heat_map;

           *

      ORA-20000: Heat map is not enabled.

      

      exec prvt_dbms_inmemory_advisor.verify_heat_map(1573);

           *

      ORA-20000: Heat map is not enabled.