ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
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
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;
/
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;
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
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
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;
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;
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;
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;
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;
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;