| General Information |
| Purpose |
SQL Plan Management Internal Support |
| Source |
{ORACLE_HOME}/rdbms/admin/prvsspmi.plb |
| Dependencies |
| DBMS_SMB |
DBMS_XPLAN |
| DBMS_SMB_INTERNAL |
DUAL |
| DBMS_SPM |
PLITBLM |
| DBMS_SPM_LIB |
SMB$CONFIG |
| DBMS_SQLTUNE_INTERNAL |
SQL$TEXT |
| DBMS_SQLTUNE_UTIL2 |
SQLOBJ$ |
| DBMS_SYS_ERROR |
SQLSET_ROW |
|
| Defined Data Type |
TYPE name_list IS TABLE OF VARCHAR2(30); |
| Security Model |
Owned by SYS with no privileges granted |
| |
| 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 (new 11.2.0.3)  |
| 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 (new 11.2.0.3 parameters) |
| 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 |
