Oracle | DBMS_SPM

Oracle DBMS_SPM_INTERNAL Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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 VACHAR2, 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