Oracle | DBMS_SPM

Oracle DBMS_SPM_INTERNAL 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 SQL Plan Management Internal Support

AUTHID DEFINER

Data Types TYPE name_list IS TABLE OF VARCHAR2(30); TYPE sql_plan_type_table ...;

Dependencies

CDB_HIST_SNAPSHOT	DBMS_LOB	DUAL
DBA_ADVISOR_ACTIONS	DBMS_OUTPUT	PLITBLM
DBA_ADVISOR_EXECUTIONS	DBMS_SMB	PRVT_ADVISOR
DBA_ADVISOR_EXEC_PARAMETERS	DBMS_SMB_INTERNAL	PRVT_REPORT_TAGS
DBA_ADVISOR_FINDINGS	DBMS_SPM	SMB$CONFIG
DBA_ADVISOR_OBJECTS	DBMS_SPM_LIB	SQL$TEXT
DBA_ADVISOR_RECOMMENDATIONS	DBMS_SQLTUNE	SQLOBJ$
DBA_ADVISOR_SQLSTATS	DBMS_SQLTUNE_INTERNAL	SQLOBJ$AUXDATA
DBA_ADVISOR_TASKS	DBMS_SQLTUNE_UTIL1	SQLOBJ$PLAN
DBA_AUTO_INDEX_EXECUTIONS	DBMS_SQLTUNE_UTIL2	SQLSET_ROW
DBMS_SQLSET_PLANS	DBMS_STANDARD	SQL_PLAN_TABLE_TYPE
DBA_SQL_PLAN_BASELINES	DBMS_STATS_INTERNAL	SYS_IXMLAGG
DBMS_ADVISOR	DBMS_STATS_LIB	V$SQL
DBMS_ASSERT	DBMS_SWAT	V$SQL_PLAN
DBMS_AUTO_CLUSTERING	DBMS_SWAT_MM_INTERNAL	WRH$_SQLTEXT
DBMS_AUTO_INDEX	DBMS_SYS_ERROR	WRH$_SQL_PLAN
DBMS_AUTO_INDEX_INTERNAL	DBMS_XPLAN	WRI$_ADV_OBJECTS
DBMS_AUTO_TASK_ADMIN	DBMS_XPLAN_INTERNAL	WRI$_REPT_SPMEVOLVE
DBMS_AUTO_ZONEMAP_INTERNAL	DBMS_XPLAN_LIB	XMLTYPE

Documented No

Exceptions

Error Code	Reason
ORA-13607	The specified task or object SYS_AI_SPM_EVOLVE_TASK already exists
ORA-40216	Feature not supported

First Available 12.1

Security Model Owned by SYS with no privileges granted

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

Subprograms

ALTER_SESSION	I_ACCEPT_SQL_PLAN_BASELINE
AUTO_EVOLVE_TASK_CONFIG	I_CREATE_AI_EVOLVE_TASK
AUTO_PURGE_SQL_PLAN_BASELINE	I_CREATE_AUTO_EVOLVE_TASK
BOOL2STR	I_EVOLVE_CHECK_TIME_LIMIT
CHECK_AUTO_SPM_ENABLED	I_GET_PLAN
CHECK_SPM_ALLOWED	I_MASK_NUM
CREATE_SQL_PLAN_BASELINE	I_NOTIFY_SMB_CONFIG_UPDATE
FETCH_FLAGS_USING_PLAN_NAME	I_PROCESS_SQLSET_ROW_PHV
FETCH_HANDLE_USING_PLAN_NAME	I_REPORT_EVOLVE_TASK
FETCH_NAME_USING_SIG_CAT_PID	I_REPORT_SINGLE_PLAN
FETCH_PLAN_ID_USING_PLAN_NAME	I_SPM_TRC
FETCH_PLAN_SIGN_ID_AND_FLAGS	LOAD_ALTERNATE_PLANS
FETCH_SIG_USING_PLAN_NAME	LOAD_ALTERNATE_PLANS_FOR_SIG
FETCH_SIG_USING_SQL_HANDLE	LOAD_PLANS_FROM_AWR
FETCH_TEXT_USING_SQL_HANDLE	LOAD_PLANS_FROM_CC
GET_CONFIG_FILTER_NUM_AND_LEN	LOAD_PLANS_FROM_SQLSET
GET_NONACCEPTED_PLANS	LOAD_PLANS_SET
GET_OUTLINE	LOAD_SPM_SMB_CONFIG
GET_PARAM_VALUE	OPEN_GET_PLAN_INFO_CUR
GET_PLANS_FROM_CC	OPEN_LOADED_PLANS_CUR
GET_PLAN_HASH_2	PLAN_FINDINGS_INFO
GET_PLAN_OUTLINE	RESET_SESSION_CTX
GET_PLAN_ROWS_FROM_SMB	SET_SESSION_CTX
GET_SPM_TRACING_VALUE	STR2BOOL
GET_SQL_IDS_FROM_CC	UNMIGRATE_STORED_OUTLINE
GET_SQL_TEXT_FROM_SQL_ID	UPDATE_AUTOCAP_CONFIG
HIGH_FREQ_EVOLVE_TASK_PROC	UPDATE_CONFIG

ALTER_SESSION

Undocumented dbms_spm_internal.alter_session( param IN VARCHAR2, old IN BOOLEAN, new IN BOOLEAN);

TBD

AUTO_EVOLVE_TASK_CONFIG

Undocumented dbms_spm_internal.auto_evolve_task_config( parameter_name IN VARCHAR2, parameter_value IN NUMBER);

exec dbms_spm_internal.auto_evolve_task_config('AUTO_SPM_EVOLVE_TASK', 0);

      

      PL/SQL procedure successfully completed.

AUTO_PURGE_SQL_PLAN_BASELINE

Purges the existing SQL Plan Baselines dbms_spm_internal.auto_purge_sql_plan_baseline RETURN NUMBER;

set serveroutput on

      

      DECLARE

       x NUMBER;

      BEGIN

        x := dbms_spm_internal.auto_purge_sql_plan_baseline;

        dbms_output.put_line(TO_CHAR(x));

      END;

      /

      17

      

      PL/SQL procedure successfully completed.

BOOL2STR

Converts a Boolean to its string representation dbms_spm_internal.bool2str(val IN BOOLEAN) RETURN VARCHAR2;

DECLARE

       retVal dbms_id;

      BEGIN

        retVal := dbms_spm_internal.bool2str(TRUE);

        dbms_output.put_line(RetVal);

      END;

      /

      TRUE

      

      PL/SQL procedure successfully completed.

CHECK_AUTO_SPM_ENABLED

Undocumented

Suspect this is likely a feature that will only execute on Exadata, in the Oracle Cloud, or in a future version dbms_spm_internal.check_auto_spm_enabled;

exec dbms_spm_internal.check_auto_spm_enabled;

           *

      ORA-40216: feature not supported

CHECK_SPM_ALLOWED

Undocumented dbms_spm_internal.check_spm_allowed;

exec dbms_spm_internal.check_spm_allowed;

CREATE_SQL_PLAN_BASELINE

Create a SQL Plan Baseline dbms_spm_internal.create_sql_plan_baseline( sql_text IN CLOB, parsing_schema_name IN VARCHAR2, plan_name IN VARCHAR2, enabled IN VARCHAR2, fixed IN VARCHAR2) RETURN BINARY_INTEGER;

SELECT plan_name

      FROM dba_sql_plan_baselines;

      

      set serveroutput on

      

      DECLARE

       sqltxt CLOB := 'SELECT * FROM servers';

       i      BINARY_INTEGER;

      BEGIN

        i := dbms_spm_internal.create_sql_plan_baseline(sqltxt,'UWCLASS','UWPlan','YES','YES');

        dbms_output.put_line(TO_CHAR(i));

      END;

      /

      

      SELECT plan_name

      FROM dba_sql_plan_baselines;

FETCH_FLAGS_USING_PLAN_NAME

Undocumented dbms_spm_internal.fetch_flags_using_plan_name(plan_name IN VARCHAR2) RETURN NUMBER;

TBD

FETCH_HANDLE_USING_PLAN_NAME

Returns the handle for the named SQL Plan dbms_spm_internal.fetch_handle_using_plan_name(plan_name IN VARCHAR2) RETURN VARCHAR2;

SELECT plan_name

      FROM dba_sql_plan_baselines;

      

      set serveroutput on

      

      DECLARE

       ph VARCHAR2(30);

      BEGIN

        ph := dbms_spm_internal.fetch_handle_using_plan_name('UWPLAN');

        dbms_output.put_line(ph);

      END;

      /

FETCH_NAME_USING_SIG_CAT_PID

Undocumented dbms_spm_internal.fetch_name_using_sig_cat_pid( signature IN NUMBER, category IN VARCHAR2, plan_id IN NUMBER) RETURN VARCHAR2;

TBD

FETCH_PLAN_ID_USING_PLAN_NAME

Undocumented dbms_spm_internal.fetch_plan_id_using_plan_name(plan_name IN VARCHAR2) RETURN NUMBER;

TBD

FETCH_PLAN_SIGN_ID_AND_FLAGS

Undocumented dbms_spm_internal.fetch_plan_sign_id_and_flags( plan_name IN VARCHAR2, obj_type IN NUMBER) RETURN REF CURSOR;

TBD

FETCH_SIG_USING_PLAN_NAME

Undocumented dbms_spm_internal.fetch_sig_using_plan_name( signature IN NUMBER, plan_name IN VARCHAR2) RETURN NUMBER;

TBD

FETCH_SIG_USING_SQL_HANDLE

Returns the signature for a SQL Plan dbms_spm_internal.fetch_sig_using_sql_handle(handle IN VARCHAR2) RETURN NUMBER;

SELECT plan_name

      FROM dba_sql_plan_baselines;

      

      

      DECLARE

       ph  VARCHAR2(30);

       sig NUMBER;

      BEGIN

        ph := dbms_spm_internal.fetch_handle_using_plan_name('UWPLAN');

        dbms_output.put_line('Handle: ' || ph);

        sig := dbms_spm_internal.fetch_sig_using_sql_handle(ph);

        dbms_output.put_line('Signature: ' || TO_CHAR(sig));

      END;

      /

FETCH_TEXT_USING_SQL_HANDLE

Returns the text for a SQL Plan dbms_spm_internal.fetch_text_using_sql_handle(handle IN VARCHAR2) RETURN CLOB;

SELECT plan_name

      FROM dba_sql_plan_baselines;

      

      

      DECLARE

       ph  VARCHAR2(30);

       txt CLOB;

      BEGIN

        ph := dbms_spm_internal.fetch_handle_using_plan_name('UWPLAN');

        dbms_output.put_line('Handle: ' || ph);

        txt := dbms_spm_internal.fetch_text_using_sql_handle(ph);

        dbms_output.put_line('Text: ' || txt);

      END;

      /

GET_CONFIG_FILTER_NUM_AND_LEN

Undocumented dbms_spm_internal.get_config_filter_num_and_len( num_filters OUT BINARY_INTEGER, len_filters OUT BINARY_INTEGER);

TBD

GET_NONACCEPTED_PLANS

Undocumented dbms_spm_internal.get_nonaccepted_plans( sig IN NUMBER, sql_handle IN VARCHAR2) RETURN SYS_REFCURSOR;

TBD

GET_OUTLINE

Undocumented dbms_spm_internal.get_outline( sql_id IN VARCHAR2, plan_hash_value IN NUMBER) RETURN SYS_REFCURSOR;

TBD

GET_PARAM_VALUE

Undocumented dbms_spm_internal.get_param_value(parameter_name IN VARCHAR2) RETURN NUMBER;

TBD

GET_PLANS_FROM_CC

Undocumented dbms_spm_internal.get_plans_from_cc( sql_id IN VARCHAR2, plan_hash_value IN NUMBER) RETURN SYS_REFCURSOR;

TBD

GET_PLAN_HASH_2

Undocumented dbms_spm_internal.get_plan_hash_2( sql_id IN VARCHAR2, plan_hash_value IN NUMBER) RETURN SYS_REFCURSOR;

TBD

GET_PLAN_OUTLINE

Undocumented dbms_spm_internal.get_plan_outline( plan_name IN VARCHAR2, plan_outline OUT CLOB);

TBD

GET_PLAN_ROWS_FROM_SMB

Undocumented dbms_spm_internal.get_plan_rows_from_smb( plan_id IN NUMBER, signature IN NUMBER) RETURN REF CURSOR;

TBD

GET_SPM_TRACING_VALUE

Undocumented dbms_spm_internal.get_spm_tracing_value RETURN BINARY_INTEGER;

SELECT dbms_spm_internal.get_spm_tracing_value;

      

      GET_SPM_TRACING_VALUE

      ---------------------

                          0

GET_SQL_IDS_FROM_CCGET_SPM_TRACING_VALUE

Undocumented dbms_spm_internal.get_sql_ids_from_cc( attr_name IN VARCHAR2, attr_value IN VARCHAR2) RETURN SYS_REFCURSOR;

TBD

GET_SQL_TEXT_FROM_SQL_ID (new 23ai)

Undocumented dbms_spm_internal.get_sql_text_from_sql_id(sql_id IN VARCHAR2) RETURN CLOB;

TBD

HIGH_FREQ_EVOLVE_TASK_PROC

Undocumented dbms_spm_internal.high_freq_evolve_task_proc;

exec dbms_spm_internal.high_freq_evolve_task_proc;

      

      PL/SQL procedure successfully completed.

I_ACCEPT_SQL_PLAN_BASELINE

Undocumented dbms_spm_internal.i_accept_sql_plan_baseline( task_name IN VARCHAR2, exec_name IN VARCHAR2, obj_id IN NUMBER, task_owner IN VARCHAR2, force IN BOOLEAN) RETURN NUMBER;

TBD

I_CREATE_AI_EVOLVE_TASK

Undocumented dbms_spm_internal.i_create_ai_evolve_task(

exec dbms_spm_internal.i_create_ai_evolve_task;

           *

      ORA-13607: The specified task or object SYS_AI_SPM_EVOLVE_TASK already exists

I_CREATE_AUTO_EVOLVE_TASK

Undocumented dbms_spm_internal.i_create_auto_evolve_task;

exec dbms_spm_internal.i_create_auto_evolve_task;

I_EVOLVE_CHECK_TIME_LIMIT

Sets the maximum time limit for evolving baselines dbms_spm_internal.i_evolve_check_time_limit(time_limit IN NUMBER);

exec dbms_spm_internal.i_evolve_check_time_limit(30);

I_GET_PLAN

Undocumented dbms_spm_internal.i_get_plan( trace IN BINARY_INTEGER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER, source IN VARCHAR2) RETURN dbms-spm_internal.sql_plan_type_table

TBD

I_MASK_NUM

Undocumented dbms_spm_internal.i_mask_num( value IN NUMBER, mask IN BOOLEAN) RETURN VARCHAR2;

DECLARE

       b      BOOLEAN := FALSE;

       retVal VARCHAR2(30);

      BEGIN

        retVal := dbms_spm_internal.i_mask_num(42, TRUE);

        dbms_output.put_line(retVal);

      END;

      /

      XXX

      

      PL/SQL procedure successfully completed.

I_NOTIFY_SMB_CONFIG_UPDATE

Undocumented dbms_spm_internal.i_notify_smb_config_update;

exec dbms_spm_internal.i_notify_smb_config_update;

I_PROCESS_SQLSET_ROW_PHV

Undocumented dbms_spm_internal.i_process_sqlset_row_phv( trace IN BINARY_INTEGER, plan_row IN OUT sys.sqlset_row;

TBD

I_REPORT_EVOLVE_TASK

Undocumented dbms_spm_internal.i_report_evolve_task( tid IN NUMBER, wkld IN OUT dbms_sqltune_util1.task_wkldobj, exec_name IN VARCHAR2, exec_type IN VARCHAR2, obj_id IN NUMBER, level_flags IN NUMBER, plan_format IN VARCHAR2, section IN VARCHAR2, report_ref IN VARCHAR2) RETURN XMLTYPE;

TBD

I_REPORT_SINGLE_PLAN

Undocumented dbms_spm_internal.i_report_single_plan( wkld_oid IN NUMBER, sql_handle IN VARCHAR2, plan_name IN VARCHAR2, base_plan_name IN VARCHAR2, plan_creator IN VARCHAR2, tid IN NUMBER, exec_name IN VARCHAR2, exec_type IN VARCHAR2, level_flags IN NUMBER, plan_format IN VARCHAR2, section IN VARCHAR2, sql_text IN CLOB, parsing_schema_name IN VARCHAR2) RETURN XMLTYPE;

TBD

I_SPM_TRC

Undocumented dbms_spm_internal.i_spm_trc( trace IN BINARY_INTEGER, message IN CLOB);

TBD

LOAD_ALTERNATE_PLANS

Undocumented dbms_spm_internal.load_alternate_plans( sql_handle IN VARCHAR2, limit IN NUMBER, min_executions IN NUMBER, source IN VARCHAR2, baselines IN VARCHAR2);

TBD

LOAD_ALTERNATE_PLANS_FOR_SIG (new 23ai)

Undocumented dbms_spm_internal.load_alternative_plans_for_sig( signature IN NUMBER, limit IN NUMBER, min_executions IN NUMBER, source IN VARCHAR2, baselines IN VARCHAR2, aimode IN BOOLEAN, stsratio IN NUMBER, threshold IN NUMBER);

TBD

LOAD_PLANS_FROM_AWR

Undocumented dbms_spm_internal.load_plans_from_awr( begin_snap IN NUMBER, end_snap IN NUMBER, basic_filter IN VARCHAR2, fixed IN VARCHAR2, enabled IN VARCHAR2, commit_rows IN NUMBER, is_evolve IN BOOLEAN, dbid IN NUMBER) RETURN BINARY_INTEGER;

TBD

LOAD_PLANS_FROM_CC

Undocumented dbms_spm_internal.load_plans_from_cc( trace IN BINARY_INTEGER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER, sql_text IN CLOB, fixed IN VARCHAR2, enabled IN VARCHAR2, is_evolve IN BOOLEAN) RETURN BINARY_INTEGER;

TBD

LOAD_PLANS_FROM_SQLSET

Undocumented dbms_spm_internal.load_plans_from_sqlset( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2, basic_filter IN VARCHAR2, fixed IN VARCHAR2, enabled IN VARCHAR2, commit_rows IN NUMBER, is_evolve IN BOOLEAN) RETURN BINARY_INTEGER;

TBD

LOAD_PLANS_SET

Undocumented dbms_spm_internal.load_plans_set( sql_text IN CLOB, plans_set IN dbms_spm_internal.plans_tab_type, plans_cnt IN BINARY_INTEGER, category IN VARCHAR2, flags IN BINARY_INTEGER, plan_name IN VARCHAR2, sess_user IN VARCHAR2, origin IN BINARY_INTEGER, description IN VARCHAR2) RETURN BINARY_INTEGER;

TBD

LOAD_SPM_SMB_CONFIG (new 23ai)

Undocumented dbms_spm_internal.load_spm_smb_config;

exec dbms_spm_internal.load_spm_smb_config;

      

      PL/SQL procedure successfully completed.

OPEN_GET_PLAN_INFO_CUR (new 23ai)

Undocumented dbms_spm_internal.open_get_plan_info_cur( sig IN NUMBER, sql_id IN VARCHAR2, plan_name IN VARCHAR2, get_verified_plans_cur OUT REF CURSOR;

TBD

OPEN_LOADED_PLANS_CUR (new 23ai)

Undocumented dbms_spm_internal.open_loaded_plans_cur( handle IN VARCHAR2, loaded_plans_cur OUT REF CURSOR);

TBD

PLAN_FINDINGS_INFO (new 23ai)

Undocumented dbms_spm_internal.plan_findings_info( user IN VARCHAR2, task_name_p IN VARCHAR2, new_exec IN VARCHAR2, reproduced OUT VARCHAR2, perf_dif OUT NUMBER);

TBD

RESET_SESSION_CTX

Resets the stssion context dbms_spm_internal.reset_session_ctx( orig_session_ctx IN sys.dbms_spm_internal.sessioncontext);

TBD

SET_SESSION_CTX

Sets the session context dbms_spm_internal.set_session_cts( orig_session_ctx IN OUT sys.dbms_spm_internal.sessioncontext);

TBD

STR2BOOL

Converts a string to its Boolean representation dbms_spm_internal.str2bool(val IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF dbms_spm_internal.str2bool('TRUE') THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

      TRUE

      

      PL/SQL procedure successfully completed.

UNMIGRATE_STORED_OUTLINE

Undocumented dbms_spm_internal.unmigrate_stored_outline( attribute_name IN VARCHAR2, attribute_value IN CLOB) RETURN BINARY_INTEGER;

TBD

UPDATE_AUTOCAP_CONFIG

Undocumented dbms_spm_internal.update_autocap_config( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, allow IN BOOLEAN);

TBD

UPDATE_CONFIG

Undocumented

Overload 1 dbms_spm_internal.update_config( parameter_name IN VARCHAR2, parameter_value IN NUMBER);

TBD

Overload 2 dbms_spm_internal.update_config( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2);

TBD