Create_tuning_task
is an overloaded function for creating tuning tasks via a SQL
statement's text, a SQL statement's cursor cache identifier, an AWR
range of snapshot identifiers, or a SQL Tuning Set. In all cases, it
returns the new tuning task name as a VARCHAR2.
Create_tuning_task based upon a SQL statement's text
is as follows:
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SQL_TEXT
|
CLOB
|
IN
|
|
|
BIND_LIST
|
SQLBinds
|
IN
|
NULL
|
|
USER_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
SCOPE
|
VARCHAR2
|
IN
|
SCOPE_COMPREHENSIVE
|
|
TIME_LIMIT
|
NUMBER
|
IN
|
TIME_LIMIT_DEFAULT
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
DESCRIPTION
|
VARCHAR2
|
IN
|
NULL
|
Table 7.165:
Create_tuning_task Parameters Based on SQL Statement's Text
Create_tuning_task
based upon a SQL statement's cursor cache identifier is as follows:
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SQL_ID
|
VARCHAR2
|
IN
|
|
|
PLAN_HASH_VALUE
|
NUMBER
|
IN
|
NULL
|
|
SCOPE
|
VARCHAR2
|
IN
|
SCOPE_COMPREHENSIVE
|
|
TIME_LIMIT
|
NUMBER
|
IN
|
TIME_LIMIT_DEFAULT
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
DESCRIPTION
|
VARCHAR2
|
IN
|
NULL
|
Table 7.166:
Create_tuning_task Parameters Based on SQL Statement's Cursor
Cache Identifier
Create_tuning_task
based upon an AWR range of snapshot identifiers is as follows:
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
BEGIN_SNAP
|
NUMBER
|
IN
|
|
|
END_SNAP
|
NUMBER
|
IN
|
|
|
SQL_ID
|
VARCHAR2
|
IN
|
|
|
PLAN_HASH_VALUE
|
NUMBER
|
IN
|
NULL
|
|
SCOPE
|
VARCHAR2
|
IN
|
SCOPE_COMPREHENSIVE
|
|
TIME_LIMIT
|
NUMBER
|
IN
|
TIME_LIMIT_DEFAULT
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
DESCRIPTION
|
VARCHAR2
|
IN
|
NULL
|
Table 7.167:
Create_tuning_task Parameters Based on AWR range of snapshot
identifiers
Create_tuning_task
based upon an SQL Tuning Set (STS) is as follows:
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SQLSET_NAME
|
VARCHAR2
|
IN
|
|
|
BASIC_FILTER
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_FILTER
|
VARCHAR2
|
IN
|
NULL
|
|
RANK1
|
VARCHAR2
|
IN
|
NULL
|
|
RANK2
|
VARCHAR2
|
IN
|
NULL
|
|
RANK3
|
VARCHAR2
|
IN
|
NULL
|
|
RESULT_PERCENTAGE
|
NUMBER
|
IN
|
NULL
|
|
RESULT_LIMIT
|
NUMBER
|
IN
|
NULL
|
|
SCOPE
|
VARCHAR2
|
IN
|
SCOPE_COMPREHENSIVE
|
|
TIME_LIMIT
|
NUMBER
|
IN
|
TIME_LIMIT_DEFAULT
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
DESCRIPTION
|
VARCHAR2
|
IN
|
NULL
|
|
PLAN_FILTER
|
VARCHAR2
|
IN
|
'MAX_ELAPSED_TIME'
|
|
SQLSET_OWNER
|
VARCHAR2
|
IN
|
NULL
|
Table 7.168:
Create_tuning_task Parameters Based on STS
Delete_sqlset
is a procedure that deletes or drops the SQL statements from an STS.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SQLSET_NAME
|
VARCHAR2
|
IN
|
|
|
BASIC_FILTER
|
VARCHAR2
|
IN
|
NULL
|
|
SQLSET_OWNER
|
VARCHAR2
|
IN
|
NULL
|
Table 7.169:
Delete_sqlset Parameters
Drop_sql_profile
is a procedure that drops
the user specified SQL Profile.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
NAME
|
VARCHAR2
|
IN
|
|
|
IGNORE
|
BOOLEAN
|
IN
|
FALSE
|
Table 7.170:
Drop_sql_profile Parameters
Drop_sqlset
is a procedure that deletes the user specified STS and it must be
inactive.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SQLSET_NAME
|
VARCHAR2
|
IN
|
|
|
SQLSET_OWNER
|
VARCHAR2
|
IN
|
NULL
|
Table 7.171:
Drop_sqlset Parameters
Drop_tuning_task
is a procedure that deletes the user specified SQL tuning task. The
task and all intermediate data are deleted.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
Table 7.172:
Drop_tuning_task Parameters
Execute_tuning_task
is both a procedure and function that executes a pre-existing SQL
tuning task. As a function, it returns the new task's name as a
VARCHAR2.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
EXECUTION_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
EXECUTION_PARAMS
|
DBMS_ADVISOR.ArgList
|
IN
|
NULL
|
|
EXECUTION_DESC
|
VARCHAR2
|
IN
|
NULL
|
Table 7.173:
Execute_tuning_task Parameters
Implement_tuning_task
is a procedure that implements the SQL Profile recommendations made by
the SQL Tuning Advisor.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
REC_TYPE
|
VARCHAR2
|
IN
|
REC_TYPE_SQL_PROFILES
|
|
ONWER_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
EXECUTION_NAME
|
VARCHAR2
|
IN
|
NULL
|
Table 7.174:
Implement_tuning_task Parameters
Interrupt_tuning_task
is a procedure that interrupts the currently running tuning task but
does so as a normal type exit, and thus leaves intermediate results
for review.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
Table 7.175:
Interrupt_tuning_task Parameters
and include the URL for the page.
