12
DBMS_ADVISOR

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 the various database server components.

This chapter contains the following topics:

• Using DBMS_ADVISOR
  • Security Model
  • Subprograms Used in All Advisors
  • Subprograms Used in SQLAccess Advisor
  • Parameters
• Summary of DBMS_ADVISOR Subprograms

---

Using DBMS_ADVISOR

• Security Model
• Subprograms Used in All Advisors
• Subprograms Used in SQLAccess Advisor
• Parameters

---

Security Model

Security on this package can be controlled by granting EXECUTE on this package to selected users or roles. You might want to write a cover package on top of this one that restricts the alert names used. EXECUTE privilege on this cover package can then be granted rather than on this package. In addition, there is an ADVISOR privilege, which is required by DBMS_ADVISOR procedures.

---

Subprograms Used in All Advisors

Table 12-1 lists DBMS_ADVISOR procedures that are used in all Advisors.

Table 12-1 DBMS_ADVISOR Generic Subprograms 

Subprogram	Description
CANCEL_TASK Procedure	Cancels a currently executing task operation
CREATE_FILE Procedure	Creates an external file from a PL/SQL CLOB variable, which is useful for creating scripts and reports
CREATE_OBJECT Procedure	Creates a new task object
CREATE_TASK Procedures	Creates a new Advisor task in the repository
DELETE_TASK Procedure	Deletes the specified task from the repository
EXECUTE_TASK Procedure	Executes the specified task
GET_REC_ATTRIBUTES Procedure	Retrieves specific recommendation attributes from a task
GET_TASK_SCRIPT Procedure	Creates and returns an executable SQL script of the Advisor task's recommendations in a buffer
INTERRUPT_TASK Procedure	Stops a currently executing task, ending its operations as it would at a normal exit
MARK_RECOMMENDATION Procedure	Sets the annotation_status for a particular recommendation
QUICK_TUNE Procedure	Performs an analysis on a single SQL statement
RESET_TASK Procedure	Resets a task to its initial state
SET_DEFAULT_TASK_PARAMETER Procedures	Modifies a default task parameter
SET_TASK_PARAMETER Procedures	Sets the specified task parameter value
UPDATE_OBJECT Procedure	Updates a task object
UPDATE_REC_ATTRIBUTES Procedure	Updates an existing recommendation for the specified task
UPDATE_TASK_ATTRIBUTES Procedure	Updates a task's attributes

---

Subprograms Used in SQLAccess Advisor

Table 12-2 lists DBMS_ADVISOR procedures that are used in the SQLAccess Advisor.

Table 12-2 DBMS_ADVISOR SQLAccess Advisor Subprograms 

Subprogram	Description
ADD_SQLWKLD_REF Procedure	Adds a workload reference to an Advisor task
ADD_SQLWKLD_STATEMENT Procedure	Adds a single statement to a workload
CREATE_SQLWKLD Procedure	Creates a new workload object
DELETE_SQLWKLD Procedure	Deletes an entire workload object
DELETE_SQLWKLD_REF Procedure	Deletes an entire workload object
DELETE_SQLWKLD_STATEMENT Procedure	Deletes one or more statements from a workload
IMPORT_SQLWKLD_SCHEMA Procedure	Imports data into a workload from the current SQL cache
IMPORT_SQLWKLD_SQLCACHE Procedure	Imports data into a workload from the current SQL cache
IMPORT_SQLWKLD_STS Procedure	Imports data into a workload from a SQL Tuning Set into a SQL workload data object
IMPORT_SQLWKLD_SUMADV Procedure	Imports data into a workload from the current SQL cache
IMPORT_SQLWKLD_USER Procedure	Imports data into a workload from the current SQL cache
SET_DEFAULT_SQLWKLD_PARAMETER Procedure	Imports data into a workload from schema evidence
SET_SQLWKLD_PARAMETER Procedure	Sets the value of a workload parameter
TUNE_MVIEW Procedure	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
UPDATE_SQLWKLD_ATTRIBUTES Procedure	Updates a workload object
UPDATE_SQLWKLD_STATEMENT Procedure	Updates one or more SQL statements in a workload

---

Parameters

The Oracle Database includes a number of advisors for different subsystems in the database to automatically determine how the operation of the corresponding subcomponents could be further optimized. The SQL Tuning and SQLAccess Advisor, for example, provide recommendations for running SQL statements faster. Memory advisors help size the various memory components without resorting to trial-and-error techniques. The Segment Advisor handles all space-related issues, such as recommending wasted-space reclamation, predicting the sizes of new tables and indexes, and analyzing growth trends, and the Undo Advisor lets you size the undo tablespace. You should see the specific documentation for each Advisor for details regarding how parameters are used.

This section includes:

• Generic Advisor Parameters
• SQLAccess Advisor Task Parameters
• SQL Workload Object Parameters
• Segment Advisor Parameters

Generic Advisor Parameters

The generic Advisor parameters are the following:

• DAYS_TO_EXPIRE
• END_SNAPSHOT
• END_TIME
• INSTANCE
• JOURNALING
• MODE
• START_SNAPSHOT
• START_TIME
• TARGET_OBJECTS
• TIME_LIMIT

See your Advisor-specific documentation for further information.

SQLAccess Advisor Task Parameters

Table 12-3 lists SQLAccess Advisor task parameters.

Table 12-3 SQLAccess Advisor Task Parameters 

Parameter	Datatype	Description
ACTION_LIST	STRINGLIST	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. Possible values are: Single action Comma-delimited action list ADVISOR_UNUSED (default)
COMMENTED_FILTER_LIST	NUMBER	Comma-delimited list of strings. When set, SQLAccess Advisor will filter out any SQL statement that contain any of the specified strings in the first 20 characters of its text.
CREATION_COST	STRING	When set to true (default), the SQL Access Advisor will weigh the cost of creation of the access structure (index or materialized view) against the frequency of the query and potential improvement in the query execution time. When set to false, the cost of creation is ignored.
DAYS_TO_EXPIRE	NUMBER	Specifies the expiration time in days for the current SQLAccess 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. Once the task expires, it will become a candidate for removal by an automatic purge operation. Possible values are: An integer in the range of 0 to 2147483647 ADVISOR_UNLIMITED ADVISOR_UNUSED The default value is 30.
DEF_INDEX_OWNER	STRING	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: Existing schema name. Quoted identifiers are supported. ADVISOR_UNUSED (default)
DEF_INDEX_TABLESPACE	STRING	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: Existing tablespace name. Quoted identifiers are supported. ADVISOR_UNUSED. No tablespace clause will be present in the script for indexes. The default value is ADVISOR_UNUSED.
DEF_MVIEW_OWNER	STRING	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: Existing schema name. Quoted identifiers are supported. ADVISOR_UNUSED (default)
DEF_MVIEW_TABLESPACE	STRING	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: Existing tablespace name. Quoted identifiers are supported. ADVISOR_UNUSED. No tablespace clause will be present in the script for materialized views. The default value is ADVISOR_UNUSED.
DEF_MVLOG_TABLSPACE	STRING	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: Existing tablespace name. Quoted identifiers are supported. ADVISOR_UNUSED. No tablespace clause will be present in the script for materialized view logs. The default value is ADVISOR_UNUSED.
DML_VOLATILITY	STRING	When set to TRUE, the SQLAccess advisor will consider the impact of index maintenance and materialized view refresh in determining the recommendations. It will limit the access structure recommendations involving columns or tables that are frequently updated. For example, if there are too many DMLs on a column, it may favor a Btree index over a bitmap index on that column. For this process to be effective, the workload must include DML (insert/update/delete/merge/direct path inserts) statements that represent the update behavior of the application. See the related parameter refresh_mode.
END_TIME	STRING	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-YYY HH24:MI:SS, where: DD is the numeric date MM is the numeric month YYYY is the numeric year HH is the hour in 24 hour format MI is the minute SS is the second
EVALUATION_ONLY	STRING	If set to TRUE, causes SQLAccess Advisor to analyze the workload, but only comment on how well the current configuration is supporting it. No tuning recommendations will be generated. Possible values are: FALSE (default) and TRUE
EXECUTION_TYPE	STRINGLIST	The type of recommendations that is desired. Possible values: FULL All supported recommendation types will be considered. INDEX_ONLY The SQLAccess Advisor will only consider index solutions as recommendations. MVIEW_ONLY The SQLAccess Advisor will consider materialized view and materialized view log solutions as recommendations. MVIEW_LOG_ONLY The SQLAccess Advisor will only consider materialized view log solutions as recommendations. The default value is FULL.
INDEX_NAME_TEMPLATE	STRING	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 SQLAccess 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: Any literal value up to 22 characters. TABLE Causes the parent table name to be substituted into the index name. If the name is too long, it will be trimmed to fit. TASK_ID Causes the current task identifier number to be inserted in hexadecimal form. SEQ Causes a sequence number to be inserted in hexadecimal form. Because this number is used to guarantee uniqueness, it is a required token. The default template is: TABLE_IDX$$_TASK_IDSEQ
JOURNALING	NUMBER	Controls the logging of messages to the journal (USER_ADVISOR_JOURNAL view). The higher the setting, the more information is logged to the journal. Valid settings are: 0: no journal messages 1: informational messages only 2: warning messages 3: explanation of errors 4: explanation of fatal errors (default) 5-9: debug messages
MODE	STRING	Specifies the mode by which Access Advisor will operate during an analysis. Valid values are: LIMITED Indicates the Advisor will attempt to a quick job by limiting the search-space of candidate recommendations, and correspondingly, the results may be of a low quality. COMPREHENSIVE Indicates the Advisor will search a large pool of candidates that may take long to run, but the resulting recommendations will be of the highest quality. The default value is COMPREHENSIVE.
MODULE_LIST	STRINGLIST	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 workload import operation, if a SQL statement's application module does not match a name in the module list, it will not be stored in the workload object. Possible values are: Single application Comma-delimited module list ADVISOR_UNUSED (default)
MVIEW_NAME_TEMPLATE	STRING	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 SQLAccess 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: Any literal value up to 22 characters. TASK_ID Causes the current task identifier number to be inserted in hexadecimal form. SEQ Causes a sequence number to be inserted in hexadecimal form. Because this number is used to guarantee uniqueness, it is a required token. The default template is: MV$$_TASK_IDSEQ
ORDER_LIST	STRINGLIST	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 ORDER_LIST values. A comma must separate multiple order keys. Possible values are: BUFFER_GETS Sets the order using the SQL statement's buffer-get count value. CPU_TIME Sets the order using the SQL statement's CPU time value. DISK_READS Sets the order using the SQL statement's disk-read count value. ELAPSED_TIME Sets the order using the SQL statement's elapsed time value. EXECUTIONS Sets the order using the SQL statement's execution frequency value. OPTIMIZER_COST Sets the order using the SQL statement's optimizer cost value. I/O Sets the order using the SQL statement's I/O count value. PRIORITY Sets the order using the user-supplied business priority value. All values are accessed in descending order, where a high value is considered more interesting than a low value. The default value is PRIORITY, OPTIMIZER_COST.
REFRESH_MODE	STRING	Specifies whether materialized views are refreshed ON_DEMAND or ON_COMMIT. This will be used to weigh the impact of materialized view refresh when the parameter dml_volatility is set to TRUE. Possible values are: ON_DEMAND (default) and ON_COMMIT
REPORT_DATE_FORMAT	STRING	This is the default date and time formatting template. The default format is DD/MM/YYYYHH24:MI.
SQL_LIMIT	NUMBER	Specifies the number of SQL statements to be analyzed. The SQL_LIMIT filter is applied after all other filters have been applied. For example, if only statements referencing the table foo.bar are to be accepted, the SQL_LIMIT value will be only apply to those statements. When used in conjunction with the parameter ORDER_LIST, SQLAccess Advisor will process the most interesting SQL statements by ordering the statements according to the specified sort keys. Possible values: An integer in the range of 1 to 2147483647 ADVISOR_UNLIMITED ADVISOR_UNUSED (default)
START_TIME	STRING	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-YYY HH24:MI:SS, where: DD is the numeric date MM is the numeric month YYYY is the numeric year HH is the hour in 24 hour format MI is the minute SS is the second
STORAGE_CHANGE	NUMBER	Contains the amount of space adjustment that can be consumed by SQLAccess Advisor recommendations. Zero or negative values are only permitted if the workload type is marked as FULL. When the SQLAccess 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 STORAGE_CHANGE value to the space quantity currently used by existing access structures. A negative STORAGE_CHANGE value may force SQLAccess Advisor to remove existing structures in order to shrink space demand. Possible values: Any valid integer including negative values, zero and positive values. The default value is ADVISOR_UNLIMITED.
USERNAME_LIST	STRINGLIST	Contains a fully qualified list of usernames that are eligible for processing in a 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. Possible values: Single username comma-delimited username list ADVISOR_UNUSED The default value is ADVISOR_UNUSED.
VALID_TABLE_LIST	TABLELIST	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 %. A % wildcard matches any set of consecutive characters. 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. Valid syntax for a table reference is: schema.table schema schema.% (equivalent to schema) Possible values: Single table reference comma-delimited table reference list ADVISOR_UNUSED The default value is ADVISOR_UNUSED.
WORKLOAD_SCOPE	STRING	Describes the level of application coverage the workload represents. Possible values are FULL and PARTIAL. FULL Should be used if the workload contains all interesting application SQL statements for the targeted tables. PARTIAL (default) Should be used if the workload contains anything less than a full representation of the interesting application SQL statements for the targeted tables.

SQL Workload Object Parameters

Table 12-4 lists SQLAccess Advisor object parameters.

Table 12-4 SQL Workload Object Parameters 

Name	Datatype	Description
ACTION_LIST	STRINGLIST	Contains a fully qualified list of actions that are eligible for saving in a workload. 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 workload import operation, if a SQL statements action does not match a name in the action list, it will not be stored in the workload object. An action name is case sensitive. Possible values: Single action comma-delimited action list ADVISOR_UNUSED The default value is ADVISOR_UNUSED.
COMMENTED_FILTER_LIST	NUMBER	Comma-delimited list of strings. When set, SQLAccess Advisor will filter out any SQL statement that contain any of the specified strings in the first 20 characters of its text.
DAYS_TO_EXPIRE	NUMBER	Specifies the expiration time in days for the current SQL Workload object. The value is relative to the last modification date. Once the data expires, it will become a candidate for removal by an automatic purge operation. Possible values: An integer in the range of 0 to 2147483647 ADVISOR_UNLIMITED ADVISOR_UNUSED The default value is 30.
END_TIME	STRING	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-YYY HH24:MI:SS, where: DD is the numeric date MM is the numeric month YYYY is the numeric year HH is the hour in 24 hour format MI is the minute SS is the second
INVALID_TABLE_LIST	TABLELIST	Contains a fully qualified list of tables that are not eligible for tuning. The list elements are comma-delimited, and quoted identifiers are supported. Wildcard specifications are supported for both schemas and tables. The default value is all tables within the users scope are eligible for tuning. The supported wildcard character is %. A % wildcard matches any set of consecutive characters. When a SQL statement is processed, it will not be accepted if any referenced table matches an entry in the invalid table list. Valid syntax for a table reference is: schema.table schema schema.% (Equivalent to schema) Possible values: Single table reference comma-delimited table reference list ADVISOR_UNUSED The default value is ADVISOR_UNUSED. Note that SQLAccess Advisor maintains an internal list of non-tunable tables regardless of the contents of the INVALID_TABLE_LIST parameter. No table that is owned by SYS, SYSTEM or any other pre-defined Oracle schema can be tuned.
JOURNALING	NUMBER	Controls the logging of messages to the journal (USER_ADVISOR_JOURNAL view). The higher the setting, the more information is logged to the journal. Valid settings are: 0: no journal messages 1: informational messages only 2: warning messages 3: explanation of errors 4: explanation of fatal errors (default) 5-9: debug messages
MODULE_LIST	STRINGLIST	Contains a fully qualified list of application modules that are eligible for saving 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 workload import operation, if a SQL statements application module does not match a name in the module list, it will not be stored in the workload objecttask. Possible values: Single module comma-delimited module list ADVISOR_UNUSED The default value is ADVISOR_UNUSED.
ORDER_LIST	STRING	Contains the primary natural order in which the SQLAccess Advisor processes workload elements during the import operation. Possible values are: BUFFER_GETS, OPTIMIZER_COST, CPU_TIME, DISK_READS, ELAPSED_TIME, EXECUTIONS, and PRIORITY.
REPORT_DATE_FORMAT		This parameter is not used.
SQL_LIMIT	NUMBER	Specifies the maximum number of SQL statements to be saved during a workload import operation. The SQL_LIMIT filter is applied after all other filters have been applied. For example, if only statements referencing the table foo.bar are to be accepted, the SQL_LIMIT value will be only apply to those statements. When used in conjunction with the parameter ORDER_LIST, Access Advisor will process and save the most interesting SQL statements by ordering the statements according to the specified sort keys. Possible values: An integer in the range of 1 to 2147483647 ADVISOR_UNLIMITED ADVISOR_UNUSED The default value is ADVISOR_UNLIMITED.
START_TIME	STRING	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-YYY HH24:MI:SS, where: DD is the numeric date MM is the numeric month YYYY is the numeric year HH is the hour in 24 hour format MI is the minute SS is the second
USERNAME_LIST	STRINGLIST	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 workload import operation, if a SQL statements username does not match a name in the username list, it will not be stored in the workload object. A Username is not case sensitive unless it is quoted. Possible values: Single username comma-delimited username list ADVISOR_UNUSED The default value is ADVISOR_UNUSED.
VALID_TABLE_LIST	TABLELIST	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. The supported wildcard character is %. A % wildcard matches any set of consecutive characters. 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. When using the IMPORT_SQLWKLD_SCHEMA procedure, the valid_table_list parameter cannot contain wildcards such as SCO% or SCOTT.EMP%. The only form of wildcards supported is SCOTT.%, which specifies all tables in a given schema. Valid syntax for a table reference is: schema.table schema schema.% (Equivalent to schema) Possible values: Single table reference comma-delimited table reference list ADVISOR_UNUSED The default value is ADVISOR_UNUSED.

Segment Advisor Parameters

Table 12-5 lists the input task parameters that can be set in the Segment Advisor using the SET_TASK_PARAMETER procedure.

Table 12-5 Segment Advisor Task Parameters 

Parameter	Default Value	Possible Values	Description
MODE	COMPREHENSIVE	LIMITED: Analysis restricted to statistics available in Automatic Workload Repository. COMPREHENSIVE: Comprehensive analysis based on sampling and Automatic Workload Repository statistics.	The data to use for analysis.
TIME_LIST	UNLIMITED	UNLIMITED	The time limit for which the Advisor should run. Specified in seconds.
RECOMMEND_ALL	TRUE	TRUE: Generate recommendations on all segments specified by the user. FALSE: Generate recommendations for only those objects that are eligible for shrink.	Whether to generate recommendations for all segments.

---

Summary of DBMS_ADVISOR Subprograms

Table 12-6 DBMS_ADVISOR Package Subprograms

Subprogram	Description	Used in
ADD_SQLWKLD_REF Procedure	Adds a workload reference to an Advisor task	SQLAccess Advisor only
ADD_SQLWKLD_STATEMENT Procedure	Adds a single statement to a workload	SQLAccess Advisor only
CANCEL_TASK Procedure	Cancels a currently executing task operation	All Advisors
CREATE_FILE Procedure	Creates an external file from a PL/SQL CLOB variable, which is useful for creating scripts and reports	All Advisors
CREATE_OBJECT Procedure	Creates a new task object	All Advisors
CREATE_SQLWKLD Procedure	Creates a new workload object	SQLAccess Advisor only
CREATE_TASK Procedures	Creates a new Advisor task in the repository	All Advisors
DELETE_SQLWKLD Procedure	Deletes an entire workload object	SQLAccess Advisor only
DELETE_SQLWKLD_REF Procedure	Deletes an entire workload object	SQLAccess Advisor only
DELETE_SQLWKLD_STATEMENT Procedure	Deletes one or more statements from a workload	SQLAccess Advisor only
DELETE_TASK Procedure	Deletes the specified task from the repository	All Advisors
EXECUTE_TASK Procedure	Executes the specified task	All Advisors
GET_REC_ATTRIBUTES Procedure	Retrieves specific recommendation attributes from a task	All Advisors
GET_TASK_SCRIPT Procedure	Creates and returns an executable SQL script of the Advisor task's recommendations in a buffer	All Advisors
IMPORT_SQLWKLD_SCHEMA Procedure	Imports data into a workload from the current SQL cache	SQLAccess Advisor only
IMPORT_SQLWKLD_SQLCACHE Procedure	Imports data into a workload from the current SQL cache	SQLAccess Advisor only
IMPORT_SQLWKLD_STS Procedure	Imports data into a workload from a SQL Tuning Set into a SQL workload data object	SQLAccess Advisor only
IMPORT_SQLWKLD_SUMADV Procedure	Imports data into a workload from the current SQL cache	SQLAccess Advisor only
IMPORT_SQLWKLD_USER Procedure	Imports data into a workload from the current SQL cache	SQLAccess Advisor only
INTERRUPT_TASK Procedure	Stops a currently executing task, ending its operations as it would at a normal exit	All Advisors
MARK_RECOMMENDATION Procedure	Sets the annotation_status for a particular recommendation	All Advisors
QUICK_TUNE Procedure	Performs an analysis on a single SQL statement	All Advisors
RESET_TASK Procedure	Resets a task to its initial state	All Advisors
SET_DEFAULT_SQLWKLD_PARAMETER Procedure	Imports data into a workload from schema evidence	SQLAccess Advisor only
SET_DEFAULT_TASK_PARAMETER Procedures	Modifies a default task parameter	All Advisors
SET_SQLWKLD_PARAMETER Procedure	Sets the value of a workload parameter	SQLAccess Advisor only
SET_TASK_PARAMETER Procedures	Sets the specified task parameter value	All Advisors
TUNE_MVIEW Procedure	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	SQLAccess Advisor only
UPDATE_OBJECT Procedure	Updates a task object	All Advisors
UPDATE_REC_ATTRIBUTES Procedure	Updates an existing recommendation for the specified task	All Advisors
UPDATE_SQLWKLD_ATTRIBUTES Procedure	Updates a workload object	SQLAccess Advisor only
UPDATE_SQLWKLD_STATEMENT Procedure	Updates one or more SQL statements in a workload	SQLAccess Advisor only
UPDATE_TASK_ATTRIBUTES Procedure	Updates a task's attributes	All Advisors

---

ADD_SQLWKLD_REF Procedure

This procedure establishes a link between the current SQLAccess 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 SQLAccess Advisor task and a SQL Workload object is made, the workload is protected from removal or modification.

Syntax

DBMS_ADVISOR.ADD_SQLWKLD_REF (
   task_name              IN VARCHAR2,
   workload_name          IN VARCHAR2);

Parameters

Table 12-7 ADD_SQLWKLD_REF Procedure Parameters

Parameter	Description
task_name	The SQLAccess task name that uniquely identifies an existing task.
workload_name	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 DELETE_REFERENCE.

Examples

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);
END;
/

---

ADD_SQLWKLD_STATEMENT Procedure

This procedure adds a single statement to the specified workload.

Syntax

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);

Parameters

Table 12-8 ADD_SQLWKLD_STATEMENT Procedure Parameters 

Parameter	Description
workload_name	The workload name that uniquely identifies an existing workload.
module	An optional business application module that will be associated with the SQL statement.
action	An optional application action that will be associated with the SQL statement.
cpu_time	The total CPU time in seconds that is consumed by SQL statement over the population time window.
elapsed_time	The total elapsed time in seconds that is consumed by SQL statement over the population time window.
disk_reads	The total disk-read operations that are consumed by SQL statement over the population time window.
buffer_gets	The total buffer-get operations that are consumed by SQL statement over the population time window.
rows_processed	The average number of rows processed by the statement over the population time window.
optimizer_cost	The optimizer's calculated cost value.
executions	The total execution count by the SQL statement over the population time window.
priority	The relative priority of the SQL statement. The value must be one of the following: 1-HIGH, 2-MEDIUM, or 3-LOW.
last_execution_date	The date and time at which the SQL statement last executed. If the value is NULL, then the current date and time will be used.
stat_period	Time interval in seconds from which statement statistics were calculated.
username	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 SCOTT is the executing user, then you must provide the user identifier SCOTT in all uppercase letters. It will not recognize the user scott as a match for SCOTT.
sql_text	The complete SQL statement. To increase the quality of a recommendation, the SQL statement should not contain bind variables.

Usage Notes

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.

Examples

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;
/

---

CANCEL_TASK 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 SQLAccess Advisor periodically checks for soft interrupts and acts appropriately. As a result, this operation may take a few seconds to respond to a call.

Syntax

DBMS_ADVISOR.CANCEL_TASK (
   task_name      IN  VARCHAR2);

Parameters

Table 12-9 CANCEL_TASK Procedure Parameter

Parameter	Description
task_name	A valid Advisor task name that uniquely identifies an existing task.

Usage Notes

A cancel command effective 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.

Examples

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.CANCEL_TASK('My Task');
END;
/

---

CREATE_FILE Procedure

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.

Syntax

DBMS_ADVISOR.CREATE_FILE (
   buffer       IN  CLOB,
   location     IN  VARCHAR2,
   filename     IN  VARCHAR2);

Parameters

Table 12-10 CREATE_FILE Procedure Parameters

Parameter	Description
buffer	A CLOB buffer containing report or script information.
location	Specifies the directory that will contain the new file. You must use the directory alias as defined by the CREATE DIRECTORY statement. The Advisor will translate the alias into the actual directory location.
filename	Specifies the output file to receive the script commands. The filename can only contain the name and an optional file type of the form filename.filetype.

Usage Notes

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.

Examples

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;
/

---

CREATE_OBJECT Procedure

This procedure creates a new task object.

Syntax

DBMS_ADVISOR.CREATE_TASK (
   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);

Parameters

Table 12-11 CREATE_OBJECT Procedure Parameters

Parameter	Description
task_name	A valid advisor task name that uniquely identifies an existing task.
object_type	Specifies the external object type.
attr1	Advisor-specific data.
attr2	Advisor-specific data.
attr3	Advisor-specific data.
attr4	Advisor-specific data.
attr5	Advisor-specific data.
object_id	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.

Return Values

Returns the new object identifier.

Usage Notes

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.

Examples

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);
END;
/

---

CREATE_SQLWKLD Procedure

This procedure creates a new private SQL Workload object for the user. A SQL Workload object manages a SQL workload on behalf of the SQLAccess Advisor. A SQL Workload object must exist prior to performing any other SQL Workload operations, such as importing or updating SQL statements.

Syntax

DBMS_ADVISOR.CREATE_SQLWKLD (
   workload_name            IN VARCHAR2,
   description              IN VARCHAR2 := NULL,
   template                 IN VARCHAR2 := NULL,
   is_template              IN VARCHAR2 := 'FALSE');

Parameters

Table 12-12 CREATE_SQLWKLD Procedure Parameters

Parameter	Description
workload_name	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.
description	Specifies an optional workload description. Descriptions can be up to 256 characters.
template	An optional SQL Workload name of an existing workload data object or data object template.
is_template	An optional value that enables you to set the newly created workload as a template. Valid values are TRUE and FALSE.

Return Values

The SQLAccess Advisor returns a unique workload object identifier number that must be used for subsequent activities within the new SQL Workload object.

Usage Notes

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.

Once a SQL Workload object is present, it can then be referenced by one or more SQLAccess Advisor tasks using the ADD_SQLWKLD_REF procedure.

Examples

DECLARE
  workload_name VARCHAR2(30);
BEGIN
  workload_name := 'My Workload';

DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
END;
/

---

CREATE_TASK Procedures

This procedure creates a new Advisor task in the repository.

Syntax

DBMS_ADVISOR.CREATE_TASK (
   advisor_name           IN VARCHAR2 NOT NULL,
   task_name              IN VARCHAR2,
   task_desc              IN VARCHAR2 := NULL,
   task_or_template       IN VARCHAR2 := NULL,
   is_template            IN VARCHAR2 := 'FALSE');

DBMS_ADVISOR.CREATE_TASK (
   advisor_name            IN VARCHAR2 NOT NULL,
   task_id                 OUT NUMBER,
   task_name               IN OUT VARCHAR2,
   task_desc               IN VARCHAR2 := NULL,
   task_or_template        IN VARCHAR2 := NULL,
   is_template             IN VARCHAR2 := 'FALSE');

Parameters

Table 12-13 CREATE_TASK Procedure Parameters

Parameter	Description
advisor_name	Specifies the unique advisor name as defined in the view DBA_ADVISOR_DEFINITIONS.
task_id	A number that uniquely identifies the created task. The number is generated by the procedure and returned to the user.
task_name	Specifies a new task name. Names must be unique among all tasks for the user. For version 2 of CREATE_TASK, the system can generate a unique name. Names can be up to 30 characters long.
task_desc	Specifies an optional task description. Descriptions can be up to 256 characters in length.
task_or_template	An optional task name of an existing task or task template. To specify built-in SQLAccess Advisor templates, use the template name as described earlier.
is_template	An optional value that allows the user to set the newly created task as template. Valid values are: TRUE and FALSE.

Return Values

Returns a unique task ID number and a unique task name if one is not specified.

Usage Notes

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 SQLAccess Advisor, use the identifier DBMS_ADVISOR.SQLACCESS_ADVISOR as the advisor_name.

The SQLAccess 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 SQLAccess Advisor, this is the default template.

Examples

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);
END;
/

---

DELETE_SQLWKLD Procedure

This procedure deletes an existing SQL Workload object from the repository.

Syntax

DBMS_ADVISOR.DELETE_SQLWKLD (
   workload_name        IN VARCHAR2);

Parameters

Table 12-14 DELETE_SQLWKLD Procedure Parameters 

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload. The wildcard % is supported as a WORKLOAD_NAME. The rules of use are identical to the LIKE operator. For example, to delete all tasks for the current user, use the wildcard % as the WORKLOAD_NAME. If a wildcard is provided, the DELETE_SQLWKLD operation will not delete any workloads marked as READ_ONLY or TEMPLATE.

Usage Notes

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.

Examples

DECLARE
  workload_name VARCHAR2(30);
BEGIN
  workload_name := 'My Workload';

DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.DELETE_SQLWKLD(workload_name);
END;
/

---

DELETE_SQLWKLD_REF Procedure

This procedure removes a link between the current SQLAccess task and a SQL Workload data object.

Syntax

DBMS_ADVISOR.DELETE_SQLWKLD_REF (
   task_name              IN VARCHAR2,
   workload_name          IN NUMBER);

Parameters

Table 12-15 DELETE_SQLWKLD_REF Procedure Parameters

Parameter	Description
task_name	The SQLAccess task name that uniquely identifies an existing task.
workload_name	The name of the workload object to be unlinked. The wildcard % is supported as a workload_name. The rules of use are identical to the LIKE operator. For example, to remove all links to workload objects, use the wildcard % as the workload_name.

Examples

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.DELETE_SQLWKLD_REF(task_name, workload_name);
END;
/

---

DELETE_SQLWKLD_STATEMENT Procedure

This procedure deletes one or more statements from a workload.

Syntax

DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT (
   workload_name        IN VARCHAR2,
   sql_id               IN VARCHAR2);

DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT (
   workload_name        IN VARCHAR2,
   search               IN VARCHAR2,
   deleted              OUT NUMBER);

Parameters

Table 12-16 DELETE_SQLWKLD_STATEMENT Procedure Parameters

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload.
sql_id	The Advisor-generated identifier number that is assigned to the statement. To specify all workload statements, use the constant ADVISOR_ALL.
search	An optional SQL predicate that allows the user to refine the workload entries that will be deleted. The WHERE keyword should not be included in the text.
deleted	Returns the number of statements deleted by the searched deleted operation.

Usage Notes

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.

Examples

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, 'MONTHLY', 'ROLLUP',
                                     100,400,5041,103,640445,680000,2,
                                     1,SYSDATE,1,'SH','SELECT avg(amount_sold) 
FROM sh.sales');
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');

DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT(workload_name,'module =
   ''MONTHLY''',deleted);

  SELECT min(sql_id) INTO id FROM USER_ADVISOR_SQLW_STMTS
   WHERE workload_name = 'My Workload';

DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT(workload_name, id);
END;
/

---

DELETE_TASK Procedure

This procedure deletes an existing task from the repository.

Syntax

DBMS_ADVISOR.DELETE_TASK (
   task_name          IN VARCHAR2);

Parameters

Table 12-17 DELETE_TASK Procedure Parameters

Parameter	Description
task_name	A single Advisor task name that will be deleted from the repository. The wildcard % is supported as a TASK_NAME. The rules of use are identical to the LIKE operator. For example, to delete all tasks for the current user, use the wildcard % as the TASK_NAME. If a wildcard is provided, the DELETE_TASK operation will not delete any tasks marked as READ_ONLY or TEMPLATE.

Examples

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.DELETE_TASK(task_name);
END;
/

---

EXECUTE_TASK Procedure

This procedure performs the Advisor analysis or evaluation for the specified task.

Syntax

DBMS_ADVISOR.EXECUTE_TASK (
   task_name          IN VARCHAR2);

Parameters

Table 12-18 EXECUTE_TASK Procedure Parameters

Parameter	Description
task_name	The task name that uniquely identifies an existing task.

Usage Notes

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.

Examples

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);
END;
/

---

GET_REC_ATTRIBUTES Procedure

This procedure retrieves a specified attribute of a new object as recommended by Advisor analysis.

Syntax

DBMS_ADVISOR.GET_REC_ATTRIBUTES (
   workload_name         IN VARCHAR2,
   rec_id                IN NUMBER,
   action_id             IN NUMBER,
   attribute_name        IN VARCHAR2,
   value                 OUT VARCHAR2);

Parameters

Table 12-19 GET_REC_ATTRIBUTES Procedure Parameters 

Parameter	Description
task_name	The task name that uniquely identifies an existing task.
rec_id	The Advisor-generated identifier number that is assigned to the recommendation.
action_id	The Advisor-generated action identifier that is assigned to the particular command.
attribute_name	Specifies the attribute to change.
value	The buffer to receive the requested attribute value.

Return Values

The requested attribute value is returned in the VALUE argument.

Usage Notes

This will name and assign ownership to new objects such as indexes and materialized views during the analysis operation. However, it will not necessarily choose appropriate names, so you can manually set the owner, name and tablespace values for new objects.

Examples

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;
/

---

GET_TASK_REPORT Procedure

This procedure creates and returns an XML report for the specified task.

Syntax

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)
RETURN CLOB;

Parameters

Table 12-20 GET_TASK_REPORT Procedure Parameters 

Parameter	Description
task_name	The name of the task from which the script will be created.
type	The possible values are: TEXT, HTML, and XML.
level	The possible values are BASIC, TYPICAL, and ALL.
section	Advisor-specific report sections.
owner_name	Owner of the task. If specified, the system will check to see if the current user has read privileges to the task data.

Return Values

Returns the buffer receiving the script.

---

GET_TASK_SCRIPT Procedure

This procedure 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.

Syntax

DBMS_ADVISOR.GET_TASK_SCRIPT (
   task_name          IN VARCHAR2
   type               IN VARCHAR2 := 'IMPLEMENTATION',
   rec_id             IN NUMBER := NULL,
   action_id          IN NUMBER := NULL)
RETURN CLOB;

Parameters

Table 12-21 GET_TASK_SCRIPT Procedure Parameters

Parameter	Description
task_name	The task name that uniquely identifies an existing task.
type	Specifies the type of script to generate. The possible values are: IMPLEMENTATION and UNDO
rec_id	An optional recommendation identifier number that can be used to extract a subset of the implementation script. A zero or the value DBMS_ADVISOR.ADVISOR_ALL indicates all accepted recommendations would be included. The default is to include all accepted recommendations for the task.
action_id	Optional action identifier number that can be used to extract a single action as a DDL command. A zero or the value DBMS_ADVISOR.ADVISOR_ALL indicates all actions for the recommendation would be included. The default is to include all actions for a recommendation.

Return Values

Returns the script as a CLOB buffer.

Usage Notes

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.

Examples

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;
/

---

IMPORT_SQLWKLD_SCHEMA Procedure

This procedure constructs and loads a SQL workload based on schema evidence. The workload is also referred to as a hypothetical workload.

Syntax

DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA (
   workload_name         IN VARCHAR2,
   import_mode           IN VARCHAR2,
   priority              NUMBER := 2,
   saved_rows            OUT NUMBER,
   failed_rows           OUT NUMBER);

Parameters

Table 12-22 IMPORT_SQLWKLD_SCHEMA Procedure Parameters 

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload.
import_mode	Specifies the action to be taken when storing the workload. Possible values are: APPEND Indicates that the collected workload will be added to any existing workload in the task. NEW Indicates that the collected workload will be the exclusive workload for the task. If an existing workload is found, an exception will be thrown. REPLACE Indicates the collected workload will be the exclusive workload for the task. If an existing workload is found, it will be deleted prior to saving the new workload. The default value is NEW.
priority	Specifies the application priority for each statement that is saved in the workload object. The value must be one of the following: 1-HIGH, 2-MEDIUM, or 3-LOW.
total_rows	Returns the number of rows processed.
saved_rows	Returns the number of rows actually saved in the repository.

Return Values

This call returns the number of rows processed and the number of rows saved and failed as output parameters.

Usage Notes

To successfully import a hypothetical workload, the target schemas must contain dimension or primary/foreign key information.

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.

Examples

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;
/

---

IMPORT_SQLWKLD_SQLCACHE Procedure

This procedure creates a SQL workload from the current contents of the server's SQL cache.

Syntax

DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (
   workload_name         IN VARCHAR2,
   import_mode           IN VARCHAR2,
   priority              IN NUMBER := 2,
   saved_rows            OUT NUMBER,
   failed_rows           OUT NUMBER);

Parameters

Table 12-23 IMPORT_SQLWKLD_SQLCACHE Procedure Parameters 

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload.
import_mode	Specifies the action to be taken when storing the workload. Possible values are: APPEND Indicates that the collected workload will be added to any existing workload in the task. NEW Indicates that the collected workload will be the exclusive workload for the task. If an existing workload is found, an exception will be thrown. REPLACE Indicates the collected workload will be the exclusive workload for the task. If an existing workload is found, it will be deleted prior to saving the new workload. The default value is NEW.
priority	Specifies the application priority for each statement that is saved in the workload object. The value must be one of the following: 1-HIGH, 2-MEDIUM, or 3-LOW.
saved_rows	Returns number of rows processed and the number of rows saved as output parameters.
failed_rows	Returns the number of rows that were not saved due to syntax or validation errors.

Return Values

This call returns the number of rows processed and the number of rows saved and failed as output parameters.

Usage Notes

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.

Examples

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;
/

---

IMPORT_SQLWKLD_STS Procedure

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.

Syntax

DBMS_ADVISOR.IMPORT_SQLWKLD_STS (
   workload_name         IN VARCHAR2,
   sqlset_name           IN VARCHAR2,
   import_mode           IN VARCHAR2,
   priority              IN NUMBER := 2,
   saved_rows            OUT NUMBER,
   failed_rows           OUT NUMBER);

Parameters

Table 12-24 IMPORT_SQLWKLD_STS Procedure Parameters 

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload.
sqlset_name	The name of an existing SQL Tuning Set workload from which the data will be imported.
import_mode	Specifies the action to be taken when storing the workload. Possible values are: APPEND Indicates that the collected workload will be added to any existing workload in the task. NEW Indicates that the collected workload will be the exclusive workload for the task. If an existing workload is found, an exception will be thrown. REPLACE Indicates the collected workload will be the exclusive workload for the task. If an existing workload is found, it will be deleted prior to saving the new workload. The default value is NEW.
priority	Specifies the application priority for each statement that is saved in the workload object. The value must be one of the following: 1-HIGH, 2-MEDIUM, or 3-LOW. The default value is 2.
saved_rows	Returns the number of rows actually saved in the repository.
failed_rows	Returns the number of rows that were not saved due to syntax or validation errors.

Return Values

This call returns the number of rows processed and the number of rows saved and failed as output parameters.

Usage Notes

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.

Examples

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;
/

---

IMPORT_SQLWKLD_SUMADV Procedure

This procedure collects a SQL workload from a Summary Advisor workload. This procedure is intended to assist 9i Summary Advisor users in the migration to SQLAccess Advisor.

Syntax

DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV (
   workload_name         IN VARCHAR2,
   import_mode           IN VARCHAR2,
   priority              IN NUMBER := 2,
   sumadv_id             IN NUMBER,
   saved_rows            OUT NUMBER,
   failed_rows           OUT NUMBER);

Parameters

Table 12-25 IMPORT_SQLWKLD_SUMADV Procedure Parameters 

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload.
import_mode	Specifies the action to be taken when storing the workload. Possible values are: APPEND Indicates that the collected workload will be added to any existing workload in the task. NEW Indicates that the collected workload will be the exclusive workload for the task. If an existing workload is found, an exception will be thrown. REPLACE Indicates the collected workload will be the exclusive workload for the task. If an existing workload is found, it will be deleted prior to saving the new workload. The default value is NEW.
priority	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 SQLAccess Advisor priority. The value must be one of the following: 1-HIGH, 2-MEDIUM, or 3-LOW.
sumadv_id	Specifies the Summary Advisor workload identifier number.
saved_rows	Returns the number of rows actually saved in the repository.
failed_rows	Returns the number of rows that were not saved due to syntax or validation errors.

Return Values

This call returns the number of rows processed and the number of rows saved and failed as output parameters.

Usage Notes

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.

Examples

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;
/

---

IMPORT_SQLWKLD_USER Procedure

This procedure collects a SQL workload from a specified user table.

Syntax

DBMS_ADVISOR.IMPORT_SQLWKLD_USER (
   workload_name         IN VARCHAR2,
   import_mode           IN VARCHAR2,
   owner                 IN VARCHAR2,
   table                 IN VARCHAR2,
   saved_rows            OUT NUMBER,
   failed_rows           OUT NUMBER);

Parameters

Table 12-26 IMPORT_SQLWKLD_USER Procedure Parameters

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload.
import_mode	Specifies the action to be taken when storing the workload. Possible values are: APPEND Indicates that the collected workload will be added to any existing workload in the task. NEW Indicates that the collected workload will be the exclusive workload for the task. If an existing workload is found, an exception will be thrown. REPLACE Indicates the collected workload will be the exclusive workload for the task. If an existing workload is found, it will be deleted prior to saving the new workload. The default value is NEW.
owner	Specifies the owner name of the table or view from which workload data will be collected.
table	Specifies the name of the table or view from which workload data will be collected.
saved_rows	Returns the number of rows actually saved in the workload object.
failed_rows	Returns the number of rows that were not saved due to syntax or validation errors.

Return Values

This call returns the number of rows processed and the number of rows saved and failed as output parameters.

Usage Notes

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.

Examples

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;
/

---

INTERRUPT_TASK Procedure

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.

Syntax

DBMS_ADVISOR.INTERRUPT_TASK (
   task_name          IN VARCHAR2);

Parameters

Table 12-27 INTERRUPT_TASK Procedure Parameters

Parameter	Description
task_name	A single Advisor task name that will be interrupted.

Examples

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.INTERRUPT_TASK('My Task');
END;
/

---

MARK_RECOMMENDATION Procedure

This procedure marks a recommendation for import or implementation.

Syntax

DBMS_ADVISOR.MARK_RECOMMENDATION (
   task_name          IN VARCHAR2
   id                 IN NUMBER,
   action             IN VARCHAR2);

Parameters

Table 12-28 MARK_RECOMMENDATION Procedure Parameters

Parameter	Description
task_name	Name of the task.
id	The recommendation identifier number assigned by the Advisor.
action	The recommendation action setting. The possible actions are: ACCEPT: Marks the recommendation as accepted. With this setting, the recommendation will appear in implementation and undo scripts. IGNORE: Marks the recommendation as ignore. With this setting, the recommendation will not appear in an implementation or undo script. IMPLEMENTED: Marks the recommendation as implemented. With this setting, a user can manage which recommendations have been successfully implemented. The implementation script can also set this value. REJECT: Marks the recommendation as rejected. With this setting, the recommendation will not appear in any implementation or undo scripts.

Usage Notes

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.

Examples

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;
/

---

QUICK_TUNE Procedure

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.

Syntax

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);

Parameters

Table 12-29 QUICK_TUNE Procedure Parameters

Parameter	Description
advisor_name	Name of the Advisor that will perform the analysis.
task_name	Name of the task.
attr1	Advisor-specific attribute in the form of a CLOB variable.
attr2	Advisor-specific attribute in the form of a VARCHAR2 variable.
attr3	Advisor-specific attribute in the form of a NUMBER.
task_or_template	An optional task name of an existing task or task template.

Usage Notes

If indicated by the user, the final recommendations can be implemented by the procedure.

The task will be created using either a specified SQLAccess 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.

Examples

DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := 'My Task';

DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name,
                  'SELECT avg(amount_sold) FROM sh.sales WHERE promo_id=10');
END;
/

---

RESET_SQLWKLD Procedure

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.

Syntax

DBMS_ADVISOR.RESET_SQLWKLD (
   workload_name        IN VARCHAR2);

Parameters

Table 12-30 RESET_SQLWKLD Procedure Parameters

Parameter	Description
workload_name	The SQL Workload object name that uniquely identifies an existing workload.

Usage Notes

RESET_SQLWKLD should be executed after any workload adjustments such as adding or removing SQL statements.

Examples

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;
/

---

RESET_TASK Procedure

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.

Syntax

DBMS_ADVISOR.RESET_TASK (
   task_name          IN VARCHAR2);

Parameters

Table 12-31 RESET_TASK Procedure Parameters

Parameter	Description
task_name	The task name that uniquely identifies an existing task.

Examples

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 WHERE promo_id = 10');
DBMS_ADVISOR.EXECUTE_TASK(task_name);

DBMS_ADVISOR.RESET_TASK(task_name);
END;
/

---

SET_DEFAULT_SQLWKLD_PARAMETER Procedure

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.

Syntax

DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER (
   parameter            IN VARCHAR2,
   value                IN VARCHAR2);

DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER (
   parameter            IN VARCHAR2,
   value                IN NUMBER);

Parameters

Table 12-32 SET_DEFAULT_SQLWKLD_PARAMETER Procedure Parameters

Parameter	Description
parameter	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.
value	The value of the specified parameter. The value can be specified as a string or a number. If the value is DBMS_ADVISOR.DEFAULT, the value will be reset to the default value.

Usage Notes

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.

Examples

BEGIN
DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER('VALID_TABLE_LIST','SH.%');
END;
/

---

SET_DEFAULT_TASK_PARAMETER Procedures

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.

Syntax

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);

Parameters

Table 12-33 SET_DEFAULT_TASK_PARAMETER Procedure Parameters

Parameter	Description
advisor_name	Specifies the unique advisor name as defined in the view DBA_ADVISOR_DEFINITIONS.
parameter	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
value	The value of the specified task parameter. The value can be specified as a string or a number.

Examples

BEGIN
DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(DBMS_ADVISOR.SQLACCESS_ADVISOR,
   'VALID_TABLE_LIST', 'SH.%');
END;
/

---

SET_SQLWKLD_PARAMETER Procedure

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.

Syntax

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);

Parameters

Table 12-34 SET_SQLWKLD_PARAMETER Procedure Parameters

Parameter	Description
workload_name	The SQL Workload object name that uniquely identifies an existing workload.
parameter	The name of the data parameter to be modified. Parameter names are not case sensitive.
value	The value of the specified parameter. The value can be specified as a string or a number. If the value is DBMS_ADVISOR.DEFAULT,the value will be reset to the default value.

Usage Notes

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.

Examples

DECLARE
  workload_name VARCHAR2(30);
BEGIN
  workload_name := 'My Workload';

DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(workload_name, 'VALID_TABLE_LIST','SH.%');
END;
/

---

SET_TASK_PARAMETER Procedures

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.

Syntax

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);

Parameters

Table 12-35 SET_TASK_PARAMETER Procedure Parameters

Parameter	Description
task_name	The Advisor task name that uniquely identifies an existing task.
parameter	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.
value	The value of the specified task parameter. The value can be specified as a string or a number. If the value is DEFAULT, the value will be reset to the default value.

Usage Notes

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.

Examples

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;
/

---

TUNE_MVIEW Procedure

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.

Syntax

DBMS_ADVISOR.TUNE_MVIEW (
   task_name IN OUT VARCHAR2, 
   mv_create_stmt IN [CLOB | VARCHAR2]);

Parameters

Table 12-36 TUNE_MVIEW Procedure Parameters

Parameter	Description
task_name	The task name for looking up the results in a catalog view. If not specified, the system will generate a name and return.
mv_create_stmt	The original materialized view creation statement.

Usage Notes

Executing 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 12-37 USER_TUNE_MVIEW and DBA_TUNE_MVIEW Views 

Column Name	Column Description
OWNER	The materialized view owner's name
TASK_NAME	The task name as a key to access the set of recommendations
SCRIPT_TYPE	Recommendation ID used to indicate the row is for IMPLEMENTATION or UNDO script.
ACTION_ID	Action ID used as the command order number
STATEMENT	For TUNE_MVIEW output, this column represents the following statements, and includes statement properties such as REFRESH and REWRITE options: CREATE MATERIALIZED VIEW LOG ALTER MATERIALIZED VIEW LOG FORCE [CREATE | DROP] MATERIALIZED VIEW

Examples

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.

---

UPDATE_OBJECT Procedure

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.

Syntax

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);

Parameters

Table 12-38 UPDATE_OBJECT Procedure Parameters

Parameter	Description
task_name	A valid advisor task name that uniquely identifies an existing task.
object_id	The advisor-assigned object identifier.
attr1	Advisor-specific data. If set to NULL, there will be no effect on the target object.
attr2	Advisor-specific data. If set to NULL, there will be no effect on the target object.
attr3	Advisor-specific data. If set to NULL, there will be no effect on the target object.
attr4	Advisor-specific data. If set to NULL, there will be no effect on the target object.
attr5	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.

Usage Notes

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.

Examples

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;
/

---

UPDATE_REC_ATTRIBUTES Procedure

This procedure updates the owner, name, and tablespace for a recommendation.

Syntax

DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES (
   task_name            IN VARCHAR2
   rec_id               IN NUMBER,
   action_id            IN NUMBER,
   attribute_name       IN VARCHAR2,
   value                IN VARCHAR2);

Parameters

Table 12-39 UPDATE_REC_ATTRIBUTES Procedure Parameters

Parameter	Description
task_name	The task name that uniquely identifies an existing task.
rec_id	The Advisor-generated identifier number that is assigned to the recommendation.
action_id	The Advisor-generated action identifier that is assigned to the particular command.
attribute_name	Name of the attribute to be changed. The valid values are: owner The new owner of the object. name The new name of the object. tablespace The new tablespace for the object.
value	Specifies the new value for the recommendation attribute.

Usage Notes

Recommendation attributes cannot be modified unless the task has successfully executed.

Examples

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;
/

---

UPDATE_SQLWKLD_ATTRIBUTES Procedure

This procedure changes various attributes of a SQL Workload object or template.

Syntax

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,
   source               IN VARCHAR2 := NULL);

Parameters

Table 12-40 UPDATE_SQLWKLD_ATTRIBUTES Procedure Parameters

Parameter	Description
workload_name	The workload object name that uniquely identifies an existing workload.
new_name	The new workload object name. If the value is NULL or contains the value ADVISOR_UNUSED, the workload will not be renamed. A task name can be up to 30 characters long.
description	A new workload description. If the value is NULL or contains the value ADVISOR_UNUSED, the description will not be changed. Names can be up to 256 characters long.
read_only	Set to TRUE so it cannot be changed.
is_template	TRUE if workload is to be used as a template.
source	Indicates a source application name that initiated the workload creation. If the value is NULL or contains the value ADVISOR_UNUSED, the source will not be changed.

Examples

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;
/

---

UPDATE_SQLWKLD_STATEMENT Procedure

This procedure updates an existing SQL statement in a specified SQL workload.

Syntax

DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT (
   workload_name     IN VARCHAR2,
   updated           OUT NUMBER,
   action            IN VARCHAR2,
   priority          IN NUMBER,
   username          IN VARCHAR2,
   search            IN VARCHAR2);

DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT (
   workload_name     IN VARCHAR2,
   sql_id            IN NUMBER,
   module            IN VARCHAR2,
   action            IN VARCHAR2,
   priority          IN NUMBER,
   username          IN VARCHAR2);

Parameters

Table 12-41 UPDATE_SQLWKLD_STATEMENT Procedure Parameters 

Parameter	Description
workload_name	The SQL Workload object name that uniquely identifies an existing workload.
sql_id	The Advisor-generated identifier number that is assigned to the statement. To specify all workload statements, use the constant DBMS_ADVISOR. ADVISOR_ALL.
updated	Returns the number of statements changed by a searched update.
module	Specifies a business application name that will be associated with the SQL statement. If the value is NULL or contains the value ADVISOR_UNUSED, then the column will not be updated in the repository.
action	Specifies the application action for the statement. If the value is NULL or contains the value ADVISOR_UNUSED, then the column will not be updated in the repository.
priority	The relative priority of the SQL statement. The value must be one of the following: 1 - HIGH, 2 - MEDIUM, or 3 - LOW If the value is NULL or contains the value ADVISOR_UNUSED, then the column will not be updated in the repository.
username	The Oracle user name that executed the SQL statement. If the value is NULL or contains the value ADVISOR_UNUSED, then the column will not be updated in the repository. 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 SCOTT is the executing user, then you must provide the user identifier SCOTT in all uppercase letters. It will not recognize the user scott as a match for SCOTT.
search	An optional SQL predicate that allows the user to refine the workload entries that will be updated. The WHERE keyword should not be included in the text.

Usage Notes

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.

Examples

DECLARE
  workload_name VARCHAR2(30);
  updated 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');

DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT(workload_name, 'module = ''MONTHLY''',
   updated, 'YEARLY', NULL, NULL,NULL);
END;
/

---

UPDATE_TASK_ATTRIBUTES Procedure

This procedure changes various attributes of a task or a task template.

Syntax

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,
   source             IN VARCHAR2 := NULL);

Parameters

Table 12-42 UPDATE_TASK_ATTRIBUTES Procedure Parameters

Parameter	Description
task_name	The Advisor task name that uniquely identifies an existing task.
new_name	The new Advisor task name. If the value is NULL or contains the value ADVISOR_UNUSED, the task will not be renamed. A task name can be up to 30 characters long.
description	A new task description. If the value is NULL or contains the value ADVISOR_UNUSED, the description will not be changed. Names can be up to 256 characters long.
read_only	Sets the task to read-only. Possible values are: TRUE and FALSE If the value is NULL or contains the value ADVISOR_UNUSED, the setting will not be changed.
is_template	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: TRUE and FALSE. If the value is NULL or contains the value ADVISOR_UNUSED, the setting will not be changed.
source	Indicates a source application name that initiated the task creation. If the value is NULL or contains the value ADVISOR_UNUSED, the source will not be changed.

Examples

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;
/