Oracle DBMS_SPM_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose SQL Plan Management Internal Support
AUTHID CURRENT_USER
Data Types TYPE name_list IS TABLE OF VARCHAR2(30);

TYPE sql_plan_type_table ...;
Dependencies
CDB_HIST_SNAPSHOT DBMS_SMB_INTERNAL  
DBA_ADVISOR_ACTIONS DBMS_SPM PRVT_REPORT_TAGS
DBA_ADVISOR_EXECUTIONS DBMS_SPM_LIB SMB$CONFIG
DBA_ADVISOR_EXEC_PARAMETERS DBMS_SQLTUNE SQL$TEXT
DBA_ADVISOR_FINDINGS DBMS_SQLTUNE_INTERNAL SQLOBJ$
DBA_ADVISOR_OBJECTS DBMS_SQLTUNE_UTIL1 SQLOBJ$AUXDATA
DBA_ADVISOR_RECOMMENDATIONS DBMS_SQLTUNE_UTIL2 SQLSET_ROW
DBA_ADVISOR_SQLSTATS DBMS_STANDARD SQL_PLAN_TABLE_TYPE
DBA_ADVISOR_TASKS DBMS_STATS_LIB SYS_IXMLAGG
DBA_SQL_PLAN_BASELINES DBMS_SYS_ERROR V$SQL_PLAN
DBMS_ADVISOR DBMS_XPLAN WRI$_ADV_OBJECTS
DBMS_ASSERT DBMS_XPLAN_LIB WRI$_REPT_SPMEVOLVE
DBMS_LOB DUAL XMLAGG
DBMS_OUTPUT PLITBLM XMLTYPE
DBMS_SMB PRVT_ADVISOR  
Documented No
First Available 12.1.0
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsspmi.plb
Subprograms
 
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;
/
 
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_spm_internal.fetch_plan_id_using_plan_name(plan_name IN VARCHAR2)
RETURN NUMBER;
TBD
 
FETCH_PLAN_SIGN_ID_AND_FLAGS (new 12.2)
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_NONACCEPTED_PLANS
Undocumented dbms_spm_internal.get_nonaccepted_plans(
sig        IN NUMBER,
sql_handle IN VARCHAR2)
RETURN SYS_REFCURSOR;
DECLARE
 src SYS_REFCURSOR;
 ph  VARCHAR2(30);
 sig NUMBER;
BEGIN
  ph := dbms_spm_internal.fetch_handle_using_plan_name('UWPLAN');
  sig := dbms_spm_internal.fetch_sig_using_sql_handle(ph);
  src := dbms_spm_internal.get_nonaccepted_plans(sig, ph);
END;
/
 
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 (new 12.2)
Undocumented dbms_spm_internal.get_plan_outline(
plan_name    IN  VARCHAR2,
plan_outline OUT CLOB);
TBD
 
GET_PLAN_ROWS_FROM_SMB (new 12.2)
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_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
 
I_ACCEPT_SQL_PLAN_BASELINE (new 12.2)
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_AUTO_EVOLVE_TASK (new 12.2)
Undocumented dbms_spm_internal.i_create_auto_evolve_task;
exec dbms_spm_internal.i_create_auto_evolve_task;
 
I_EVOLVE_CHECK_TIME_LIMIT (new 12.2)
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 (new 12.2)
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_NOTIFY_SMB_CONFIG_UPDATE (new 12.2)
Undocumented dbms_spm_internal.i_notify_smb_config_update;
exec dbms_spm_internal.i_notify_smb_config_update;
 
I_PROCESS_SQLSET_ROW_PHV (new 12.2)
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 (new 12.2)
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_SPM_TRC (new 12.2)
Undocumented dbms_spm_internal.i_spm_trc(
trace   IN BINARY_INTEGER,
message IN CLOB);
TBD
 
LOAD_ALTERNATE_PLANS (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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
 
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 (new 12.2)
Undocumented dbms_spm_internal.update_autocap_config(
parameter_name  IN VARCHAR2,
parameter_value IN VARCHAR2,
allow           IN BOOLEAN);
TBD
 
UPDATE_CONFIG
Undocumented dbms_spm_internal.update_config(
parameter_name  IN VARCHAR2,
parameter_value IN NUMBER);
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_SPM
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved