Oracle DBMS_SPM_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
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