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
DBMS_SQLTUNE provides the interface for tuning SQL on demand. This INTERNAL package provides support for DBMS_SQLTUNE and other dependent objects.
AUTHID
DEFINER
Data Types
TYPE i_capture_statset_pair ...
TYPE i_ql_pdb_table ...
TYPE sqlmon_report_usg_t ...
Dependencies
ANYDATA
DBMS_SQLTUNE_UTIL0
WRI$_ADV_ACTIONS
CDB_SQL_PROFILES
DBMS_SQLTUNE_UTIL1
WRI$_ADV_DEF_PARAMETERS
DBA_ADVISOR_ACTIONS
DBMS_SQLTUNE_UTIL2
WRI$_ADV_EXECUTIONS
DBA_ADVISOR_EXECUTIONS
DBMS_STANDARD
WRI$_ADV_FINDINGS
DBA_ADVISOR_EXEC_PARAMETERS
DBMS_STATS
WRI$_ADV_HDM_T
DBA_ADVISOR_FINDINGS
DBMS_STATS_INTERNAL
WRI$_ADV_OBJECTS
DBA_ADVISOR_OBJECTS
DBMS_SYSTEM
WRI$_ADV_PARAMETERS
DBA_ADVISOR_PARAMETERS
DBMS_SYS_ERROR
WRI$_ADV_RATIONALE
DBA_ADVISOR_RECOMMENDATIONS
DBMS_UTILITY
WRI$_ADV_RECOMMENDATIONS
DBA_ADVISOR_SQLSTATS
DBMS_WORKLOAD_REPLAY_I
WRI$_ADV_SPM_EVOLVE
DBA_ADVISOR_TASKS
DBMS_XPLAN
WRI$_ADV_SQLTUNE
DBA_AUTOTASK_WINDOW_CLIENTS
DBMS_XPLAN_INTERNAL
WRI$_ADV_SQLT_RTN_PLAN
DBA_DB_LINKS
DBMS_XPLAN_TYPE_TABLE
WRI$_ADV_TASKS
DBA_SQLSET_DEFINITIONS
DUAL
WRI$_REPT_SQLMONITOR
DBA_SQL_PROFILES
PLITBLM
WRI$_REPT_SQLPI
DBMS_ADVISOR
PRVT_ACCESS_ADVISOR
WRI$_REPT_SQLT
DBMS_ASSERT
PRVT_ADVISOR
WRI$_SQLMON_USAGE
DBMS_AUTO_INDEX_INTERNAL
PRVT_HDM
WRI$_SQLSET_BINDS
DBMS_AUTO_SQLTUNE
PRVT_REPORT_TAGS
WRI$_SQLSET_DEFINITIONS
DBMS_LOB
PRVT_SQLADV_INFRA
WRI$_SQLSET_ID_SEQ
DBMS_LOCK
PRVT_SQLPA
WRI$_SQLSET_MASK
DBMS_MANAGEMENT_PACKS
PRVT_SQLPROF_INFRA
WRI$_SQLSET_PLANS
DBMS_REPORT
PRVT_SQLSET_INFRA
WRI$_SQLSET_PLANS_TOCAP
DBMS_SMB
PRVT_WORKLOAD
WRI$_SQLSET_PLAN_LINES
DBMS_SMB_INTERNAL
SQLSET_ROW
WRI$_SQLSET_REFERENCES
DBMS_SPM
SQL_BINDS
WRI$_SQLSET_REF_ID_SEQ
DBMS_SPM_INTERNAL
SQL_PLAN_ALLSTAT_ROW_TYPE
WRI$_SQLSET_STATEMENTS
DBMS_SQLDIAG
SQL_PLAN_ROW_TYPE
WRI$_SQLSET_STATISTICS
DBMS_SQLDIAG_INTERNAL
SQL_PLAN_STAT_ROW_TYPE
WRI$_SQLSET_STMT_ID_SEQ
DBMS_SQLPA
SQL_PLAN_TABLE_TYPE
WRI$_SQLSET_STS_TOPACK
DBMS_SQLQ_INTERNAL
V$SYSTEM_PARAMETER
X$KEACMDN
DBMS_SQLTCB_INTERNAL
V_$PARAMETER
XMLSEQUENCE
DBMS_SQLTUNE
WRH$_SQLTEXT
XMLTYPE
DBMS_SQLTUNE_LIB
Documented
No
Exceptions
Error Code
Reason
ORA-13607
The specified task or object
SYS_AUTO_SQL_TUNING_TASK already exists
dbms_sqltune_internal.accept_all_sql_profiles(
task_name IN VARCHAR2,
category IN VARCHAR2,
replace IN BOOLEAN,
force_match IN BOOLEAN,
profile_type IN VARCHAR2,
autotune_period IN NUMBER,
execution_name IN VARCHAR2,
task_owner IN VARCHAR2,
description IN VARCHAR2,
profile_creator IN VARCHAR2);
dbms_sqltune_internal.i_accept_sql_profile(
task_name IN VARCHAR2,
task_owner IN VARCHAR2,
object_id IN NUMBER,
name IN VARCHAR2,
description IN VARCHAR2,
category IN VARCHAR2,
replace IN BOOLEAN,
force_match IN BOOLEAN,
is_patch IN BOOLEAN,
profile_type IN VARCHAR2,
profile_creator IN VARCHAR2)
RETURN VARCHAR2;
dbms_sqltune_internal.i_add_sqlset_reference(
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2,
ref_owner IN VARCHAR2,
description IN VARCHAR2)
RETURN NUMBER;
dbms_sqltune_internal.i_add_task_sqlset_reference(
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2,
advisor_name IN VARCHAR2,
task_name IN VARCHAR2,
task_owner_id IN NUMBER,
ref_id OUT NUMBER);
dbms_sqltune_internal.i_combine_capture_stats(
stats_src IN sys.dbms_sqltune_internal.i_capture_statset_pair,
stats_dst IN OUT sys.dbms_sqltune_internal.i_capture_statset_pair);
Undocumented but executed by $ORACLE_HOME/rdbms/admin/ sqltacrt.sql
dbms_sqltune_internal.i_create_auto_tuning_task;
exec dbms_sqltune_internal.i_create_auto_tuning_task;
BEGIN dbms_sqltune_internal.i_create_auto_tuning_task; END;
*
ERROR at line 1:
ORA-13607: The specified task or object SYS_AUTO_SQL_TUNING_TASK already exists
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14353
ORA-06512: at "SYS.PRVT_ADVISOR", line 6086
ORA-06512: at "SYS.PRVT_ADVISOR", line 1843
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 7135
ORA-06512: at "SYS.PRVT_ADVISOR", line 1616
ORA-06512: at "SYS.PRVT_ADVISOR", line 6048
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14265
ORA-06512: at line 1
-- the following code can be found in $ORACLE_HOME/rdbms/admin/execsqlt.sql
Rem Create the automatic SQL Tuning and automatic SPM evolve tasks
Rem If the tasks already exist (catproc is being re-run), do not error.
BEGIN
sys.dbms_sqltune_internal.i_create_auto_tuning_task;
EXCEPTION
WHEN OTHERS THEN
IF (sqlcode = -13607 -- task already exists
OR
sqlcode = -65040) THEN -- operation not allowed inside PDB(lrg 7000350)
NULL;
ELSE
RAISE;
END IF;
END;
/
dbms_sqltune_internal.i_create_sql_plan_baseline(
task_name IN VARCHAR2,
object_id IN NUMBER,
plan_hash_value IN NUMBER,
owner_name IN VARCHAR2,
baseline_creator IN VARCHAR2);
dbms_sqltune_internal.i_create_sql_profile(
sql_text IN CLOB,
profile_xml IN CLOB,
name IN VARCHAR2,
description IN VARCHAR2,
category IN VARCHAR2,
creator IN VARCHAR2,
validate IN BOOLEAN,
replace IN BOOLEAN,
force_match IN BOOLEAN,
type IN VARCHAR2,
is_patch IN BOOLEAN,
plan_rows IN sys.sql_plan_table_type)
RETURN VARCHAR2;
TBD
Overload 2
dbms_sqltune_internal.i_create_sql_profile(
hash_value IN NUMBER,
address IN RAW,
profile_xml IN CLOB,
name IN VARCHAR2,
description IN VARCHAR2,
category IN VARCHAR2,
creator IN VARCHAR2,
validate IN BOOLEAN,
replace IN BOOLEAN,
force_match IN BOOLEAN,
is_patch IN BOOLEAN)
RETURN VARCHAR2;
dbms_sqltune_internal.i_create_tuning_task(
sql_rec IN sys.sqlset_row,
scope IN VARCHAR2,
tlimit IN NUMBER,
taskname IN VARCHAR2,
descrp IN VARCHAR2,
adv_name IN VARCHAR2,
task_owner_id IN NUMBER)
RETURN VARCHAR2;
TBD
Overload 2
dbms_sqltune_internal.i_create_tuning_task(
sqlsetowner IN VARCHAR2,
basicfilter IN VARCHAR2,
objfilter IN VARCHAR2,
plnfilter IN VARCHAR2,
rank1 IN VARCHAR2,
rank2 IN VARCHAR2,
rank3 IN VARCHAR2,
rsltperc IN NUMBER,
rsltlimit IN NUMBER,
scope IN VARCHAR2,
tlimit IN NUMBER,
taskname IN VARCHAR2,
descrp IN VARCHAR2,
adv_name IN VARCHAR2,
task_owner_id IN NUMBER)
RETURN VARCHAR2;
TBD
Overload 3
dbms_sqltune_internal.i_create_tuning_task(
spa_task_id IN NUMBER,
spa_compare_exec IN VARCHAR2,
spa_exec_one IN VARCHAR2,
spa_exec_one_type IN NUMBER,
spa_env_obj_id IN NUMBER,
spa_sql_obj_id IN NUMBER,
spa_sqlset_name IN VARCHAR2,
spa_sqlset_owner IN VARCHAR2,
basic_filter IN VARCHAR2,
scope IN VARCHAR2,
tlimit IN NUMBER,
taskname IN VARCHAR2,
descrp IN VARCHAR2,
adv_name IN VARCHAR2,
task_owner_id IN NUMBER)
RETURN VARCHAR2;
dbms_sqltune_internal.i_delete_sqlset(
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2,
sqlset_id IN NUMBER,
sts_cursor IN REF CURSOR)
RETURN NUMBER;
dbms_sqltune_internal.i_get_command_pieces(
owner_name IN VARCHAR2,
cmd IN VARCHAR2,
att1 IN VARCHAR2,
att2 IN VARCHAR2,
att3 IN VARCHAR2,
att4 IN VARCHAR2,
att5 IN VARCHAR2,
att6 IN VARCHAR2,
level_flags IN NUMBER,
first_piece IN OUT VARCHAR2,
second_piece IN OUT VARCHAR2,
profile_force_match IN NUMBER);
dbms_sqltune_internal.i_load_sql_profile(
sqlset_id IN NUMBER,
load_cur IN REF CURSOR,
attribute_selected IN BINARY_INTEGER,
load_action IN BINARY_INTEGER,
load_flags IN BINARY_INTEGER,
update_option IN VARCHAR2,
ignore_null IN BOOLEAN,
commit_rows IN BINARY_INTEGER,
capture_stats IN OUT sys.dbms_sqltune_internal.i_capture_stat_context,
filter_recursive IN BOOLEAN)
RETURN NUMBER;
dbms_sqltune_internal.i_load_sqlset_cursor(
sql_stmt IN sys.sqlset_row,
profile_xml IN CLOB,
name IN VARCHAR2,
description IN VARCHAR2,
category IN VARCHAR2,
creator IN VARCHAR2,
task_id IN BINARY_INTEGER,
exec_name IN VARCHAR2,
object_id IN BINARY_INTEGER,
finding_id IN BINARY_INTEGER,
rec_id IN BINARY_INTEGER,
outline IN CLOB,
plan_hash IN NUMBER,
verified IN BINARY_INTEGER,
validate IN BOOLEAN,
replace IN BOOLEAN,
force_match IN BOOLEAN,
is_patch IN BOOLEAN)
RETURN VARCHAR2;
dbms_sqltune_internal.i_open_script_cursor(
tid IN NUMBER,
exec_name IN VARCHAR2,
rec_flags IN BINARY_INTEGER,
object_id IN NUMBER,
stmt_cnt IN NUMBER,
rec_id IN NUMBER)
RETURN REF CURSOR;
dbms_sqltune_internal.i_process_sql(
stmt IN OUT sys.sqlset_row,
exec_userid IN NUMBER,
action IN VARCHAR2,
time_limit IN BINARY_INTEGER,
ctrl_options IN CLOB,
extra_result OUT CLOB,
err_code OUT NUMBER,
err_mesg OUT VARCHAR2);
dbms_sqltune_internal.i_remove_sqlset_reference(
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2,
ref_owner IN VARCHAR2,
ref_id IN NUMBER,
force IN NUMBER);
dbms_sqltune_internal.i_report_auto_summary_xml(
report_reference IN VARCHAR2,
tid IN NUMBER,
begin_exec IN VARCHAR2,
end_exec IN VARCHAR2,
section IN VARCHAR2)
RETURN sys.xmltype;
dbms_sqltune_internal.i_report_sqlt_single_sql_xml(
report_reference IN VARCHAR2,
task_name IN VARCHAR2,
ename IN VARCHAR2,
objid IN NUMBER,
owner_id IN NUMBER,
plan_group IN VARCHAR2,
plan_type IN VARCHAR2,
report_level IN VARCHAR2)
RETURN sys.xmltype;
dbms_sqltune_internal.i_report_sql_table_xml(
report_reference IN VARCHAR2,
tid IN NUMBER,
begin_ename IN VARCHAR2,
end_ename IN VARCHAR2,
finding_filter IN VARCHAR2,
orderby IN VARCHAR2,
in_order IN VARCHAR2,
result_limit IN NUMBER,
starting_with IN NUMBER,
stats_hash_filter IN NUMBER,
index_hash_filter IN NUMBER,
is_em_express IN BOOLEAN)
RETURN sys.xmltype;
dbms_sqltune_internal.i_report_tuning_task(
tid IN NUMBER,
task_name IN VARCHAR2,
begin_exec IN VARCHAR2,
end_exec IN VARCHAR2,
obj_id IN NUMBER,
result_limit IN NUMBER,
type IN VARCHAR2,
level IN VARCHAR2,
section IN VARCHAR2,
flags IN NUMBER,
owner_name IN VARCHAR2)
RETURN CLOB;
dbms_sqltune_internal.i_script_tuning_task(
task_id IN NUMBER,
task_name IN VARCHAR2,
owner_name IN VARCHAR2,
exec_name IN VARCHAR2,
rec_type IN VARCHAR2,
object_id IN NUMBER,
result_limit IN NUMBER,
script IN OUT CLOB);
dbms_sqltune_internal.i_update_sqlset(
name IN VARCHAR2,
sqlset_owner IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2);
TBD
Overload 2
dbms_sqltune_internal.i_update_sqlset(
name IN VARCHAR2,
sqlset_owner IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name IN VARCHAR2,
attribute_value IN NUMBER);
dbms_sqltune_internal.i_validate_level(
level_text IN VARCHAR2,
level_flags OUT NUMBER,
plan_format OUT VARCHAR2,
plan_flags OUT NUMBER,
exec_type IN VARCHAR2,
error_code OUT VARCHAR2);
dbms_sqltune_internal.sqlmon_parse_report_ref(
report_reference IN VARCHAR2,
with_summary IN BOOLEAN,
component_name OUT VARCHAR2,
report_name OUT VARCHAR2,
sql_id OUT VARCHAR2,
session_id OUT NUMBER,
session_serial OUT NUMBER,
sql_exec_start OUT DATE,
sql_exec_id OUT NUMBER,
sql_plan_hash_value OUT NUMBER,
start_time_filter OUT DATE,
end_time_filter OUT DATE,
inst_id OUT NUMBER,
instance_id_filter OUT NUMBER,
parallel_filter OUT VARCHAR2,
plan_line_filter OUT NUMBER,
event_detail OUT VARCHAR2,
bucket_max_count OUT NUMBER,
bucket_interval OUT NUMBER,
report_level OUT VARCHAR2,
auto_refresh OUT NUMBER,
report_base_path OUT VARCHAR2,
active_since_sec OUT NUMBER,
active_since_date OUT DATE,
active_before_date OUT DATE,
last_refresh_time OUT DATE,
dbop_name OUT VARCHAR2,
dbop_exec_id OUT NUMBER,
max_sqltext_length OUT NUMBER,
top_n_count OUT NUMBER,
top_n_rankby OUT VARCHAR2,
con_name OUT VARCHAR2,
top_n_detail_count OUT NUMBER,
report_id OUT NUMBER);
dbms_sqltune_internal.test_process_sqlset(
sqlset_name IN VARCHAR2,
wrkspc_name IN VARCHAR2,
exec_userid IN NUMBER,
action IN VARCHAR2,
time_limit IN BINARY_INTEGER,
ctrl_options IN CLOB,
basic_filter IN VARCHAR2,
rank IN VARCHAR2,
all_sql IN BOOLEAN,
commit_rows IN BINARY_INTEGER,
sqlset_owner IN VARCHAR2,
error_code OUT NUMBER,
error_message OUT VARCHAR2);