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

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.