Oracle DBMS_SPM_INTERNAL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose SQL Plan Management Internal Support
AUTHID CURRENT_USER
Data Types TYPE name_list IS TABLE OF VARCHAR2(30);
Dependencies
DBA_ADVISOR_ACTIONS DBMS_SMB_INTERNAL PRVT_REPORT_TAGS
DBA_ADVISOR_EXECUTIONS DBMS_SPM SMB$CONFIG
DBA_ADVISOR_EXEC_PARAMETERS DBMS_SPM_LIB SQL$TEXT
DBA_ADVISOR_FINDINGS DBMS_SQLTUNE SQLOBJ$
DBA_ADVISOR_OBJECTS DBMS_SQLTUNE_INTERNAL SQLOBJ$AUXDATA
DBA_ADVISOR_RECOMMENDATIONS DBMS_SQLTUNE_UTIL1 SQLSET_ROW
DBA_ADVISOR_SQLSTATS DBMS_SQLTUNE_UTIL2 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 DUAL WRI$_REPT_SPMEVOLVE
DBMS_LOB PLITBLM XMLAGG
DBMS_OUTPUT PRVT_ADVISOR XMLTYPE
DBMS_SMB    
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 FETCH_TEXT_USING_SQL_HANDLE GET_SPM_HINTSET
CREATE_SQL_PLAN_BASELINE GET_NONACCEPTED_PLANS GET_SPM_TRACING_VALUE
FETCH_HANDLE_USING_PLAN_NAME GET_OUTLINE GET_SQL_IDS_FROM_CC
FETCH_NAME_USING_SIG_CAT_PID GET_PARAM_VALUE LOAD_PLANS_SET
FETCH_PLAN_INFO GET_PLANS_FROM_CC UNMIGRATE_STORED_OUTLINE
FETCH_SIG_USING_PLAN_NAME GET_PLAN_HASH_2 UPDATE_CONFIG
FETCH_SIG_USING_SQL_HANDLE    
 
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_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_INFO
Undocumented dbms_spm_internal.fetch_plan_info(plan_name IN VARCHAR2)
RETURN SYS_REFCURSOR;
DECLARE
 src SYS_REFCURSOR;
BEGIN
  src := dbms_spm_internal.fetch_plan_info('UWPLAN');
END;
/
 
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_SPM_HINTSET
Undocumented dbms_spm_internal.get_spm_hintset(
v_signature IN NUMBER,
v_category  IN VARCHAR2,
v_obj_type  IN NUMBER,
v_plan_name IN VARCHAR2)
RETURN v_hintset;
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_CC
Undocumented dbms_spm_internal.get_sql_ids_from_cc(attr_name IN VARCHAR2, attr_value IN VARCHAR2)
RETURN SYS_REFCURSOR;
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_CONFIG
Undocumented dbms_spm_internal.update_config(
parameter_name  IN VARCHAR2,
parameter_value IN NUMBER);
TBD

Related Topics
DBMS_SPM
Packages

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