Create_object
is an overloaded procedure where parameter
ATTR5
is optional, which is not required in one version of the call, and
creates a new task object. These are generally used as input data
for advisors.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
OBJECT_TYPE
|
VARCHAR2
|
IN
|
|
|
ATTR1
|
VARCHAR2
|
IN
|
NULL
|
|
ATTR2
|
VARCHAR2
|
IN
|
NULL
|
|
ATTR3
|
VARCHAR2
|
IN
|
NULL
|
|
ATTR4
|
CLOB
|
IN
|
NULL
|
|
{ ATTR5 }
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_ID
|
NUMBER
|
OUT
|
|
Table 7.42:
Create_object Parameters
Note that the various attribute parameters have different permissible
values depending upon the object type as shown in Table 7.43 below.
|
OBJECT_TYPE
|
ATTR1
|
ATTR2
|
ATTR3
|
ATTR4
|
|
TABLESPACE
|
Tablespace Name
|
NULL
|
NULL
|
NULL
|
|
TABLE
|
Schema Name
|
Table Name
|
NULL
|
NULL
|
|
INDEX
|
Schema Name
|
Index Name
|
NULL
|
NULL
|
|
TABLE PARTITION
|
Schema Name
|
Table Name
|
Partition Name
|
NULL
|
|
INDEX PARTITION
|
Schema Name
|
Index Name
|
Subpartition Name
|
NULL
|
|
TABLE SUBPARTITION
|
Schema Name
|
Table Name
|
Partition Name
|
NULL
|
|
INDEX SUBPARTITION
|
Schema Name
|
Index Name
|
Subpartition Name
|
NULL
|
|
LOB
|
Schema Name
|
Segment Name
|
NULL
|
NULL
|
|
LOB PARTITION
|
Schema Name
|
Segment Name
|
Partition Name
|
NULL
|
|
LOB SUBPARTITION
|
Schema Name
|
Segment Name
|
Subpartition Name
|
NULL
|
Table 7.43:
Advisor Object Types Attributes
Create_task
is an overloaded procedure where parameter
task_id
is optional, i.e. not required in one version of the call and creates
a new advisor task. The predefined templates constants are
SQLACESS_OLTP, SQLACCESS_WAREHOUSE and SQLACCESS_GENERAL.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
ADVISOR_NAME
|
VARCHAR2
|
IN
|
|
|
{ TASK_ID }
|
NUMBER
|
OUT
|
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
TASK_DESC
|
VARCHAR2
|
IN
|
NULL
|
|
TEMPLATE
|
VARCHAR2
|
IN
|
NULL
|
|
IS_TEMPLATE
|
VARCHAR2
|
IN
|
'FALSE'
|
|
HOW_CREATED
|
VARCHAR2
|
IN
|
NULL
|
Table 7.44:
Create_task Parameters
Delete_sts_ref
is a procedure that drops the link between the current SQL Advisor
task and a SQL Tuning Set. The
sts_name
parameter supports wildcarding.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
STS_OWNER
|
VARCHAR2
|
IN
|
|
|
STS_NAME
|
VARCHAR2
|
IN
|
|
Table 7.45:
Delete_sts_ref Parameters
Delete_task
is a procedure that drops an existing advisor task. The
task_name
parameter supports wildcarding.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
Table 7.46:
Delete_task Parameters
Execute_task
is an overloaded procedure that executes the advisor analysis for the
named task. Note that task execution is synchronous action, so control
is not returned until it is completed. The short form of
execute_task
takes the following single parameter:
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
Table 7.47:
Execute_task Parameter, Short Form
Whereas the longer version of
execute_task
procedure accepts all of the following parameters:
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
EXECUTION_TYPE
|
VARCHAR2
|
IN
|
NULL
|
|
EXECUTION_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
EXECUTION_PARAMS
|
dbms_advisor.argList
|
IN
|
NULL
|
|
EXECUTION_DESC
|
VARCHAR2
|
IN
|
NULL
|
Table 7.48:
Execute_task Parameters, Long Form
Where the data type for
dbms_advisor.orgList
is as follows:
TYPE argList IS TABLE OF
sys.wri$_adv_parameters.value%TYPE;
CREATE TABLE SYS.WRI$_ADV_PARAMETERS
(
TASK_ID
NUMBER
NOT NULL,
NAME
VARCHAR2(30 BYTE)
NOT NULL,
VALUE
VARCHAR2(4000 BYTE)
NOT NULL,
DATATYPE
NUMBER
NOT NULL,
FLAGS
NUMBER
NOT NULL,
DESCRIPTION
VARCHAR2(9 BYTE)
);
Get_task_report
is a function that generates and returns a report for the specified
task. The only valid report type is TEXT, and the only valid levels
are BASIC, TYPICAL and ALL. It returns that report as a CLOB.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
TYPE
|
VARCHAR2
|
IN
|
'TEXT'
|
|
LEVEL
|
VARCHAR2
|
IN
|
'TYPICAL'
|
|
SECTION
|
VARCHAR2
|
IN
|
'ALL'
|
|
OWNER_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
EXECUTION_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_ID
|
NUMBER
|
IN
|
NULL
|
Table 7.49:
Get_task_report Parameters
Get_task_script
is a function that generates and returns a SQL*Plus compatible script
for the specified task. The only valid script types are UNDO and
IMPLEMENTATION. For the REC_ID and SCT_ID, a zero or ADVISOR_ALL means
all recommendations and actions should be generated. It returns that
script as a CLOB.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
TYPE
|
VARCHAR2
|
IN
|
'IMPLEMENTATION
|
|
REC_ID
|
NUMBER
|
IN
|
NULL
|
|
ACT_ID
|
NUMBER
|
IN
|
NULL
|
|
OWNER_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
EXECUTION_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_ID
|
NUMBER
|
IN
|
NULL
|
Table 7.50:
Get_task_script Parameters
Implement_task
is a procedure that simply and directly implements the recommendations
for the specified task.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
REC_ID
|
NUMBER
|
IN
|
NULL
|
|
EXIT_ON_ERROR
|
BOOLEAN
|
IN
|
NULL
|
Table 7.51:
Implement_task Parameters
Interrupt_task
is a procedure that performs a graceful termination, or a normal exit,
of the currently executing task.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
Table 7.52:
Interrupt_task Parameters
Mark_recommendation
is a procedure that marks a recommendation for possible implementation
or import. The possible actions are ACCEPT< IGNORE and REJECT.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
TASK_NAME
|
VARCHAR2
|
IN
|
|
|
ID
|
NUMBER
|
IN
|
|
|
ACTION
|
VARCHAR2
|
IN
|
|
Table 7.53:
Mark_recommendation Parameters
and include the URL for the page.
