Oracle | DBMS_SPM

Oracle DBMS_SPM_INTERNAL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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	SQLSET_ROW
DBA_AUTO_INDEX_EXECUTIONS	DBMS_SQLTUNE_UTIL2	SQL_PLAN_TABLE_TYPE
DBA_SQL_PLAN_BASELINES	DBMS_STANDARD	SYS_IXMLAGG
DBMS_ADVISOR	DBMS_STATS_INTERNAL	V$SQL_PLAN
DBMS_ASSERT	DBMS_STATS_LIB	WRI$_ADV_OBJECTS
DBMS_AUTO_INDEX	DBMS_SYS_ERROR	WRI$_REPT_SPMEVOLVE
DBMS_AUTO_INDEX_INTERNAL	DBMS_XPLAN	XMLAGG
DBMS_AUTO_TASK_ADMIN	DBMS_XPLAN_INTERNAL	XMLTYPE
DBMS_AUTO_ZONEMAP_INTERNAL	DBMS_XPLAN_LIB

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	GET_NONACCEPTED_PLANS	I_NOTIFY_SMB_CONFIG_UPDATE
AUTO_EVOLVE_TASK_CONFIG	GET_OUTLINE	I_PROCESS_SQLSET_ROW_PHV
AUTO_PURGE_SQL_PLAN_BASELINE	GET_PARAM_VALUE	I_REPORT_EVOLVE_TASK
BOOL2STR	GET_PLANS_FROM_CC	I_REPORT_SINGLE_PLAN
CHECK_AUTO_SPM_ENABLED	GET_PLAN_HASH_2	I_SPM_TRC
CHECK_SPM_ALLOWED	GET_PLAN_OUTLINE	LOAD_ALTERNATE_PLANS
CREATE_SQL_PLAN_BASELINE	GET_PLAN_ROWS_FROM_SMB	LOAD_PLANS_FROM_AWR
FETCH_FLAGS_USING_PLAN_NAME	GET_SPM_TRACING_VALUE	LOAD_PLANS_FROM_CC
FETCH_HANDLE_USING_PLAN_NAME	GET_SQL_IDS_FROM_CC	LOAD_PLANS_FROM_SQLSET
FETCH_NAME_USING_SIG_CAT_PID	HIGH_FREQ_EVOLVE_TASK_PROC	LOAD_PLANS_SET
FETCH_PLAN_ID_USING_PLAN_NAME	I_ACCEPT_SQL_PLAN_BASELINE	RESET_SESSION_CTX
FETCH_PLAN_SIGN_ID_AND_FLAGS	I_CREATE_AI_EVOLVE_TASK	SET_SESSION_CTX
FETCH_SIG_USING_PLAN_NAME	I_CREATE_AUTO_EVOLVE_TASK	STR2BOOL
FETCH_SIG_USING_SQL_HANDLE	I_EVOLVE_CHECK_TIME_LIMIT	UNMIGRATE_STORED_OUTLINE
FETCH_TEXT_USING_SQL_HANDLE	I_GET_PLAN	UPDATE_AUTOCAP_CONFIG
GET_CONFIG_FILTER_NUM_AND_LEN	I_MASK_NUM	UPDATE_CONFIG

ALTER_SESSION (new 21c)

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 (new 21c)

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;

      BEGIN dbms_spm_internal.check_auto_spm_enabled; END;

      *

      ERROR at line 1:

      ORA-40216: feature not supported

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

      ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 6062

      ORA-06512: at line 1

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;

      

      set serveroutput on

      

      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;

      

      set serveroutput on

      

      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

      FROM dual;

      

      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

HIGH_FREQ_EVOLVE_TASK_PROC (new 21c)

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;

      BEGIN dbms_spm_internal.i_create_ai_evolve_task; END;

      *

      ERROR at line 1:

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

      ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2712

      ORA-06512: at "SYS.PRVT_ADVISOR", line 6091

      ORA-06512: at "SYS.PRVT_ADVISOR", line 1842

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

      ORA-06512: at "SYS.PRVT_ADVISOR", line 7144

      ORA-06512: at "SYS.PRVT_ADVISOR", line 1615

      ORA-06512: at "SYS.PRVT_ADVISOR", line 6053

      ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2618

      ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2757

      ORA-06512: at line 1

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_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

RESET_SESSION_CTX (new 21c)

Undocumented dbms_spm_internal.reset_session_ctx( orig_session_ctx IN sys.dbms_spm_internal.sessioncontext);

TBD

SET_SESSION_CTX (new 21c)

Undocumented dbms_spm_internal.set_session_cts( orig_session_ctx IN OUT sys.dbms_spm_internal.sessioncontext);

TBD

STR2BOOL (new 21c)

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