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

Related Topics
Built-in Functions
Built-in Packages
DBMS_SPM
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx