DBMS_ADVISOR is part of the server manageability suite of advisors, a set of expert systems that identifies and helps resolve performance problems relating to database server components.
Some advisors have their own packages. For these advisors, Oracle recommends that you use the advisor-specific package rather than DBMS_ADVISOR. Each of the following advisors has its own package, tailored to its specific functionality:
Automatic Database Diagnostic Monitor (DBMS_ADDM)
SQL Performance Analyzer (DBMS_SQLPA)
SQL Repair Advisor (DBMS_SQLDIAG)
SQL Tuning Advisor (DBMS_SQLTUNE)
Compression Advisor (DBMS_COMPRESSION.GET_COMPRESSION_RATIO)
SQL Access Advisor and Segment Advisor are the only advisors with common use cases for DBMS_ADVISOR. Undo Advisor and Compression Advisor do not support DBMS_ADVISOR subprograms.
See Also:
Oracle Database Administrator's Guide to learn about Segment Advisor
Oracle Database 2 Day + Performance Tuning Guide for information regarding how to use various advisors in Enterprise Manager
Oracle Database Performance Tuning Guide for information regarding SQL Tuning Advisor and SQL Access Advisor
Oracle Database 2 Day DBA and Oracle Database Administrator's Guide to learn about Undo Advisor
This chapter contains the following topics:
Deprecated Subprograms
Security Model
This section contains topics which relate to using the DBMS_ADVISOR package.
Table 18-1 summarizes the subprograms in this package. The Used in column lists advisors relevant for each subprogram, but excludes ADDM, SQL Performance Analyzer, SQL Repair Advisor, and SQL Tuning Advisor because these advisors have their own packages.
Table 18-1 DBMS_ADVISOR Package Subprograms
| Subprogram | Description | Used in |
|---|---|---|
|
Adds a workload reference to an Advisor task (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Adds a single statement to a workload |
SQL Access Advisor |
|
|
Establishes a link between the current SQL Access Advisor task and a SQL tuning set |
SQL Access Advisor |
|
|
Cancels a currently executing task operation |
Segment Advisor, SQL Access Advisor |
|
|
Copies the contents of a SQL workload object to a SQL tuning set |
SQL Access Advisor |
|
|
Creates an external file from a PL/SQL CLOB variable, which is useful for creating scripts and reports |
SQL Access Advisor |
|
|
Creates a new task object |
Segment Advisor |
|
|
Creates a new workload object (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Creates a new Advisor task in the repository |
Segment Advisor, SQL Access Advisor |
|
|
Deletes an entire workload object (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Deletes an entire workload object (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Deletes one or more statements from a workload (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Removes a link between the current SQL Access Advisor task and a SQL tuning set object |
SQL Access Advisor |
|
|
Deletes the specified task from the repository |
SQL Access Advisor |
|
|
Executes the specified task |
Segment Advisor, SQL Access Advisor |
|
|
Retrieves specific recommendation attributes from a task |
SQL Access Advisor |
|
|
Creates and returns a report for the specified task |
||
|
Creates and returns an executable SQL script of the Advisor task's recommendations in a buffer |
SQL Access Advisor |
|
|
Implements the recommendations for a task |
SQL Access Advisor |
|
|
Imports data into a workload from the current SQL cache (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Imports data into a workload from the current SQL cache (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Imports data from a SQL tuning set into a SQL workload data object (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Imports data into a workload from the current SQL cache (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Imports data into a workload from the current SQL cache (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Stops a currently executing task, ending its operations as it would at a normal exit, so that the recommendations are visible |
Segment Advisor, SQL Access Advisor |
|
|
Sets the |
Segment Advisor, SQL Access Advisor |
|
|
Performs an analysis on a single SQL statement |
SQL Access Advisor |
|
|
Resets a workload to its initial starting point (Caution: Deprecated Subprogram) |
SQL Access Advisor |
|
|
Resets a task to its initial state |
Segment Advisor, SQL Access Advisor |
|
|
Imports data into a workload from schema evidence |
SQL Access Advisor |
|
|
Modifies a default task parameter |
Segment Advisor, SQL Access Advisor |
|
|
Sets the value of a workload parameter |
SQL Access Advisor |
|
|
Sets the specified task parameter value |
Segment Advisor, SQL Access Advisor |
|
|
Shows how to decompose a materialized view into two or more materialized views or to restate the materialized view in a way that is more advantageous for fast refresh and query rewrite |
SQL Access Advisor |
|
|
Updates a task object |
Segment Advisor |
|
|
Updates an existing recommendation for the specified task |
SQL Access Advisor |
|
|
Updates a workload object |
SQL Access Advisor |
|
|
Updates one or more SQL statements in a workload |
SQL Access Advisor |
|
|
Updates a task's attributes |
Segment Advisor, SQL Access Advisor |
Note:
This procedure is deprecated in Release 11gR1.This procedure establishes a link between the current SQL Access Advisor task and a SQL Workload object. The link allows an advisor task to access interesting data for doing an analysis. The link also provides a stable view of the data. Once a connection between a SQL Access Advisor task and a SQL Workload object is made, the workload is protected from removal or modification.
Users should use ADD_STS_REF instead of ADD_SQLWKLD_REF for all SQL tuning set-based advisor runs. This function is only provided for backward compatibility.
DBMS_ADVISOR.ADD_SQLWKLD_REF ( task_name IN VARCHAR2, workload_name IN VARCHAR2, is_sts IN NUMBER :=0);
Table 18-2 ADD_SQLWKLD_REF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The SQL Access Advisor task name that uniquely identifies an existing task. |
|
|
The name of the workload object to be linked. Once a object has been linked to a task, it becomes read-only and cannot be deleted. There is no limit to the number of links to workload objects. To remove the link to the workload object, use the procedure |
|
|
Indicates the type of workload source. Possible values are:
|
Note:
This procedure is deprecated in Release 11gR1.This procedure adds a single statement to the specified workload.
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, module IN VARCHAR2, action IN VARCHAR2, cpu_time IN NUMBER := 0, elapsed_time IN NUMBER := 0, disk_reads IN NUMBER := 0, buffer_gets IN NUMBER := 0, rows_processed IN NUMBER := 0, optimizer_cost IN NUMBER := 0, executions IN NUMBER := 1, priority IN NUMBER := 2, last_execution_date IN DATE := 'SYSDATE', stat_period IN NUMBER := 0, username IN VARCHAR2, sql_text IN CLOB);
Table 18-3 ADD_SQLWKLD_STATEMENT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload name that uniquely identifies an existing workload. |
|
|
An optional business application module that will be associated with the SQL statement. |
|
|
An optional application action that will be associated with the SQL statement. |
|
|
The total CPU time in seconds that is consumed by the SQL statement. |
|
|
The total elapsed time in seconds that is consumed by the SQL statement. |
|
|
The total disk-read operations that are consumed by the SQL statement. |
|
|
The total buffer-get operations that are consumed by the SQL statement. |
|
|
The average number of rows processed by the SQL statement. |
|
|
The optimizer's calculated cost value. |
|
|
The total execution count by the SQL statement. This value should be greater than zero. |
|
|
The relative priority of the SQL statement. The value must be one of the following: 1- |
|
|
The date and time at which the SQL statement last executed. If the value is |
|
|
Time interval in seconds from which statement statistics were calculated. |
|
|
The Oracle user name that executed the SQL statement. Because a username is an Oracle identifier, the username value must be entered exactly as it is stored in the server. For example, if the user |
|
|
The complete SQL statement. To increase the quality of a recommendation, the SQL statement should not contain bind variables. |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See RESET_TASK Procedure for directions on setting a task to its initial state.
The ADD_SQLWKLD_STATEMENT procedure accepts several parameters that may be ignored by the caller. cpu_time, elapsed_time, disk_reads, buffer_gets, and optimizer_cost are only used to sort workload data when actual analysis occurs, so actual values are only necessary when the order_list task parameter references a particular statistic.To determine what statistics to provide when adding a new SQL statement to a workload, examine or set the task parameter order_list. The order_list parameter accepts any combination of the keys: buffer_gets, optimizer_cost, cpu_time, disk_reads, elapsed_time, executions, and priority. A typical setting of priority, optimizer_cost would indicate the SQL Access Advisor will sort the workload data by priority and optimizer_cost and process the highest cost statements first. Any statements added to the workload would need to include appropriate priority and optimizer_cost values. All other statistics can be defaulted or set to zero.For the statistical keys referenced by the order_list task parameter, the actual parameter values should be reasonably accurate since they will be compared to other statements in the workload. If the caller is unable to estimate values, choose values that would determine its importance relative to other statements in the workload. For example, if the current statement is considered the most critical query in your business, then an appropriate value would be anything greater than all other values for the same statistic found in the workload.
DECLARE
workload_name VARCHAR2(30);
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold) FROM sh.sales');
END;
/
This procedure establishes a link between the current SQL Access Advisor task and a SQL tuning set. The link enables an advisor task to access data for the purpose of doing an analysis. The link also provides a stable view of the data. Once a connection between a SQL Access Advisor task and a SQL tuning set is made, the STS is protected from removal or modification.
Use ADD_STS_REF for any STS-based advisor runs. The older method of using ADD_SQLWKLD_REF with parameter IS_STS=1 is only supported for backward compatibility. Furthermore, the ADD_STS_REF function accepts a SQL tuning set owner name, whereas ADD_SQLWKLD_REF does not.
DBMS_ADVISOR.ADD_STS_REF( task_name IN VARCHAR2 NOT NULL, sts_owner IN VARCHAR2, workload_name IN VARCHAR2 NOT NULL);
Table 18-4 ADD_STS_REF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The SQL Access Advisor task name that uniquely identifies an existing task. |
|
|
The owner of the SQL tuning set. The value of this parameter may be |
|
|
The name of the workload to be linked. A workload consists of one or more SQL statements, plus statistics and attributes that fully describe each statement. The database stores a workload as a SQL tuning set. After a workload has been linked to a task, it becomes read-only and cannot be deleted. There is no limit to the number of links to workloads. To remove the link to the workload, use the procedure |
This procedure causes a currently executing operation to terminate. This call does a soft interrupt. It will not break into a low-level database access call like a hard interrupt such as Ctrl-C. The SQL Access Advisor periodically checks for soft interrupts and acts appropriately. As a result, this operation may take a few seconds to respond to a call.
A cancel command restores the task to its condition prior to the start of the cancelled operation. Therefore, a cancelled task or data object cannot be resumed.
Because all Advisor task procedures are synchronous, to cancel an operation, you must use a separate database session.
This procedure copies the contents of a SQL workload object to a SQL tuning set.
To use this procedure, the caller must have privileges to create and modify a SQL tuning set.
DBMS_ADVISOR.COPY_SQLWKLD_TO_STS ( workload_name IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW');
Table 18-6 COPY_SQLWKLD_TO_STS Procedure Parameter
| Parameter | Description |
|---|---|
|
|
The SQL Workload object name to copy. |
|
|
The SQL tuning set name into which the SQL Workload object will be copied. |
|
|
Specifies the handling of the target SQL tuning set. Possible values are:
In all cases, if the specified SQL tuning set does not exist, it will be created. |
To use this procedure, the caller must have privileges to create and modify a SQL tuning set.
This procedure creates an external file from a PL/SQL CLOB variable, which is used for creating scripts and reports. CREATE_FILE accepts a CLOB input parameter and writes the character string contents to the specified file.
Table 18-7 CREATE_FILE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
A |
|
|
Specifies the directory that will contain the new file. You must use the directory alias as defined by the |
|
|
Specifies the output file to receive the script commands. The filename can only contain the name and an optional file type of the form |
All formatting must be embedded within the CLOB.
The Oracle server restricts file access within Oracle Stored Procedures. This means that file locations and names must adhere to the known file permissions in the server.
CREATE DIRECTORY MY_DIR as '/homedir/user4/gssmith';
GRANT READ,WRITE ON DIRECTORY MY_DIR TO PUBLIC;
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
workload_name VARCHAR2(30);
BEGIN
task_name := 'My Task';
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales');
DBMS_ADVISOR.EXECUTE_TASK(task_name);
DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(task_name),
'MY_DIR','script.sql');
END;
/
This procedure creates a new task object.
DBMS_ADVISOR.CREATE_OBJECT ( task_name IN VARCHAR2, object_type IN VARCHAR2, attr1 IN VARCHAR2 := NULL, attr2 IN VARCHAR2 := NULL, attr3 IN VARCHAR2 := NULL, attr4 IN CLOB := NULL, object_id OUT NUMBER); DBMS_ADVISOR.CREATE_OBJECT ( task_name IN VARCHAR2, object_type IN VARCHAR2, attr1 IN VARCHAR2 := NULL, attr2 IN VARCHAR2 := NULL, attr3 IN VARCHAR2 := NULL, attr4 IN CLOB := NULL, attr5 IN VARCHAR2 := NULL, object_id OUT NUMBER);
Table 18-8 CREATE_OBJECT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
A valid Advisor task name that uniquely identifies an existing task. |
|
|
Specifies the external object type. |
|
|
Advisor-specific data. |
|
|
Advisor-specific data. |
|
|
Advisor-specific data. |
|
|
Advisor-specific data. |
|
|
Advisor-specific data. |
|
|
The advisor-assigned object identifier. |
The attribute parameters have different values depending upon the object type. See Oracle Database Administrator's Guide for details regarding these parameters and object types.
Task objects are typically used as input data for a particular advisor. Segment advice can be generated at the object, segment, or tablespace level. If for the object level, advice is generated on all partitions of the object (if the object is partitioned). The advice is not cascaded to any dependent objects. If for the segment level, advice can be obtained on a single segment, such as the partition or subpartition of a table, index, or LOB column. If for a tablespace level, target advice for every segment in the tablespace will be generated.
See Oracle Database Administrator's Guide for further information regarding the Segment Advisor.
Note:
This procedure is deprecated in Release 11gR1.This procedure creates a new private SQL Workload object for the user. A SQL Workload object manages a SQL workload on behalf of the SQL Access Advisor. A SQL Workload object must exist prior to performing any other SQL Workload operations, such as importing or updating SQL statements.
DBMS_ADVISOR.CREATE_SQLWKLD ( workload_name IN OUT VARCHAR2, description IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE');
Table 18-9 CREATE_SQLWKLD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
A name that uniquely identifies the created workload. If not specified, the system will generate a unique name. Names can be up to 30 characters long. |
|
|
Specifies an optional workload description. Descriptions can be up to 256 characters. |
|
|
An optional SQL Workload name of an existing workload data object or data object template. |
|
|
An optional value that enables you to set the newly created workload as a template. Valid values are |
The SQL Access Advisor returns a unique workload object identifier number that must be used for subsequent activities within the new SQL Workload object.
By default, workload objects are created using built-in default settings. To create a workload using the parameter settings of an existing workload or workload template, the user may specify an existing workload name.
After a SQL Workload object is present, it can then be referenced by one or more SQL Access Advisor tasks using the ADD_SQLWKLD_REF procedure.
This procedure creates a new Advisor task in the repository.
DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_id OUT NUMBER, task_name IN OUT VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL); DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL); DBMS_ADVISOR.CREATE_TASK ( parent_task_name IN VARCHAR2, rec_id IN NUMBER, task_id OUT NUMBER, task_name IN OUT VARCHAR2, task_desc IN VARCHAR2, template IN VARCHAR2);
Table 18-10 CREATE_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the unique advisor name as defined in the view |
|
|
A number that uniquely identifies the created task. The number is generated by the procedure and returned to the user. |
|
|
Specifies a new task name. Names must be unique among all tasks for the user. When using the second form of the |
|
|
Specifies an optional task description. Descriptions can be up to 256 characters in length. |
|
|
An optional task name of an existing task or task template. To specify built-in SQL Access Advisor templates, use the template name as described earlier. |
|
|
An optional value that allows the user to set the newly created task as template. Valid values are: |
|
|
An optional value that identifies how the source was created. |
A task must be associated with an advisor, and once the task has been created, it is permanently associated with the original advisor. By default, tasks are created using built-in default settings. To create a task using the parameter settings of an existing task or task template, the user may specify an existing task name.
For the SQL Access Advisor, use the identifier DBMS_ADVISOR.SQLACCESS_ADVISOR as the advisor_name.
The SQL Access Advisor provides three built-in task templates, using the following constants:
DBMS_ADVISOR.SQLACCESS_OLTP
Parameters are preset to favor an OLTP application environment.
DBMS_ADVISOR.SQLACCESS_WAREHOUSE
Parameters are preset to favor a data warehouse application environment.
DBMS_ADVISOR.SQLACCESS_GENERAL
Parameters are preset to favor a hybrid application environment where both OLTP and data warehouse operations may occur. For the SQL Access Advisor, this is the default template.
Note:
This procedure is deprecated in Release 11gR1.This procedure deletes an existing SQL Workload object from the repository.
Table 18-11 DELETE_SQLWKLD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. The wildcard |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See the RESET_TASK Procedure to set a task to its initial state.
Note:
This procedure is deprecated in Release 11gR1.This procedure removes a link between the current SQL Access task and a SQL Workload data object.
Users should use DELETE_STS_REF instead of DELETE_SQLWKLD_REF for all SQL tuning set-based advisor runs. This function is only provided for backward compatibility.
DBMS_ADVISOR.DELETE_SQLWKLD_REF ( task_name IN VARCHAR2, workload_name IN VARCHAR2, is_sts IN NUMBER :=0);
Table 18-12 DELETE_SQLWKLD_REF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The SQL Access task name that uniquely identifies an existing task. |
|
|
The name of the workload object to be unlinked. The wildcard |
|
|
Indicates the type of workload source. Possible values are:
|
DECLARE task_id NUMBER; task_name VARCHAR2(30); workload_name VARCHAR2(30); BEGIN task_name := 'My Task'; workload_name := 'My Workload'; DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name); DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name); DBMS_ADVISOR.DELETE_SQLWKLD_REF(task_name, workload_name); END; /
This procedure has been deprecated.
This procedure deletes one or more statements from a workload.
DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, sql_id IN NUMBER); DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, search IN VARCHAR2, deleted OUT NUMBER);
Table 18-13 DELETE_SQLWKLD_STATEMENT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. |
|
|
The Advisor-generated identifier number that is assigned to the statement. To specify all workload statements, use the constant |
|
|
Disabled. |
|
|
Returns the number of statements deleted by the searched deleted operation. |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See the RESET_TASK Procedure to set a task to its initial state.
DECLARE
workload_name VARCHAR2(30);
deleted NUMBER;
id NUMBER;
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'YEARLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales');
SELECT sql_id INTO id FROM USER_ADVISOR_SQLW_STMTS
WHERE workload_name = 'My Workload';
DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT(workload_name, id);
END;
/
This procedure removes a link between the current SQL Access Advisor task and a SQL tuning set.Use DELETE_STS_REF for any STS-based advisor runs. The older method of using DELETE_SQLWKLD_REF with parameter IS_STS=1 is only supported for backward compatibility. Furthermore, the DELETE_STS_REF function accepts an STS owner name, whereas DELETE_SQLWKLD_REF does not.
DBMS_ADVISOR.DELETE_STS_REF ( task_name IN VARCHAR2 NOT NULL, sts_owner IN VARCHAR2, workload_name IN VARCHAR2 NOT NULL);
Table 18-14 DELETE_STS_REF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The SQL Access Advisor task name that uniquely identifies an existing task. |
|
|
The owner of the SQL tuning set. The value of this parameter may be |
|
|
The name of the workload to be unlinked. A workload consists of one or more SQL statements, plus statistics and attributes that fully describe each statement. The database stores a workload as a SQL tuning set. The wildcard |
This procedure deletes an existing task from the repository.
Table 18-15 DELETE_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
A single Advisor task name that will be deleted from the repository. The wildcard If a wildcard is provided, the |
This procedure performs the Advisor analysis or evaluation for the specified task. The procedure is overloaded.
The execution-related arguments are optional and you do not need to set them for advisors that do not allow their tasks to be executed multiple times.
Advisors can execute a task multiple times and use the results for further processing and analysis.
DBMS_ADVISOR.EXECUTE_TASK ( task_name IN VARCHAR2); DBMS_ADVISOR.EXECUTE_TASK ( task_name IN VARCHAR2, execution_type IN VARCHAR2 := NULL, execution_name IN VARCHAR2 := NULL, execution_params IN dbms_advisor.argList := NULL, execution_desc IN VARCHAR2 := NULL, RETURN VARCHAR2;
Table 18-16 EXECUTE_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The task name that uniquely identifies an existing task. |
|
|
The type of action to be performed by the function. If As an example, the SQL Performance Analyzer accepts the following possible values:
|
|
|
A name to qualify and identify an execution. If not specified, it will be generated by the Advisor and returned by function. |
|
|
A list of parameters (name, value) for the specified execution. Note that execution parameters are real task parameters, but they affect only the execution they are specified for. As an example, consider the following:
DBMS_ADVISOR.ARGLIST('time_limit', 12, 'username', 'foo')
|
|
|
A 256-length string describing the execution. |
Task execution is a synchronous operation. Control will not be returned to the caller until the operation has completed, or a user-interrupt was detected.
Upon return, you can check the DBA_ADVISOR_LOG table for the execution status.
DECLARE task_id NUMBER; task_name VARCHAR2(30); workload_name VARCHAR2(30); BEGIN task_name := 'My Task'; workload_name := 'My Workload'; DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name); DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name); DBMS_ADVISOR.EXECUTE_TASK(task_name); END; /
This procedure retrieves a specified attribute of a new object as recommended by Advisor analysis.
DBMS_ADVISOR.GET_REC_ATTRIBUTES ( workload_name IN VARCHAR2, rec_id IN NUMBER, action_id IN NUMBER, attribute_name IN VARCHAR2, value OUT VARCHAR2, owner_name IN VARCHAR2 := NULL);
Table 18-17 GET_REC_ATTRIBUTES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The task name that uniquely identifies an existing task. |
|
|
The Advisor-generated identifier number that is assigned to the recommendation. |
|
|
The Advisor-generated action identifier that is assigned to the particular command. |
|
|
Specifies the attribute to change. |
|
|
The buffer to receive the requested attribute value. |
|
|
Optional owner name of the target task. This permits access to task data not owned by the current user. |
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
workload_name VARCHAR2(30);
attribute VARCHAR2(100);
BEGIN
task_name := 'My Task';
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales WHERE promo_id = 10');
DBMS_ADVISOR.EXECUTE_TASK(task_name);
DBMS_ADVISOR.GET_REC_ATTRIBUTES(task_name, 1, 1, 'NAME', attribute);
END;
/
This function creates and returns a report for the specified task.
DBMS_ADVISOR.GET_TASK_REPORT ( task_name IN VARCHAR2, type IN VARCHAR2 := 'TEXT', level IN VARCHAR2 := 'TYPICAL', section IN VARCHAR2 := 'ALL', owner_name IN VARCHAR2 := NULL, execution_name IN VARCHAR2 := NULL, object_id IN NUMBER := NULL) RETURN CLOB;
Table 18-18 GET_TASK_REPORT Function Parameters
| Parameter | Description |
|---|---|
|
|
The name of the task from which the script will be created. |
|
|
The only valid value is |
|
|
The possible values are |
|
|
Advisor-specific report sections. |
|
|
Owner of the task. If specified, the system will check to see if the current user has read privileges to the task data. |
|
|
An identifier of a specific execution of the task. It is needed only for advisors that allow their tasks to be executed multiple times. |
|
|
An identifier of an advisor object that can be targeted by the script. |
This function creates a SQL*Plus-compatible SQL script and sends the output to file. The script will contain all of the accepted recommendations from the specified task.
DBMS_ADVISOR.GET_TASK_SCRIPT ( task_name IN VARCHAR2 type IN VARCHAR2 := 'IMPLEMENTATION', rec_id IN NUMBER := NULL, act_id IN NUMBER := NULL, owner_name IN VARCHAR2 := NULL, execution_name IN VARCHAR2 := NULL, object_id IN NUMBER := NULL) RETURN CLOB;
Table 18-19 GET_TASK_SCRIPT Function Parameters
| Parameter | Description |
|---|---|
|
|
The task name that uniquely identifies an existing task. |
|
|
Specifies the type of script to generate. The possible values are |
|
|
An optional recommendation identifier number that can be used to extract a subset of the implementation script. A zero or the value |
|
|
Optional action identifier number that can be used to extract a single action as a DDL command. A zero or the value |
|
|
An optional task owner name. |
|
|
An identifier of a specific execution of the task. It is needed only for advisors that allow their tasks to be executed multiple times. |
|
|
An identifier of an advisor object that can be targeted by the script. |
Though the script is ready to execute, Oracle recommends that the user review the script for acceptable locations for new materialized views and indexes.
For a recommendation to appear in a generated script, it must be marked as accepted.
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
workload_name VARCHAR2(30);
buf CLOB;
BEGIN
task_name := 'My Task';
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales');
DBMS_ADVISOR.EXECUTE_TASK(task_name);
buf := DBMS_ADVISOR.GET_TASK_SCRIPT(task_name);
END;
/
This procedure implements the recommendations of the specified task.
DBMS_ADVISOR.IMPLEMENT_TASK ( task_name IN VARCHAR2, rec_id IN NUMBER := NULL, exit_on_error IN BOOLEAN := NULL);
Note:
This procedure is deprecated in Release 11gR1.This procedure constructs and loads a SQL workload based on schema evidence. The workload is also referred to as a hypothetical workload.
DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
Table 18-21 IMPORT_SQLWKLD_SCHEMA Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. |
|
|
Specifies the action to be taken when storing the workload. Possible values are:
The default value is |
|
|
Specifies the application priority for each statement that is saved in the workload object. The value must be one of the following: 1- |
|
|
Returns the number or rows that were not saved due to syntax or validation errors |
|
|
Returns the number of rows actually saved in the repository. |
To successfully import a hypothetical workload, the target schemas must contain dimensions.
If the VALID_TABLE_LIST parameter is not set, the search space may become very large and require a significant amount of time to complete. Oracle recommends that you limit your search space to specific set of tables.
If a task contains valid recommendations from a prior run, adding or modifying task will mark the task as invalid, preventing the viewing and reporting of potentially valuable recommendation data.
DECLARE
workload_name VARCHAR2(30);
saved NUMBER;
failed NUMBER;
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(workload_name,'VALID_TABLE_LIST','SH.%');
DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA(workload_name, 'REPLACE', 1, saved,
failed);
END;
/
Note:
This procedure is deprecated in Release 11gR1.This procedure creates a SQL workload from the current contents of the server's SQL cache.
DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
Table 18-22 IMPORT_SQLWKLD_SQLCACHE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. |
|
|
Specifies the action to be taken when storing the workload. Possible values are:
The default value is |
|
|
Specifies the application priority for each statement that is saved in the workload object. The value must be one of the following 1- |
|
|
Returns the number of rows saved as output parameters. |
|
|
Returns the number of rows that were not saved due to syntax or validation errors. |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See RESET_TASK Procedure to set a task to its initial state.
DECLARE
workload_name VARCHAR2(30);
saved NUMBER;
failed NUMBER;
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(workload_name,'VALID_TABLE_LIST','SH.%');
DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(workload_name, 'REPLACE', 1, saved,
failed);
END;
/
Note:
This procedure is deprecated in Release 11gR1.This procedure loads a SQL workload from an existing SQL tuning set. A SQL tuning set is typically created from the server workload repository using various time and data filters.
DBMS_ADVISOR.IMPORT_SQLWKLD_STS ( workload_name IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
DBMS_ADVISOR.IMPORT_SQLWKLD_STS ( workload_name IN VARCHAR2, sts_owner IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
Table 18-23 IMPORT_SQLWKLD_STS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. |
|
|
The optional owner of the SQL tuning set. |
|
|
The name of an existing SQL tuning set workload from which the data will be imported. If the |
|
|
Specifies the action to be taken when storing the workload. Possible values are:
The default value is |
|
|
Specifies the application priority for each statement that is saved in the workload object. The value must be one of the following: 1- |
|
|
Returns the number of rows actually saved in the repository. |
|
|
Returns the number of rows that were not saved due to syntax or validation errors. |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See RESET_TASK Procedure to set a task to its initial state.
DECLARE
workload_name VARCHAR2(30);
saved NUMBER;
failed NUMBER;
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(workload_name,'VALID_TABLE_LIST','SH.%');
DBMS_ADVISOR.IMPORT_SQLWKLD_STS(workload_name, 'MY_SQLSET', 'REPLACE', 1,
saved, failed);
END;
/
Note:
This procedure is deprecated in Release 11gR1.This procedure collects a SQL workload from a Summary Advisor workload. This procedure is intended to assist Oracle9i Database Summary Advisor users in the migration to SQL Access Advisor.
DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, sumadv_id IN NUMBER, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
Table 18-24 IMPORT_SQLWKLD_SUMADV Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. |
|
|
Specifies the action to be taken when storing the workload. Possible values are:
The default value is |
|
|
Specifies the default application priority for each statement that is saved in the workload object. If a Summary Advisor workload statement contains a priority of zero, the default priority will be applied. If the workload statement contains a valid priority, then the Summary Advisor priority will be converted to a comparable SQL Access Advisor priority. The value must be one of the following: 1- |
|
|
Specifies the Summary Advisor workload identifier number. |
|
|
Returns the number of rows actually saved in the repository. |
|
|
Returns the number of rows that were not saved due to syntax or validation errors. |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See RESET_TASK Procedure to set a task to its initial state.
DECLARE
workload_name VARCHAR2(30);
saved NUMBER;
failed NUMBER;
sumadv_id NUMBER;
BEGIN
workload_name := 'My Workload';
sumadv_id := 394;
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(workload_name,'VALID_TABLE_LIST','SH.%');
DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV(workload_name, 'REPLACE', 1, sumadv_id,
saved, failed);
END;
/
Note:
This procedure is deprecated in Release 11gR1.This procedure collects a SQL workload from a specified user table.
DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', owner_name IN VARCHAR2, table_name IN VARCHAR2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
Table 18-25 IMPORT_SQLWKLD_USER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. |
|
|
Specifies the action to be taken when storing the workload. Possible values are:
The default value is |
|
|
Specifies the owner name of the table or view from which workload data will be collected. |
|
|
Specifies the name of the table or view from which workload data will be collected. |
|
|
Returns the number of rows actually saved in the workload object. |
|
|
Returns the number of rows that were not saved due to syntax or validation errors. |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See RESET_TASK Procedure to set a task to its initial state.
DECLARE
workload_name VARCHAR2(30);
saved NUMBER;
failed NUMBER;
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(workload_name,'VALID_TABLE_LIST','SH.%');
DBMS_ADVISOR.IMPORT_SQLWKLD_USER(workload_name, 'REPLACE', 'SH',
'USER_WORKLOAD', saved, failed);
END;
/
This procedure stops a currently executing task. The task will end its operations as it would at a normal exit. The user will be able to access any recommendations that exist to this point.
DECLARE task_id NUMBER; task_name VARCHAR2(30); BEGIN task_name := 'My Task'; DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name); DBMS_ADVISOR.EXECUTE_TASK(task_name); END; /
While this session is executing its task, you can interrupt the task from a second session using the following statement:
BEGIN
DBMS_ADVISOR.INTERRUPT_TASK('My Task');
END;
/
This procedure marks a recommendation for import or implementation.
Table 18-27 MARK_RECOMMENDATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task. |
|
|
The recommendation identifier number assigned by the Advisor. |
|
|
The recommendation action setting. The possible actions are:
|
For a recommendation to be implemented, it must be marked as accepted. By default, all recommendations are considered accepted and will appear in any generated scripts.
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
workload_name VARCHAR2(30);
attribute VARCHAR2(100);
rec_id NUMBER;
BEGIN
task_name := 'My Task';
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales WHERE promo_id = 10');
DBMS_ADVISOR.EXECUTE_TASK(task_name);
rec_id := 1;
DBMS_ADVISOR.MARK_RECOMMENDATION(task_name, rec_id, 'REJECT');
END;
/
This procedure performs an analysis and generates recommendations for a single SQL statement.
This provides a shortcut method of all necessary operations to analyze the specified SQL statement. The operation creates a task using the specified task name. The task will be created using a specified Advisor task template. Finally, the task will be executed and the results will be saved in the repository.
DBMS_ADVISOR.QUICK_TUNE ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, attr1 IN CLOB, attr2 IN VARCHAR2 := NULL, attr3 IN NUMBER := NULL, task_or_template IN VARCHAR2 := NULL);
Table 18-28 QUICK_TUNE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the Advisor that will perform the analysis. |
|
|
Name of the task. |
|
|
Advisor-specific attribute in the form of a |
|
|
Advisor-specific attribute in the form of a |
|
|
Advisor-specific attribute in the form of a |
|
|
An optional task name of an existing task or task template. |
If indicated by the user, the final recommendations can be implemented by the procedure.
The task will be created using either a specified SQL Access task template or the built-in default template of SQLACCESS_GENERAL. The workload will only contain the specified statement, and all task parameters will be defaulted.
attr1 must be the single SQL statement to tune. For the SQL Access Advisor, attr2 is the user who would execute the single statement. If omitted, the current user will be used.
Note:
This procedure is deprecated in Release 11gR1.This procedure resets a workload to its initial starting point. This has the effect of removing all journal messages, log messages, and recalculating necessary volatility and usage statistics.
RESET_SQLWKLD should be executed after any workload adjustments such as adding or removing SQL statements.
DECLARE
workload_name VARCHAR2(30);
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales WHERE promo_id = 10');
DBMS_ADVISOR.RESET_SQLWKLD(workload_name);
END;
/
This procedure resets a task to its initial state. All intermediate and recommendation data will be removed from the task. The task status will be set to INITIAL.
DECLARE task_id NUMBER; task_name VARCHAR2(30); workload_name VARCHAR2(30); BEGIN task_name := 'My Task'; workload_name := 'My Workload'; DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name); DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name); DBMS_ADVISOR.EXECUTE_TASK(task_name); DBMS_ADVISOR.RESET_TASK(task_name); END; /
Note:
This procedure is deprecated in Release 11gR1.This procedure modifies the default value for a user parameter within a SQL Workload object or SQL Workload object template. A user parameter is a simple variable that stores various attributes that affect workload collection, tuning decisions and reporting. When a default value is changed for a parameter, workload objects will inherit the new value when they are created.
DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER ( parameter IN VARCHAR2, value IN VARCHAR2); DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER ( parameter IN VARCHAR2, value IN NUMBER);
Table 18-31 SET_DEFAULT_SQLWKLD_PARAMETER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the data parameter to be modified. Parameter names are not case sensitive. Parameter names are unique to the workload object type, but not necessarily unique to all workload object types. Various object types may use the same parameter name for different purposes. |
|
|
The value of the specified parameter. The value can be specified as a string or a number. If the value is |
A parameter will only affect operations that modify the workload collection. Therefore, parameters should be set prior to importing or adding new SQL statements to a workload. If a parameter is set after data has been placed in a workload object, it will have no effect on the existing data.
This procedure modifies the default value for a user parameter within a task or a template. A user parameter is a simple variable that stores various attributes that affect various Advisor operations. When a default value is changed for a parameter, tasks will inherit the new value when they are created.
A default task is different from a regular task. The default value is the initial value that will be inserted into a newly created task, while setting a task parameter with SET_TASK_PARAMETER sets the local value only. Thus, SET_DEFAULT_TASK_PARAMETER has no effect on an existing task.
DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER ( advisor_name IN VARCHAR2 parameter IN VARCHAR2, value IN VARCHAR2); DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER ( advisor_name IN VARCHAR2 parameter IN VARCHAR2, value IN NUMBER);
Table 18-32 SET_DEFAULT_TASK_PARAMETER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the unique advisor name as defined in the view |
|
|
The name of the task parameter to be modified. Parameter names are not case sensitive. Parameter names are unique to the task type, but not necessarily unique to all task types. Various task types may use the same parameter name for different purposes. |
|
|
The value of the specified task parameter. The value can be specified as a string or a number. |
Note:
This procedure is deprecated in Release 11gR1.This procedure modifies a user parameter within a SQL Workload object or SQL Workload object template. A user parameter is a simple variable that stores various attributes that affect workload collection, tuning decisions and reporting.
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( workload_name IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2); DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( workload_name IN VARCHAR2, parameter IN VARCHAR2, value IN NUMBER);
Table 18-33 SET_SQLWKLD_PARAMETER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The SQL Workload object name that uniquely identifies an existing workload. |
|
|
The name of the data parameter to be modified. Parameter names are not case sensitive. |
|
|
The value of the specified parameter. The value can be specified as a string or a number. If the value is |
A parameter will only affect operations that modify the workload collection. Therefore, parameters should be set prior to importing or adding new SQL statements to a workload. If a parameter is set after data has been placed in a workload object, it will have no effect on the existing data.
This procedure modifies a user parameter within an Advisor task or a template. A user parameter is a simple variable that stores various attributes that affect workload collection, tuning decisions and reporting.
DBMS_ADVISOR.SET_TASK_PARAMETER ( task_name IN VARCHAR2 parameter IN VARCHAR2, value IN VARCHAR2); DBMS_ADVISOR.SET_TASK_PARAMETER ( task_name IN VARCHAR2 parameter IN VARCHAR2, value IN NUMBER);
Table 18-34 SET_TASK_PARAMETER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The Advisor task name that uniquely identifies an existing task. |
|
|
The name of the task parameter to be modified. Parameter names are not case sensitive. Parameter names are unique to the task type, but not necessarily unique to all task types. Various task types may use the same parameter name for different purposes. |
|
|
The value of the specified task parameter. The value can be specified as a string or a number. If the value is |
A task cannot be modified unless it is in its initial state. See RESET_TASK Procedure to set a task to its initial state. See your Advisor-specific documentation for further information on using this procedure.
SQL Access Advisor Task Parameters
Table 18-35 lists SQL Access Advisor task parameters.
Table 18-35 SQL Access Advisor Task Parameters
| Parameter | Description |
|---|---|
|
|
A comma-separated list that specifies the tuning artifacts to consider during analysis. The possible values are:
Using the new keywords, the following combinations are valid:
The default value is |
|
|
When set to |
|
|
Specifies the expiration time in days for the current SQL Access Advisor task. The value is relative to the last modification date. Once the task expires, it will become a candidate for removal by an automatic purge operation. Specifies the expiration time in days for the current Access Advisor task. The value is relative to the last modification date. The datatype is Once the task expires, it becomes a candidate for removal by an automatic purge operation. The possible values are:
The default value is 30. |
|
|
Contains the default task or template name from which the Enterprise Manager SQL Access Advisor Wizard reads its initial values. The default value is |
|
|
Specifies the default owner for new index recommendations. When a script is created, this value will be used to qualify the index name. Possible values are:
The default value is |
|
|
Specifies the default tablespace for new index recommendations. When a script is created, this value will be used to specify a tablespace clause. Possible values are:
The default value is |
|
|
Specifies the default owner for new materialized view recommendations. When a script is created, this value will be used to qualify the materialized view name. Possible values are:
The default value is |
|
|
Specifies the default tablespace for new materialized view recommendations. When a script is created, this value will be used to specify a tablespace clause. Possible values are
The default value is |
|
|
Specifies the default tablespace for new materialized view log recommendations. When a script is created, this value will be used to specify a tablespace clause. Possible values are:
The default value is |
|
|
Specifies the default tablespace for new partitioning recommendations. When a script is created, this value will be used to specify a tablespace clause. Possible values are:
The default value is |
|
|
When set to See the related parameter |
|
|
Specifies an end time for selecting SQL statements. If the statement did not execute on or before the specified time, it will not be processed. Each date must be in the standard Oracle form of MM-DD-YYYY HH24:MI:SS, where:
The datatype is |
|
|
This parameter is maintained for backward compatibility. All values will be translated and placed into the If set to Possible values are:
The default value is |
|
|
This parameter is maintained for backward compatibility. All values will be translated and placed into the The translated values are:
The type of recommendations that is desired. Possible values:
The default value is |
|
|
When performing an The possible values are:
The default value is |
|
|
Specifies the method by which new index names are formed. If the TASK_ID is omitted from the template, names generated by two concurrently executing SQL Access Advisor tasks may conflict and cause undesirable effects. So it is recommended that you include the TASK_ID in the template. Once formatted, the maximum size of a name is 30 characters. Valid keywords are:
The default template is < |
|
|
Contains a fully qualified list of actions that are not eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted names are supported. An action can be any string. If an action is not quoted, it will be changed to uppercase lettering and stripped of leading and trailing spaces. An action string is not scanned for correctness. During a task execution, if a SQL statement's action matches a name in the action list, it will not be processed by the task. An action name is case sensitive. The possible values are:
The default value is |
|
|
Contains a fully qualified list of modules that are not eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted names are supported. A module can be any string. If a module is not quoted, it will be changed to uppercase lettering and stripped of leading and trailing spaces. A module string is not scanned for correctness. During a task execution, if a SQL statement's module matches a name in the list, it will not be processed by the task. A module name is case sensitive. The possible values are:
The default value is |
|
|
Contains a fully qualified list of text strings that are not eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted values are supported. A SQL string can be any string. If a string is not quoted, it will be changed to uppercase lettering and stripped of leading and trailing spaces. A SQL string is not scanned for correctness. During a task execution, if a SQL statement contains a string in the SQL string list, it will not be processed by the task. The possible values are:
The default value is |
|
|
Contains a fully qualified list of usernames that are not eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted names are supported. During a task execution, if a SQL statement's username matches a name in the username list, it will not be processed by the task. A username is not case sensitive unless it is quoted. The possible values are:
The default value is |
|
|
Controls the logging of messages to the journal ( Possible values are:
Each journal value represents all recorded messages at that level or lower. For example, when choosing
The default value is |
|
|
User can suggest that the Partition Expert cut off the number of partitioning schemes to investigate. This can help with cutting down the run time of the advisor. Possible values are:
The default value is |
|
|
Limits the number of partitions the advisor will recommend for any base table, index, or materialized view. Possible values are:
The default value is |
|
|
Specifies the mode by which Access Advisor will operate during an analysis. Valid values are:
The default value is |
|
|
Specifies the method by which new materialized view names are formed. If the TASK_ID is omitted from the template, names generated by two concurrently executing SQL Access Advisor tasks may conflict and cause undesirable effects. So it is recommended that you include the TASK_ID in the template. The format is any combination of keyword tokens and literals. However, once formatted, the maximum size of a name is 30 characters. Valid tokens are:
The default template is: |
|
|
This parameter has been deprecated. Contains the primary natural order in which the Access Advisor processes workload elements during the analysis operation. To determine absolute natural order, Access Advisor sorts the workload using Possible values are:
All values are accessed in descending order, where a high value is considered more interesting than a low value. The default value is |
|
|
Specifies the method by which new partition names are formed. The format is any combination of keyword tokens and literals. However, once formatted, the maximum size of a name is 30 characters. Valid tokens are:
The default template is |
|
|
Specifies the approach used to make partitioning recommendations. One possible value is |
|
|
Specifies the type of partitioning used. Possible values are |
|
|
Contains the primary natural order in which the SQL Access Advisor processes workload elements during the analysis operation. To determine absolute natural order, SQL Access Advisor sorts the workload using Possible values are:
All values are accessed in descending order, where a high value is considered more interesting than a low value. The default value is |
|
|
When considering candidate materialized views, exact text match solutions will only be included if this parameter contains The possible values are:
The default value is |
|
|
Allows the SQL Access Advisor to recommend optimal tablespaces for any partitioning scheme. If this is not set, the SQL Access Advisor will simply recommend a partitioning method but give no advice on physical storage. Possible values are:
The datatype is |
|
|
Specifies whether materialized views are refreshed Possible values are:
The default value is |
|
|
This is the default date and time formatting template. The default format is |
|
|
Controls the display of The possible values are:
The default value is |
|
|
Specifies the number of SQL statements to be analyzed. The When used in conjunction with the parameter The possible values are:
The default value is |
|
|
Specifies a start time for selecting SQL statements. If the statement did not execute on or before the specified time, it will not be processed. Each date must be in the standard Oracle form of MM-DD-YYYY HH24:MI:SS, where:
The datatype is |
|
|
Contains the amount of space adjustment that can be consumed by SQL Access Advisor recommendations. Zero or negative values are only permitted if the workload scope is marked as When the SQL Access Advisor produces a set of recommendations, the resultant physical structures must be able to fit into the budgeted space. A space budget is computed by adding the Possible values:
The default value is |
|
|
Specifies the time in minutes that the SQL Access Advisor can use to perform an analysis operation. If the SQL Access Advisor reaches a specified recommendation quality or all input data has been analyzed, processing will terminate regardless of any remaining time. Possible values:
The default value is 720 (12 hours). The datatype is Note that specifying |
|
|
Contains a fully qualified list of actions that are eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted names are supported. An action can be any string. If an action is not quoted, it will be changed to uppercase lettering and stripped of leading and trailing spaces. An action string is not scanned for correctness. During a task execution, if a SQL statement's action does not match a name in the action list, it will not be processed by the task. An action name is case sensitive. The possible values are:
The default value is |
|
|
Contains a fully qualified list of application modules that are eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted names are supported. A module can be any string. If a module is not quoted, it will be changed to uppercase lettering and stripped of leading and trailing spaces. A module string is not scanned for correctness. During a task execution, if a SQL statement's module does not match a name in the module list, it will not be processed by the task. A module name is case sensitive. The possible values are:
The default value is |
|
|
Contains a fully qualified list of text strings that are eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted names are supported. A SQL string can be any string. If a string is not quoted, it will be changed to uppercase lettering and stripped of leading and trailing spaces. A SQL string is not scanned for correctness. During a task execution, if a SQL statement does not contain string in the SQL string list, it will not be processed by the task. The possible values are:
The default value is |
|
|
Contains a fully qualified list of tables that are eligible for tuning. The list elements are comma-delimited, and quoted identifiers are supported. Wildcard specifications are supported for tables. The default value is all tables within the user's scope are eligible for tuning. Supported wildcard character is When a SQL statement is processed, it will not be accepted unless at least one referenced table is specified in the valid table list. If the list is unused, then all table references within a SQL statement are considered valid. The valid syntax for a table reference is:
The possible values are:
The default value is |
|
|
Contains a fully qualified list of usernames that are eligible for processing in a SQL workload object. The list elements are comma-delimited, and quoted names are supported. During a task execution, if a SQL statement's username does not match a name in the username list, it will not be processed by the task. A username is not case sensitive unless it is quoted. The possible values are:
The default value is |
|
|
Describes the level of application coverage the workload represents. Possible values are
The datatype is |
Table 18-36 lists the input task parameters that can be set in the Segment Advisor using the SET_TASK_PARAMETER procedure.
Table 18-36 Segment Advisor Task Parameters
| Parameter | Description |
|---|---|
|
|
The data to use for analysis. The default value is
|
|
|
The time limit for which the Advisor should run. It is specified in seconds, and the default and possible values are |
|
|
Whether to generate recommendations for all segments. The default value is |
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
BEGIN
task_name := 'My Task';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'VALID_TABLELIST',
'SH.%,SCOTT.EMP');
END;
/
Table 18-37 lists the input task parameters that can be set in the Undo Advisor using the SET_TASK_PARAMETER procedure.
Table 18-37 Undo Advisor Task Parameters
| Parameter | Description |
|---|---|
|
|
The undo tablespace of the system. There is no default value, and the possible value is |
|
|
The starting time for the system to perform analysis using the snapshot numbers in the AWR repository. There is no default value and the possible values are the valid snapshot numbers in the AWR repository. |
|
|
The ending time for the system to perform analysis using the snapshot numbers in the AWR repository. There is no default value and the possible values are the valid snapshot numbers in the AWR repository. |
|
|
The number of seconds between the beginning time of the period and now. Describes a period of time for the system to perform analysis. |
|
|
The number of seconds between the ending time of the period and now. |
DECLARE
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.EXECUTE_TASK(tname);
END;
/
Automatic Database Diagnostic Monitor (ADDM) Task Parameters
Table 18-38 lists the input task parameters that can be set in ADDM using the SET_TASK_PARAMETER procedure. See Oracle Database Performance Tuning Guide for more information on using these parameters.
Table 18-38 ADDM Task Parameters
| Parameter | Description |
|---|---|
|
|
The starting time for the system to perform analysis using the snapshot numbers in the AWR repository. There is no default value, and the possible values are the valid snapshot numbers in the AWR repository. |
|
|
The ending time for the system to perform analysis using the snapshot numbers in the AWR repository. There is no default value, and the possible values are the valid snapshot numbers in the AWR repository. |
|
|
The database for |
|
|
The instance for |
|
|
If the |
|
|
The average time to read the database block in microseconds. The default value is 10 milliseconds, and the possible values are system-dependent. |
The following creates and executes an ADDM task for the current database and an AWR snapshot range between 19 and 26. Note that this example will analyze all instances, whether you have only one or an Oracle RAC database.
DECLARE
tid NUMBER;
tname VARCHAR2(30) := 'ADDM_TEST';
BEGIN
DBMS_ADVISOR.CREATE_TASK('ADDM', tid, tname, 'my test');
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', '19');
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', '26');
DBMS_ADVISOR.EXECUTE_TASK(tname);
END;
/
See Also:
Oracle Database Performance Tuning Guide for more information regarding ADDM usage
The DBMS_ADDM package for details on how to create and execute ADDM tasks
SQL Tuning Advisor Task Parameters
See the DBMS_SQLTUNE package and Oracle Database Performance Tuning Guide for more information.
This procedure shows how to decompose a materialized view into two or more materialized views and to restate the materialized view in a way that is more advantageous for fast refresh and query rewrite. It also shows how to fix materialized view logs and to enable query rewrite.
Table 18-39 TUNE_MVIEW Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The task name for looking up the results in a catalog view. If not specified, the system will generate a name and return. |
|
|
The original materialized view creation statement. |
See Also:
Oracle Database Performance Tuning Guide for more information about using theTUNE_MVIEW procedureExecuting TUNE_MVIEW generates two sets of output results: one is for CREATE implementation and the other is for undoing the CREATE MATERIALIZED VIEW implementation. The output results are accessible through USER_TUNE_MVIEW and DBA_TUNE_MVIEW views. You can also use DBMS_ADVISOR.GET_TASK_SCRIPT and DBMS_ADVISOR.CREATE_FILE to output the TUNE_MVIEW results into a script file for later execution.
USER_TUNE_MVIEW and DBA_TUNE_MVIEW Views
These views are to get the result after executing the TUNE_MVIEW procedure.
Table 18-40 USER_TUNE_MVIEW and DBA_TUNE_MVIEW Views
| Column Name | Column Description |
|---|---|
|
|
The materialized view owner's name. |
|
|
The task name as a key to access the set of recommendations |
|
|
Recommendation ID used to indicate the row is for |
|
|
Action ID used as the command order number. |
|
|
For
|
name VARCHAR2(30); DBMS_ADVISOR.TUNE_MVIEW.(name, 'SELECT AVG(C1) FROM my_fact_table WHERE c10 = 7');
The following is an example to show how to use TUNE_MVIEW to optimize a CREATE MATERIALIZED VIEW statement:
NAME VARCHAR2(30) := 'my_tune_mview_task'; EXECUTE DBMS_ADVISOR.TUNE_MVIEW (name, 'CREATE MATERIALIZED VIEW MY_MV REFRESH FAST AS SELECT C2, AVG(C1) FROM MY_FACT_TABLE WHERE C10 = 7 GROUP BY C2');
You can view the CREATE output results by querying USER_TUNE_MVIEW or DBA_TUNE_MVIEW as the following example:
SELECT * FROM USER_TUNE_MVIEW WHERE TASK_NAME='my_tune_mview_task' AND SCRIPT_TYPE='CREATE';
Alternatively, you can save the output results in an external script file as in the following example:
CREATE DIRECTORY TUNE_RESULTS AS ''/myscript_dir'' ;
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('my_tune_mview_task'), -
'/homes/tune','my_tune_mview_create.sql');
The preceding statement will save the CREATE output results in /myscript_dir/my_tune_mview_create.sql.
This procedure updates an existing task object. Task objects are typically used as input data for a particular advisor. Segment advice can be generated at the object, segment, or tablespace level.
DBMS_ADVISOR.UPDATE_OBJECT ( task_name IN VARCHAR2 object_id IN NUMBER, attr1 IN VARCHAR2 := NULL, attr2 IN VARCHAR2 := NULL, attr3 IN VARCHAR2 := NULL, attr4 IN CLOB := NULL, attr5 IN VARCHAR2 := NULL);
Table 18-41 UPDATE_OBJECT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
A valid advisor task name that uniquely identifies an existing task. |
|
|
The advisor-assigned object identifier. |
|
|
Advisor-specific data. If set to |
|
|
Advisor-specific data. If set to |
|
|
Advisor-specific data. If set to |
|
|
Advisor-specific data. If set to |
|
|
Advisor-specific data. If set to null, there will be no effect on the target object. |
The attribute parameters have different values depending upon the object type. See Oracle Database Administrator's Guide for details regarding these parameters and object types.
If for the object level, advice is generated on all partitions of the object (if the object is partitioned). The advice is not cascaded to any dependent objects. If for the segment level, advice can be obtained on a single segment, such as the partition or subpartition of a table, index, or lob column. If for a tablespace level, target advice for every segment in the tablespace will be generated.
See Oracle Database Administrator's Guide for further information regarding the Segment Advisor.
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
obj_id NUMBER;
BEGIN
task_name := 'My Task';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.CREATE_OBJECT (task_name,'SQL',NULL,NULL,NULL,
'SELECT * FROM SH.SALES',obj_id);
DBMS_ADVISOR.UPDATE_OBJECT (task_name, obj_id,NULL,NULL,NULL,
'SELECT count(*) FROM SH.SALES');
END;
/
This procedure updates the owner, name, and tablespace for a recommendation.
DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES ( task_name IN VARCHAR2 rec_id IN NUMBER, action_id IN NUMBER, attribute_name IN VARCHAR2, value IN VARCHAR2);
Table 18-42 UPDATE_REC_ATTRIBUTES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The task name that uniquely identifies an existing task. |
|
|
The Advisor-generated identifier number that is assigned to the recommendation. |
|
|
The Advisor-generated action identifier that is assigned to the particular command. |
|
|
Name of the attribute to be changed. The valid values are:
|
|
|
Specifies the new value for the recommendation attribute. |
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
workload_name VARCHAR2(30);
attribute VARCHAR2(100);
BEGIN
task_name := 'My Task';
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales WHERE promo_id = 10');
DBMS_ADVISOR.EXECUTE_TASK(task_name);
attribute := 'SH';
DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES(task_name, 1, 3, 'OWNER', attribute);
END;
/
Note:
This procedure is deprecated in Release 11gR1.This procedure changes various attributes of a SQL Workload object or template.
DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES ( workload_name IN VARCHAR2, new_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, read_only IN VARCHAR2 := NULL, is_template IN VARCHAR2 := NULL, how_created IN VARCHAR2 := NULL);
Table 18-43 UPDATE_SQLWKLD_ATTRIBUTES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The workload object name that uniquely identifies an existing workload. |
|
|
The new workload object name. If the value is |
|
|
A new workload description. If the value is |
|
|
Set to |
|
|
|
|
|
Indicates a source application name that initiated the workload creation. If the value is |
DECLARE
workload_name VARCHAR2(30);
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales WHERE promo_id = 10');
DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES(workload_name,'New workload name');
END;
/
Note:
This procedure is deprecated in Release 11gR1.This procedure updates an existing SQL statement in a specified SQL workload.
DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, sql_id IN NUMBER, application IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL, priority IN NUMBER := NULL, username IN VARCHAR2 := NULL); DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, search IN VARCHAR2, updated OUT NUMBER, application IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL, priority IN NUMBER := NULL, username IN VARCHAR2 := NULL);
Table 18-44 UPDATE_SQLWKLD_STATEMENT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The SQL Workload object name that uniquely identifies an existing workload. |
|
|
The Advisor-generated identifier number that is assigned to the statement. To specify all workload statements, use the constant |
|
|
Returns the number of statements changed by a searched update. |
|
|
Specifies a business application name that will be associated with the SQL statement. If the value is |
|
|
Specifies the application action for the statement. If the value is |
|
|
The relative priority of the SQL statement. The value must be one of the following: 1 - If the value is |
|
|
The Oracle user name that executed the SQL statement. If the value is Because a username is an Oracle identifier, the username value must be entered exactly like it is stored in the server. For example, if the user |
|
|
Disabled. |
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See RESET_TASK Procedure to set a task to its initial state.
DECLARE
workload_name VARCHAR2(30);
updated NUMBER;
id NUMBER;
BEGIN
workload_name := 'My Workload';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',
100,400,5041,103,640445,680000,2,
1,SYSDATE,1,'SH','SELECT AVG(amount_sold)
FROM sh.sales WHERE promo_id = 10');
SELECT sql_id INTO id FROM USER_ADVISOR_SQLW_STMTS
WHERE workload_name = 'My Workload';
DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT(workload_name, id);
END;
/
This procedure changes various attributes of a task or a task template.
DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES ( task_name IN VARCHAR2 new_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, read_only IN VARCHAR2 := NULL, is_template IN VARCHAR2 := NULL, how_created IN VARCHAR2 := NULL);
Table 18-45 UPDATE_TASK_ATTRIBUTES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The Advisor task name that uniquely identifies an existing task. |
|
|
The new Advisor task name. If the value is |
|
|
A new task description. If the value is |
|
|
Sets the task to read-only. Possible values are: If the value is |
|
|
Marks the task as a template. Physically, there is no difference between a task and a template; however, a template cannot be executed. Possible values are: |
|
|
Indicates a source application name that initiated the task creation. If the value is |
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
BEGIN
task_name := 'My Task';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES(task_name,'New Task Name');
DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('New Task Name',NULL,'New description');
END;
/