17 SQLAccess Advisor

Overview of Using the SQLAccess Advisor

One of the easiest ways to use the SQLAccess Advisor is to invoke its wizard, which is available in Oracle Enterprise Manager from the Advisor Central page. If you prefer to use SQLAccess Advisor through the DBMS_ADVISOR package, this section describes the basic components and the sequence in which the various procedures must be called.

This section describes the four steps in generating a set of recommendations:

• Create a task

• Define the workload

• Generate the recommendations

• View and implement the recommendations

Step 1 Create a task

Before any recommendations can be made, a task must be created. The task is important because it is where all information relating to the recommendation process resides, including the results of the recommendation process. If you use the wizard in Oracle Enterprise Manager or the DBMS_ADVISOR.QUICK_TUNE procedure, the task is created automatically for you. In all other cases, you must create a task using the DBMS_ADVISOR.CREATE_TASK procedure.

You can control what a task does by defining parameters for that task using the DBMS_ADVISOR.SET_TASK_PARAMETER procedure.

See "Creating Tasks" for more information about creating tasks.

Step 2 Define the workload

The workload is one of the primary inputs for the SQLAccess Advisor, and it consists of one or more SQL statements, plus various statistics and attributes that fully describe each statement. If the workload contains all SQL statements from a target business application, the workload is considered a full workload; if the workload contains a subset of SQL statements, it is known as a partial workload. The difference between a full and a partial workload is that in the former case, the SQLAccess Advisor may recommend dropping certain existing materialized views and indexes if it finds that they are not being used effectively.

Typically, the SQLAccess Advisor uses the workload as the basis for all analysis activities. Although the workload may contain a wide variety of statements, it carefully ranks the entries according to a specific statistic, business importance, or a combination of statistics and business importance. This ranking is critical in that it enables the SQLAccess Advisor to process the most important SQL statements ahead of those with less business impact.

For a collection of data to be considered a valid workload, the SQLAccess Advisor may require particular attributes to be present. Although analysis can be performed if some of the items are missing, the quality of the recommendations may be greatly diminished. For example, the SQLAccess Advisor requires a workload to contain a SQL query and the user who executed the query. All other attributes are optional; however, if the workload also contained I/O and CPU information, then SQLAccess Advisor may be able to better evaluate the current efficiency of the statement. The workload is stored as a separate object, which is created using the DBMS_ADVISOR.CREATE_SQLWKLD procedure, and can easily be shared among many Advisor tasks. Because the workload is independent, it must be linked to a task using the DBMS_ADVISOR.ADD_SQLWKLD_REF procedure. Once this link has been established, the workload cannot be deleted or modified until all Advisor tasks have removed their dependency on the workload. A workload reference will be removed when a parent Advisor task is deleted or when the workload reference is manually removed from the Advisor task by the user using the DBMS_ADVISOR.DELETE_SQLWKLD_REF procedure.

You can use the SQLAccess Advisor without a workload, however, for best results, a workload must be provided in the form of a user-supplied table, SQL Tuning Set or imported from the SQL Cache. If a workload is not provided, the SQLAccess Advisor can generate and use a hypothetical workload based on the dimensions defined in your schema.

Once the workload is loaded into the repository or at the time the recommendations are generated, a filter can be applied to the workload to restrict what is analyzed. This provides the ability to generate different sets of recommendations based on different workload scenarios.

The recommendation process and customization of the workload are controlled by SQLAccess Advisor parameters. These parameters control various aspects of the recommendation process, such as the type of recommendation that is required and the naming conventions for what it recommends. With respect to the workload, parameters control how long the workload exists and what filtering is to be applied to the workload.

To set these parameters, use the SET_TASK_PARAMETER and SET_SQLWKLD_PARAMETER procedures. Parameters are persistent in that they remain set for the lifespan of the task or workload object. When a parameter value is set using the SET_TASK_PARAMETER procedure, it does not change until you make another call to SET_TASK_PARAMETER.

See "Defining the Contents of a Workload" for more information about workloads.

Step 3 Generate the recommendations

Once a task exists and a workload is linked to the task and the appropriate parameters are set, you can generate recommendations using the DBMS_ADVISOR.EXECUTE_TASK procedure. These recommendations are stored in the SQLAccess Advisor Repository.

The recommendation process generates a number of recommendations and each recommendation will comprise of one or more actions. For example, create a materialized view and then analyze it to gather statistical information.

A task recommendation can range from a simple suggestion to a complex solution that requires implementation of a set of database objects such as indexes, materialized views, and materialized view logs. When an Advisor task is executed, it carefully analyzes collected data and user-adjusted task parameters. The SQLAccess Advisor will then attempt to form a resolution based on its built-in knowledge. The resolutions are then refined and stored in the form of a structured recommendation that can be viewed and implemented by the user.

See "Generating Recommendations" for more information about generating recommendations.

Step 4 View and implement the recommendations

There are two ways to view the recommendations from the SQLAccess Advisor: using the catalog views or by generating a script using the DBMS_ADVISOR.GET_TASK_SCRIPT procedure. In Enterprise Manager, the recommendations may be displayed once the SQLAccess Advisor process has completed.

See "Viewing the Recommendations" for a description of using the catalog views to view the recommendations. See "Generating SQL Scripts" to see how to create a script.

Not all recommendations have to be accepted and you can mark the ones that should be included in the recommendation script.

The final step is then implementing the recommendations and verifying that query performance has improved.

SQLAccess Advisor Flowchart

Figure 17-2 illustrates the steps in using the SQLAccess Advisor as well as an overview of all of the parameters in the SQLAccess Advisor and when it is appropriate to use them.

Figure 17-2 SQLAccess Advisor Flowchart

Description of the illustration dwhsg109.gif

SQLAccess Advisor Privileges

You need to have the ADVISOR privilege to manage or use the SQLAccess Advisor. When processing a workload, the SQLAccess Advisor attempts to validate each statement in order to identify table and column references. Validation is achieved by processing each statement as if it is being executed by the statement's original user. If that user does not have SELECT privileges to a particular table, the SQLAccess Advisor bypasses the statement referencing the table. This can cause many statements to be excluded from analysis. If the SQLAccess Advisor excludes all statements in a workload, the workload is invalid and the SQLAccess Advisor returns the following message:

QSM-00774, there are no SQL statements to process for task TASK_NAME

To avoid missing critical workload queries, the current database user must have SELECT privileges on the tables targeted for materialized view analysis. For those tables, these SELECT privileges cannot be obtained through a role.

Creating Tasks

An Advisor task is where you define what it is you want to analyze and where the results of this analysis should go. A user can create any number of tasks, each with its own specialization. All are based on the same Advisor task model and share the same repository.

You create a task using the CREATE_TASK procedure. The syntax is as follows:

DBMS_ADVISOR.CREATE_TASK (
   advisor_name          IN VARCHAR2,
   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');

The following illustrates an example of using this procedure:

VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);

See PL/SQL Packages and Types Reference for more information regarding the CREATE_TASK and CREATE_SQLWKLD procedures and their parameters.

SQLAccess Advisor Templates

When an ideal configuration for a task or workload has been identified, this configuration can be saved as a template upon which future tasks and workloads can be based.

This enables you to set up any number of tasks or workloads that can be used as intelligent starting points or templates for future task creation. By setting up a template, you can save time when performing tuning analysis. It also enables you to custom fit a tuning analysis to the business operation.

To create a task from a template, you specify the template to be used when a new task is created. At that time, the SQLAccess Advisor copies the data and parameter settings from the template into the newly created task. You can also set an existing task to be a template by setting the template attribute when creating the task or later using the UPDATE_TASK_ATTRIBUTE procedure.

To use a task as a template, you tell the SQLAccess Advisor to use a task when a new task is created. At that time, the SQLAccess Advisor copies the task template's data and parameter settings into the newly created task. You can also set an existing task to be a template by setting the template attribute.

A workload object can also be used as a template for creating new workload objects. Following the same guidelines for using a task as a template, a workload object can benefit from having a well-defined starting point. Like a task template, a template workload object can only be used to create similar workload objects.

Creating Templates

You can create a template as in the following example.

1. Create a template called MY_TEMPLATE.

   VARIABLE template_id NUMBER;
   VARIABLE template_name VARCHAR2(255);
   EXECUTE :template_name := 'MY_TEMPLATE';
   EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor',:template_id, -
                                    :template_name, is_template => 'TRUE');
   
   

2. Set template parameters. For example, the following sets the naming conventions for recommended indexes and materialized views and the default tablespaces:

   -- set naming conventions for recommended indexes/mvs
   EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
      :template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_<SEQ>');
   
   EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
      :template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_<SEQ>');
   
   -- set default tablespace for recommended indexes/mvs
   EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
      :template_name, 'DEF_INDEX_TABLESPACE', 'SH_INDEXES');
   
   EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
      :template_name, 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');
   
   

3. This template can now be used as a starting point to create a task as follows:

   VARIABLE task_id NUMBER;
   VARIABLE task_name VARCHAR2(255);
   EXECUTE :task_name := 'MYTASK';
   EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, -
                                    :task_name, template=>'MY_TEMPLATE');
   
   

   The following example uses a pre-defined template SQLACCESS_WAREHOUSE. See Table 17-4 for more information.

   EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', -
      :task_id, :task_name,  template=>'SQLACCESS_WAREHOUSE');
   

Workload Objects

Because the workload is stored as a separate workload object, it can easily be shared among many Advisor tasks. Once a workload object has been referenced by an Advisor task, a workload object cannot be deleted or modified until all Advisor tasks have removed their dependency on the data. A workload reference will be removed when a parent Advisor task is deleted or when the workload reference is manually removed from the Advisor task by the user.

The SQLAccess Advisor performs best when a workload based on usage is available. The SQLAccess Workload Repository is capable of storing multiple workloads, so that the different uses of a real-world data warehousing or transaction processing environment can be viewed over a long period of time and across the life cycle of database instance startup and shutdown.

Before the actual SQL statements for a workload can be defined, the workload must be created using the CREATE_SQLWKLD procedure. Then, the workload is loaded using the appropriate IMPORT_SQLWKLD procedure. A specific workload can be removed by calling the DELETE_SQLWKLD procedure and passing it a valid workload name. To remove all workloads for the current user, call DELETE_SQLWKLD and pass the constant value ADVISOR_ALL or %.

Managing Workloads

The CREATE_SQLWKLD procedure creates a workload and it must exist prior to performing any other workload operations, such as importing or updating SQL statements. The workload is identified by its name, so you should define a name that is unique and is relevant for the operation.

Its syntax is as follows:

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

The following examples illustrate using this procedure:

VARIABLE workload_name VARCHAR2(255);
EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name,'This is my first workload');

See PL/SQL Packages and Types Reference for more information regarding the CREATE_SQLWKLD procedure and its parameters.

Linking a Task and a Workload

Before the recommendation process can begin, the task must be linked to a workload. You achieve this by using the ADD_SQKLWKLD_REF procedure and the task and workload are linked by using their respective names. This procedure establishes a link between the Advisor task and a workload. Once a connection between an Advisor task and a workload is made, the workload is protected from removal. The syntax is as follows:

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

The following example links the MYTASK task created to the MYWORKLOAD SQL workload.

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');

See PL/SQL Packages and Types Reference for more information regarding the ADD_SQLWKLD_REF procedure and its parameters.

Defining the Contents of a Workload

Once a workload has been created, it must then be populated with information. Ideally, a workload will consist of the SQL statements (unless it is a hypothetical workload) that are being used against the database. The SQLAccess Advisor can obtain its workload from the following sources:

• SQL Tuning Set

• Loading a User-Defined Workload

• Loading a SQL Cache Workload

• Using a Hypothetical Workload

• Using a Summary Advisor 9i Workload

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

VARIABLE sqlsetname  VARCHAR2(30);
VARIABLE workload_name VARCHAR2(30);
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
EXECUTE   :sqlsetname := 'MY_STS_WORKLOAD';
EXECUTE   :workload_name := 'MY_WORKLOAD';
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD (:workload_name);
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_STS (:workload_name , -
     :sqlsetname, 'NEW', 1, :saved_stmts, :failed_stmts);

DBMS_ADVISOR.IMPORT_SQLWKLD_USER (
   workload_name         IN VARCHAR2,
   import_mode           IN VARCHAR2,
   owner_name            IN VARCHAR2,
   table_name            IN VARCHAR2,
   saved_rows            OUT NUMBER,
   failed_rows           OUT NUMBER);

VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER( -
   'MYWORKLOAD', 'NEW', 'SH', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);

MODULE

VARCHAR2(64)

ACTION

VARCHAR2(64)

BUFFER_GETS

NUMBER

CPU_TIME

NUMBER

ELAPSED_TIME

NUMBER

DISK_READS

NUMBER

ROWS_PROCESSED

NUMBER

EXECUTIONS

NUMBER

OPTIMIZER_COST

NUMBER

LAST_EXECUTION_DATE

DATE

PRIORITY

NUMBER

SQL_TEXT

CLOB or LONG or VARCHAR2

STAT_PERIOD

NUMBER

USERNAME

VARCHAR(30)

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

VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (-
   'MYWORKLOAD', 'APPEND', 2, :saved_stmts, :failed_stmts);

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

VARIABLE workload_name VARCHAR2(255);
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
EXECUTE :workload_name := 'SCHEMA_WKLD';
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER (:workload_name, -
                                            VALID_TABLE_LIST, 'SH');
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA ( -
  :workload_name, 'NEW', 2, :saved_stmts, :failed_stmts);

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

-- Order statements by OPTIMIZER_COST
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( -
   'MYWORKLOAD', 'ORDER_LIST', 'OPTIMIZER_COST');
-- Max number of statements 3
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'SQL_LIMIT', 3);

SQL Workload Journal

During the import of a workload, various informational messages are recorded in the SQL Workload Journal. These can be viewed using the view USER_ADVISOR_SQLW_JOURNAL. The journal is useful to identify why some statements were filtered out of the workload. For example, if a certain SQL statement refers to invalid tables, tables with missing statistics or has privilege errors, the information will be recorded in the journal. The amount of information output can be controlled by setting the JOURNALING parameter.

You can turn journaling off before importing workload as follows:

EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNALING', 0);

To view only fatal messages:

EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNALING', 4);

See PL/SQL Packages and Types Reference for details of all the settings for the JOURNALING parameter.

The information in the journal is for diagnostic purposes only and subject to change in future releases. It should not be used within any application.

Adding SQL Statements to a Workload

An alternative to importing a workload is to manually specify the SQL statements and add them to your workload using the ADD_SQLWKLD_STATEMENT procedure. This procedure adds a SQL statement to the specified workload. The syntax is as follows:

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

See PL/SQL Packages and Types Reference for more information regarding the ADD_SQLWKLD_STATEMENT procedure and its parameters. The following example adds a single statement to the MYWORKLOAD workload.

VARIABLE sql_text VARCHAR2(400);
EXECUTE :sql_text := 'SELECT AVG(amount_sold) FROM sales';
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( -
   'MYWORKLOAD', 'MONTHLY', 'ROLLUP', priority=>1, executions=>10, -
    username => 'SH',  sql_text => :sql_text);

Deleting SQL Statements from a Workload

You can delete an existing SQL statement from a specified workload using the DELETE_SQLWKLD_STATEMENT procedure. Its syntax has two forms. The first form lets you delete a statement specified by a given sql_id.

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

The second form lets you delete statements that match a specified search condition.

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

The following example deletes from MYWORKLOAD with sql_id 10:

EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT('MYWORKLOAD', 10);

The following example deletes from MYWORKLOAD all statements that satisfy the condition executions less than 5:

VARIABLE deleted_stmts NUMBER;
EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT ( -
   'MYWORKLOAD', 'executions < 5', :deleted_stmts);

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. See PL/SQL Packages and Types Reference for more information regarding the DELETE_SQLWKLD_STATEMENT procedure and its parameters.

Changing SQL Statements in a Workload

You can modify SQL statements in a workload by using the UPDATE_SQLWKLD_STATEMENT procedure. This procedure updates an existing SQL statement in the specified workload.

The syntax takes two forms. The first form enables you to update a SQL statement by specifying its sql_id.

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

The second forms enables you to update all SQL statements satisfying a given search condition.

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

The following example changes the priority to 3 for statement id 10:

EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT('MYWORKLOAD', 10, priority=>3);
                                              

The following examples changes the priority to 3 for all statements in MYWORKLOAD that have executions less than 10. The count of updated statements is returned in the updated_stmts variable.

VARIABLE updated_stmts NUMBER;
EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( -
   'MYWORKLOAD', 'executions < 10', :updated_stmts, priority => 3);

See PL/SQL Packages and Types Reference for more information regarding the UPDATE_SQLWKLD_STATEMENT procedure and its parameters.

Maintaining Workloads

There are several other operations that can be performed upon a workload, including the following:

• Setting Workload Attributes

• Resetting Workloads

• Removing a Link Between a Workload and a Task

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

EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES ( -
   'MYWORKLOAD', read_only=> 'TRUE');

EXECUTE DBMS_ADVISOR.RESET_SQLWKLD('MYWORKLOAD');

EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');

Removing Workloads

When workloads are no longer needed, they can be removed using the procedure DELETE_SQLWKLD. You can delete all workloads or a specific collection, but a workload cannot be deleted if it is still linked to a task.

The following procedure is an example of removing a specific workload. It deletes an existing workload from the repository.

DBMS_ADVISOR.DELETE_SQLWKLD (workload_name  IN VARCHAR2);
EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD('MYWORKLOAD');

See PL/SQL Packages and Types Reference for more information regarding the DELETE_SQLWKLD procedure and its parameters.

Recommendation Options

Before recommendations can be generated, the parameters for the task must first be defined using the SET_TASK_PARAMETER procedure. If parameters are not defined, then the defaults are used.

You can set task parameters by using the SET_TASK_PARAMETER procedure. The syntax is as follows.

DBMS_ADVISOR.SET_TASK_PARAMETER (
   task_name           IN VARCHAR2
   parameter           IN VARCHAR2,
   value               IN VARCHAR2);

There are many task parameters and, to help identify the relevant ones, they have been grouped into categories in Table 17-2.

ACTION_LIST

DAYS_TO_EXPIRE

DEF_INDEX_OWNER

DML_VOLATILITY

MODULE_LIST

REPORT_DATE_FORMAT

DEF_INDEX_TABLESPACE

EVALUATION_ONLY

ORDER_LIST

JOURNALING

DEF_MVIEW_OWNER

EXECUTION_TYPE

SQL_LIMIT

DEF_MVIEW_TABLESPACE

MODE

START_TIME

DEF_MVLOG_TABLSPACE

REFRESH_MODE

USERNAME_LIST

INDEX_NAME_TEMPLATE

STORAGE_CHANGE

VALID_TABLE_LIST

MVIEW_NAME_TEMPLATE

CREATION_COST

WORKLOAD_SCOPE

MODULE_LIMIT

TIME_LIMIT

END_TIME

COMMENTED_FILTER_LIST

In the following example, set the storage change of task MYTASK to 100MB. This indicates 100MB of additional space for recommendations. A zero value would indicate that no additional space can be allocated. A negative value indicates that the advisor must attempt to trim the current space utilization by the specified amount.

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK','STORAGE_CHANGE', 100000000);

In the following example, we set the VALID_TABLE_LIST parameter to filter out all queries that do no consist of tables SH.SALES and SH.CUSTOMERS.

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   'MYTASK', 'VALID_TABLE_LIST', 'SH.SALES, SH.CUSTOMERS');

See PL/SQL Packages and Types Reference for more information regarding the SET_TASK_PARAMETER procedure and its parameters.

Generating Recommendations

You can generate recommendations by using the EXECUTE_TASK procedure with your task name. After the procedure finishes, you can check the DBA_ADVISOR_LOG table for the actual execution status and the number of recommendations and actions that have been produced. The recommendations can be queried by task name in {DBA, USER}_ADVISOR_RECOMMENDATIONS and the actions for these recommendations can be viewed by task in {DBA, USER}_ADVISOR_ACTIONS.

DBMS_ADVISOR.EXECUTE_TASK (task_name   IN VARCHAR2);

EXECUTE DBMS_ADVISOR.EXECUTE_TASK('MYTASK');

Viewing the Recommendations

Each recommendation generated by the SQLAccess Advisor can be viewed using several catalog views, such as (DBA, USER)_ADVISOR_RECOMMENDATIONS. However, it is easier to use the GET_TASK_SCRIPT procedure or use the SQLAccess Advisor in Enterprise Manager, which graphically displays the recommendations and provides hyperlinks to quickly see which SQL statements benefit from a recommendation. Each recommendation produced by the SQLAccess Advisor is linked to the SQL statement it benefits.

The following shows the recommendation (rec_id) produced by an Advisor run, with their rank and total benefit. The rank is a measure of the importance of the queries that the recommendation helps. The benefit is the total improvement in execution cost (in terms of optimizer cost) of all the queries using the recommendation.

VARIABLE workload_name VARCHAR2(255); 
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE :workload_name := 'MYWORKLOAD'; 

SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = :task_name;

    REC_ID       RANK    BENEFIT
---------- ---------- ----------
         1          2       2754
         2          3       1222
         3          1       5499
         4          4        594

To identify which query benefits from which recommendation, you can use the views DBA_* and USER_ADVISOR_SQLA_WK_STMTS. The precost and postcost numbers are in terms of the estimated optimizer cost (shown in EXPLAIN PLAN) without and with the recommended access structure changes, respectively. To see recommendations for each query, issue the following statement:

SELECT sql_id, rec_id, precost, postcost,
       (precost-postcost)*100/precost AS percent_benefit
FROM USER_ADVISOR_SQLA_WK_STMTS
WHERE TASK_NAME = :task_name AND workload_name = :workload_name;

    SQL_ID     REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
---------- ---------- ---------- ---------- ---------------
       121          1       3003        249      91.7082917
       122          2       1404        182       87.037037
       123          3       5503          4      99.9273124
       124          4        730        136       81.369863

Each recommendation consists of one or more actions, which must be implemented together to realize the benefit provided by the recommendation. The SQLAccess Advisor produces the following types of actions:

• CREATE|DROP|RETAIN MATERIALIZED VIEW

• CREATE|ALTER|RETAIN MATERIALIZED VIEW LOG

• CREATE|DROP|RETAIN INDEX

• GATHER STATS

The CREATE actions corresponds to new access structures. RETAIN recommendation indicate that existing access structures must be kept. DROP recommendations are only produced if the WORKLOAD_SCOPE parameter is set to FULL. The GATHER STATS action will generate a call to DBMS_STATS procedure to gather statistics on a newly generated access structure. Note that multiple recommendations may refer to the same action, however when generating a script for the recommendation, you will only see each action once.

In the following example, you can see how many distinct actions there are for this set of recommendations.

SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
FROM user_advisor_actions WHERE task_name = :task_name;

'ACTIONCOUNT        CNT   
------------ ----------  
Action Count          20

-- see the actions for each recommendations
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;

    REC_ID  ACTION_ID COMMAND
---------- ---------- ------------------------------
         1          5 CREATE MATERIALIZED VIEW LOG
         1          6 ALTER MATERIALIZED VIEW LOG
         1          7 CREATE MATERIALIZED VIEW LOG
         1          8 ALTER MATERIALIZED VIEW LOG
         1          9 CREATE MATERIALIZED VIEW LOG
         1         10 ALTER MATERIALIZED VIEW LOG
         1         11 CREATE MATERIALIZED VIEW
         1         12 GATHER TABLE STATISTICS
         1         19 CREATE INDEX
         1         20 GATHER INDEX STATISTICS
         2          5 CREATE MATERIALIZED VIEW LOG
         2          6 ALTER MATERIALIZED VIEW LOG
         2          9 CREATE MATERIALIZED VIEW LOG
         ...
    

Each action has several attributes that pertain to the properties of the access structure. The name and tablespace for each access structure when applicable are placed in attr1 and attr2 respectively. The space occupied by each new access structure is in num_attr1. All other attributes are different for each action.

Table 17-3 maps SQLAccess Advisor action information to the corresponding column in DBA_ADVISOR_ACTIONS.

The following PL/SQL procedure can be used to print out some of the attributes of the recommendations.

CONNECT SH/SH;
CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS 
CURSOR curs IS
  SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4
  FROM user_advisor_actions
  WHERE task_name = in_task_name
  ORDER BY action_id;
  v_action        number;
  v_command     VARCHAR2(32);
  v_attr1       VARCHAR2(4000);
  v_attr2       VARCHAR2(4000);
  v_attr3       VARCHAR2(4000);
  v_attr4       VARCHAR2(4000);
  v_attr5       VARCHAR2(4000);
BEGIN
  OPEN curs;
  DBMS_OUTPUT.PUT_LINE('=========================================');
  DBMS_OUTPUT.PUT_LINE('Task_name = ' || in_task_name);
  LOOP
     FETCH curs INTO  
       v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
   EXIT when curs%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action);
   DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);
   DBMS_OUTPUT.PUT_LINE('Attr1 (name)      : ' || SUBSTR(v_attr1,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr3             : ' || SUBSTR(v_attr3,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr4             : ' || v_attr4);
   DBMS_OUTPUT.PUT_LINE('Attr5             : ' || v_attr5);
   DBMS_OUTPUT.PUT_LINE('----------------------------------------');  
   END LOOP;   
   CLOSE curs;      
   DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');
END show_recm;
/
-- see what the actions are using sample procedure
set serveroutput on size 99999
EXECUTE show_recm(:task_name);
A fragment of a sample output from this procedure is as follows:
Task_name = MYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG 
Attr1 (name)      : "SH"."CUSTOMERS"
Attr2 (tablespace):
Attr3             : ROWID, SEQUENCE
Attr4             :  INCLUDING NEW VALUES
Attr5             :
----------------------------------------
..
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW
Attr1 (name)      : "SH"."SH_MV$$_0004"
Attr2 (tablespace): "SH_MVIEWS"
Attr3             : REFRESH FAST WITH ROWID
Attr4             : ENABLE QUERY REWRITE
Attr5             :
----------------------------------------
..
----------------------------------------
Action ID: 19
Command : CREATE INDEX
Attr1 (name)      : "SH"."SH_IDX$$_0013"
Attr2 (tablespace): "SH_INDEXES"
Attr3             : "SH"."SH_MV$$_0002"
Attr4             : BITMAP
Attr5             :

See PL/SQL Packages and Types Reference for details regarding Attr5 and Attr6.

Access Advisor Journal

During the analysis process (EXECUTE_TASK), the Access Advisor will save useful information regarding the analysis to a journal. The journal can be viewed using the view USER_ADVISOR_JOURNAL. The amount of information output varies depending on the setting of task parameter JOURNALING.

You can turn journaling off as follows:

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 0);

To view only informational messages:

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 1);

See PL/SQL Packages and Types Reference for details of all the settings for the journaling parameter.The information in the journal is for diagnostic purposes only and subject to change in future releases. It should not be used within any application.

Stopping the Recommendation Process

If the SQLAccess Advisor takes too long to make its recommendations using the procedure EXECUTE_TASK, you can stop it by calling the CANCEL_TASK procedures and passing in the task_name for this recommendation process. If you use CANCEL_TASK, no recommendations will be made.

DBMS_ADVISOR.CANCEL_TASK (task_name   IN  VARCHAR2);

EXECUTE DBMS_ADVISOR.CANCEL_TASK('MYTASK');

Marking Recommendations

By default, all SQLAccess Advisor recommendations are ready to be implemented, however, the user can choose to skip or exclude selected recommendations by using the MARK_RECOMMENDATION procedure. MARK_RECOMMENDATION allows the user to annotate a recommendation with a REJECT or IGNORE setting, which will cause the GET_TASK_SCRIPT to skip it when producing the implementation procedure.

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

The following example marks a recommendation with ID 2 as REJECT. This recommendation and any dependent recommendations will not appear in the script.

EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION('MYTASK', 2, 'REJECT');

See PL/SQL Packages and Types Reference for more information regarding the MARK_RECOMMENDATIONS procedure and its parameters.

Modifying Recommendations

Using the UPDATE_REC_ATTRIBUTES procedure, the SQLAccess Advisor names and assigns ownership to new objects such as indexes and materialized views during the analysis operation. However, it does not necessarily choose appropriate names, so you may manually set the owner, name, and tablespace values for new objects. For recommendations referencing existing database objects, owner and name values cannot be changed. The syntax is as follows:

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

The attribute_name parameter can take the following values:

• BASE_TABLE

  Specifies the base table reference for the recommended object.

• OWNER

  Specifies the owner name of the recommended object.

• NAME

  Specifies the name of the recommended object.

• TABLESPACE

  Specifies the tablespace of the recommended object.

The following example modifies the attribute TABLESPACE for recommendation ID 1, action ID 1 to SH_MVIEWS.

EXECUTE DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES('MYTASK', 1, 1, - 
                                           'TABLESPACE', 'SH_MVIEWS');

See PL/SQL Packages and Types Reference for more information regarding the UPDATE_REC_ATTRIBUTES procedure and its parameters.

Generating SQL Scripts

An alternative to querying the metadata to see the recommendations, is to create a script of the SQL statements for the recommendations, using the procedure GET_TASK_SCRIPT. The resulting script is an executable SQL file that can contain DROP, CREATE, and ALTER statements. For new objects, the names of the materialized views, materialized view logs, and indexes are auto-generated by using the user-specified name template. You should review the generated SQL script before attempting to execute it.

There are four task parameters that control the naming conventions (MVIEW_NAME_TEMPLATE and INDEX_NAME_TEMPLATE), the owner for these new objects (DEF_INDEX_OWNER and DEF_MVIEW_OWNER), and the tablespaces (DEF_MVIEW_TABLESPACE and DEF_INDEX_TABLESPACE).

The following example shows how to generate a CLOB containing the script for the recommendations.

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'), - 
               'ADVISOR_RESULTS', 'advscript.sql'); 

To save the script to a file, a directory path must be supplied so that the procedure CREATE_FILE knows where to store the script. In addition, read and write privileges must be granted on this directory. The following example shows how to save an advisor script CLOB to a file:

-- create a directory and grant permissions to read/write to it
CONNECT SH/SH;
CREATE DIRECTORY ADVISOR_RESULTS AS '/mydir';
GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;

The following is a fragment of a script generated by this procedure. The script also includes PL/SQL calls to gather stats on the recommended access structures and marks the recommendations as IMPLEMENTED at the end.

Rem  Access Advisor V10.0.0.0.0 - Beta
Rem  
Rem  Username:        SH
Rem  Task:            MYTASK
Rem  Execution date:  15/04/2003 11:35
Rem  
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE

CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS"
    WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS"
    ADD ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;
..
CREATE MATERIALIZED VIEW "SH"."MV$$_00510002"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, COUNT(*) M1 FROM
SH.CUSTOMERS WHERE (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA') GROUP
BY SH.CUSTOMERS.CUST_STATE_PROVINCE;
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('"SH"', '"MV$$_00510002"', NULL, 
     DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
..
CREATE BITMAP INDEX "SH"."MV$$_00510004_IDX$$_00510013"
    ON "SH"."MV$$_00510004" ("C4");
whenever sqlerror EXIT SQL.SQLCODE
BEGIN
  DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',1,'IMPLEMENTED');
  DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',2,'IMPLEMENTED');
  DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',3,'IMPLEMENTED');
  DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',4,'IMPLEMENTED');
END;
/

When Recommendations are No Longer Required

The RESET_TASK procedure resets a task to its initial starting point. This has the effect of removing all recommendations, and intermediate data from the task. The actual task status is set to INITIAL. The syntax is as follows:

DBMS_ADVISOR.RESET_TASK (task_name     IN VARCHAR2);

The following illustrates an example of using this procedure:

EXECUTE DBMS_ADVISOR.RESET_TASK('MYTASK');

See PL/SQL Packages and Types Reference for more information regarding the RESET_TASK procedure and its parameters.

Performing a Quick Tune

If you only want to tune a single SQL statement, the QUICK_TUNE procedure accepts as its input a task_name and a SQL statement. It will then create a task and workload and execute that task. There is no difference in the results from using QUICK_TUNE. They are exactly the same as those from using EXECUTE_TASK, but this approach is easier to use when there is only a single SQL statement to be tuned. The syntax is as follows:

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

The following example shows how to quick tune a single SQL statement:

VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
EXECUTE :sql_stmt := 'SELECT COUNT(*) FROM customers 
                      WHERE cust_state_province=''CA''';
EXECUTE :task_name  := 'MY_QUICKTUNE_TASK';
EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, -
              :task_name, :sql_stmt);

See PL/SQL Packages and Types Reference for more information regarding the QUICK_TUNE procedure and its parameters.

Managing Tasks

Every time recommendations are generated, tasks are created and, unless some maintenance is performed on these tasks, they will grow over time and will occupy storage space. There may be tasks that you want to keep and prevent accidental deletion. Therefore, there are several management operations that can be performed on tasks:

• Updating Task Attributes

• Deleting Tasks

• Setting DAYS_TO_EXPIRE

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

EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('MYTASK', 'TUNING1');

EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', read_only => 'TRUE');

EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', is_template=>'TRUE');

DBMS_ADVISOR.DELETE_TASK (task_name  IN VARCHAR2);

EXECUTE DBMS_ADVISOR.DELETE_TASK('MYTASK');

Using SQLAccess Advisor Constants

You can use the constants shown in Table 17-4 with the SQLAccess Advisor.

ADVISOR_ALL

ADVISOR_CURRENT

ADVISOR_DEFAULT

ADVISOR_UNLIMITED

ADVISOR_UNUSED

SQLACCESS_GENERAL

SQLACCESS_OLTP

SQLACCESS_WAREHOUSE

SQLACCESS_ADVISOR

Examples of Using the SQLAccess Advisor

This section illustrates some typical scenarios for using the SQLAccess Advisor. Oracle Database provides a script that contains this chapter's examples, aadvdemo.sql.

CONNECT SH/SH;
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2, 
'SELECT   t.week_ending_day, p.prod_subcategory, 
          SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
 FROM sales s, times t, products p WHERE s.time_id = t.time_id
 AND s.prod_id = p.prod_id AND s.prod_id > 10 AND s.prod_id < 50
 GROUP BY t.week_ending_day, p.prod_subcategory, 
          s.channel_id, s.promo_id')
/

-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2, 
 'SELECT   t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM     sales s , times t
  WHERE    s.time_id = t.time_id
  AND    s.time_id between TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'')
                       AND TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'')
GROUP BY t.calendar_month_desc')
/

--Load all SQL queries.
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2, 
'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
   SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA''
AND   ch.channel_desc IN (''Internet'',''Catalog'')
AND   t.calendar_quarter_desc IN (''1999-Q1'',''1999-Q2'')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc')
/

-- order by
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2, 
  'SELECT c.country_id, c.cust_city, c.cust_last_name
FROM customers c WHERE c.country_id IN (52790, 52789)
ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;

CONNECT SH/SH;
set serveroutput on;

VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;

EXECUTE :workload_name := 'MYWORKLOAD';

EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);

EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER (:workload_name, 'APPEND', 'SH', -

'USER_WORKLOAD', :saved_stmts, :failed_stmts);
PRINT :saved_stmts;
PRINT :failed_stmts;

EXECUTE :task_name := 'MYTASK';

EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'STORAGE_CHANGE', 100);

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   :task_name, 'EXECUTION_TYPE', 'INDEX_ONLY');

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

-- See the number of recommendations and the status of the task.

SELECT rec_id, rank, benefit
FROM user_advisor_recommendations WHERE task_name = :task_name;

-- See recommendation for each query.
SELECT sql_id, rec_id, precost, postcost,
      (precost-postcost)*100/precost AS percent_benefit
FROM user_advisor_sqla_wk_stmts
WHERE task_name = :task_name AND workload_name = :workload_name;

-- See the actions for each recommendations.
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions
WHERE task_name = :task_name
ORDER BY rec_id, action_id;

-- See what the actions are using sample procedure.
SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm(:task_name);

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-

'ADVISOR_RESULTS', 'Example1_script.sql');

CONNECT SH/SH;
VARIABLE template_id NUMBER;
VARIABLE template_name VARCHAR2(255);

EXECUTE :template_name := 'MY_TEMPLATE';

EXECUTE DBMS_ADVISOR.CREATE_TASK ( -
   'SQL Access Advisor',:template_id, :template_name, is_template=>'TRUE');

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   :template_name,  'INDEX_NAME_TEMPLATE', 'SH_IDX$$_<SEQ>');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   :template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_<SEQ>');

--Set default owners for recommended indexes/materialized views.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   :template_name, 'DEF_INDEX_OWNER', 'SH');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   :template_name, 'DEF_MVIEW_OWNER', 'SH');

--Set default tablespace for recommended indexes/materialized views.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   :template_name, 'DEF_INDEX_TABLESPACE', 'SH_INDEXES');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
   :template_name, 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');

VARIABLE task_id NUMBER;

VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( -
   'SQL Access Advisor', :task_id, :task_name, template => 'MY_TEMPLATE');

--See the parameter settings for task
SELECT parameter_name, parameter_value
FROM user_advisor_parameters
WHERE task_name = :task_name AND (parameter_name LIKE '%MVIEW%' 
   OR parameter_name LIKE '%INDEX%');

VARIABLE workload_name VARCHAR2(255);

VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);

EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( -

:workload_name, 'APPEND', 'SH', 'USER_WORKLOAD', :saved_stmts,:failed_stmts);

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-

'ADVISOR_RESULTS', 'Example2_script.sql');

CONNECT / AS SYSDBA

--Clear any prior contents of the cache.
ALTER SYSTEM FLUSH SHARED_POOL;
CONNECT SH/SH;

SELECT   t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM     sales s, times t WHERE s.time_id = t.time_id
AND  s.time_id between TO_DATE('01-JAN-2000', 'DD-MON-YYYY')
                       AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY')
GROUP BY t.calendar_month_desc;

-- Order by
SELECT c.country_id, c.cust_city, c.cust_last_name
FROM customers c WHERE c.country_id IN ('52790', '52789')
ORDER BY c.country_id, c.cust_city, c.cust_last_name;

-- Queries to illustrate filtering
CONNECT scott/tiger;
SELECT e.ename, d.dname
FROM emp e, dept d WHERE e.deptno = d.deptno;

SELECT COUNT(*) FROM dept;

CONNECT sh/sh
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;

EXECUTE :workload_name := 'MY_CACHE_WORKLOAD';

EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);

EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( -
   :workload_name, 'USERNAME_LIST', 'SH');

EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( -

:workload_name, 'APPEND', 2, :saved_stmts, :failed_stmts);
PRINT :saved_stmts;
PRINT :failed_stmts;

--See the workload statements in catalog views
SELECT num_select_stmt, create_date
FROM user_advisor_sqlw_sum
WHERE workload_name = :workload_name;

SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30)
FROM user_advisor_sqlw_stmts
WHERE workload_name = :workload_name
ORDER BY sql_id;

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT (:workload_name, username => 'SH', -

priority => 1, executions => 10,  sql_text =>  -
   'select count(*) from customers where cust_state_province=''CA''');

SELECT num_select_stmt, create_date
FROM user_advisor_sqlw_sum
WHERE workload_name = :workload_name;

VARIABLE updated_stmts NUMBER;

EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( -
   :workload_name, 'executions < 10', :updated_stmts, priority => 3);

PRINT :updated_stmts;

--See that the change has been made.
SELECT sql_id, username, executions, priority
FROM user_advisor_sqlw_stmts
WHERE workload_name = :workload_name
ORDER BY sql_id;

EXECUTE :task_name := 'MYTASK';

EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-

'ADVISOR_RESULTS', 'Example3_script.sql');

CONNECT SH/SH;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;

EXECUTE :workload_name := 'MYWORKLOAD';

EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);

EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( -

:workload_name, 'APPEND', 'SH','USER_WORKLOAD', :saved_stmts, :failed_stmts);

PRINT :saved_stmts;
PRINT :failed_stmts;

EXECUTE :task_name := 'MY_EVAL_TASK';

EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (:task_name, 'EVALUATION_ONLY', 'TRUE');

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

--See the number of recommendations and the status of the task.

SELECT rec_id, rank, benefit
FROM user_advisor_recommendations WHERE task_name = :task_name;

--See the actions for each recommendation.
SELECT rec_id, action_id, SUBSTR(command, 1, 30) AS command
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;

DBMS_ADVISOR.TUNE_MVIEW Procedure

This section discusses the following information:

• TUNE_MVIEW Syntax and Operations

• Accessing TUNE_MVIEW Output Results

• USER_TUNE_MVIEW and DBA_TUNE_MVIEW Views

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

CREATE DIRECTORY TUNE_RESULTS AS  '/tmp/script_dir';
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),- 
      'TUNE_RESULTS', 'mv_create.sql'); 
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name, - 
      'UNDO'), 'TUNE_RESULTS', 'mv_undo.sql');

VARIABLE task_cust_mv VARCHAR2(30);
VARIABLE create_mv_ddl VARCHAR2(4000);
EXECUTE :task_cust_mv := 'cust_mv';

EXECUTE :create_mv_ddl := ' -
CREATE MATERIALIZED VIEW cust_mv -
REFRESH FAST -
DISABLE QUERY REWRITE AS - 
SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount -
FROM sales s, customers cs -
WHERE s.cust_id = cs.cust_id -
GROUP BY s.prod_id, s.cust_id';

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

CREATE MATERIALIZED VIEW SH.CUST_MV
REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE AS
SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
          SUM("SH"."SALES"."AMOUNT_SOLD") M1,
          COUNT("SH"."SALES"."AMOUNT_SOLD") M2,
          COUNT(*) M3
     FROM SH.SALES, SH.CUSTOMERS
     WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID
     GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;

DROP MATERIALIZED VIEW SH.CUST_MV;

SELECT * FROM USER_TUNE_MVIEW 
WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION';

CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), -
   'TUNE_RESULTS', 'mv_create.sql');

EXECUTE :task_cust_mv := 'cust_mv2';

EXECUTE :create_mv_ddl := ' -
CREATE MATERIALIZED VIEW cust_mv -
ENABLE QUERY REWRITE AS -
SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount -
FROM sales s, customers cs, countries cn -
WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id -
AND cn.country_name IN (''USA'',''Canada'') -
GROUP BY s.prod_id, s.cust_id -
UNION -
SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount -
FROM sales s, customers cs -
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) -
GROUP BY s.prod_id, s.cust_id';

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" 
WITH ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."COUNTRIES"
    WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."COUNTRIES"
    ADD ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID","COUNTRY_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
 SUM("SH"."SALES"."AMOUNT_SOLD")
        M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES,
        SH.CUSTOMERS WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND
 (SH.SALES.CUST_ID
        IN (1012, 1010, 1005)) GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
 SH.COUNTRIES.COUNTRY_NAME
        C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES".
"AMOUNT_SOLD")
        M2, COUNT(*) M3 FROM SH.SALES, SH.CUSTOMERS, SH.COUNTRIES WHERE
 SH.CUSTOMERS.CUST_ID        = SH.SALES.CUST_ID AND SH.COUNTRIES.COUNTRY_ID = SH.CUSTOMERS.COUNTRY_ID
        AND (SH.COUNTRIES.COUNTRY_NAME IN ('USA', 'Canada')) GROUP BY
 SH.SALES.PROD_ID,
        SH.CUSTOMERS.CUST_ID, SH.COUNTRIES.COUNTRY_NAME;

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS  (SELECT "CUST_MV$SUB2"."C1" "PROD_ID","CUST_MV$SUB2"."C2" 
"CUST_ID",SUM("CUST_MV$SUB2"."M3")
        "CNT",SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2"
        "CUST_MV$SUB2" GROUP BY "CUST_MV$SUB2"."C1","CUST_MV$SUB2"."C2")UNION
        (SELECT "CUST_MV$SUB1"."C1" "PROD_ID","CUST_MV$SUB1"."C2" 
"CUST_ID",SUM("CUST_MV$SUB1"."M3")
        "CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1"
        "CUST_MV$SUB1" GROUP BY "CUST_MV$SUB1"."C1","CUST_MV$SUB1"."C2");

BEGIN
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ',
  'SELECT s.prod_id, s.cust_id, COUNT(*) cnt,
          SUM(s.amount_sold) sum_amount
   FROM sales s, customers cs, countries cn
   WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id
         AND cn.country_name IN (''USA'',''Canada'')
   GROUP BY s.prod_id, s.cust_id
   UNION
   SELECT s.prod_id, s.cust_id, COUNT(*) cnt,
          SUM(s.amount_sold) sum_amount
   FROM sales s, customers cs
   WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012)
   GROUP BY s.prod_id, s.cust_id',
  '(SELECT "CUST_MV$SUB2"."C3" "PROD_ID","CUST_MV$SUB2"."C2" "CUST_ID",
           SUM("CUST_MV$SUB2"."M3") "CNT",
           SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT" 
    FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2"
    GROUP BY "CUST_MV$SUB2"."C3","CUST_MV$SUB2"."C2")
   UNION
   (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID",
           "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
    FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1")',-1553577441)
END;
/;

DROP MATERIALIZED VIEW SH.CUST_MV$SUB1
DROP MATERIALIZED VIEW SH.CUST_MV$SUB2
DROP MATERIALIZED VIEW SH.CUST_MV
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('SH.CUST_MV$RWEQ')

SELECT * FROM USER_TUNE_MVIEW
WHERE TASK_NAME='cust_mv2'
AND SCRIPT_TYPE='IMPLEMENTATION';

CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WRITE ON DIRECTRY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv2'),-
   'TUNE_RESULTS', 'mv_create2.sql');

EXECUTE :task_cust_mv := 'cust_mv3';

EXECUTE :create_mv_ddl := '-
CREATE MATERIALIZED VIEW cust_mv -
REFRESH FAST ON DEMAND -
ENABLE QUERY REWRITE AS -
SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount -
FROM sales s, customers cs -
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (2005,1020) -
GROUP BY s.prod_id, s.cust_id UNION -
SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount -
FROM sales s, customers cs -
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) -
GROUP BY s.prod_id, s.cust_id';

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" 
  WITH ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD")
  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES"
  ADD ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD")
  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" 
  WITH ROWID, SEQUENCE ("CUST_ID")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS"
  ADD ROWID, SEQUENCE ("CUST_ID")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
  REFRESH FAST WITH ROWID
  ENABLE QUERY REWRITE AS
  SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, 
    SUM("SH"."SALES"."AMOUNT_SOLD") M1, 
    COUNT("SH"."SALES"."AMOUNT_SOLD")M2, COUNT(*) M3 
    FROM SH.CUSTOMERS, SH.SALES
    WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND
    (SH.SALES.CUST_ID IN (2005, 1020, 1012, 1010, 1005))
    GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID
CREATE MATERIALIZED VIEW SH.CUST_MV 
  REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS
  (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID",
    "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" 
    FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" 
    WHERE "CUST_MV$SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)
    UNION 
    (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID",
      "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" 
      FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" 
      WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR 
            "CUST_MV$SUB1"."C1"=1005)
      DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ',
      'SELECT s.prod_id, s.cust_id, COUNT(*) cnt, 
       SUM(s.amount_sold) sum_amount
       FROM sales s, customers cs
       WHERE s.cust_id = cs.cust_id AND s.cust_id in (2005,1020)
       GROUP BY s.prod_id, s.cust_id UNION
       SELECT s.prod_id, s.cust_id, COUNT(*) cnt,
       SUM(s.amount_sold) sum_amount
       FROM sales s, customers cs
       WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012)
       GROUP BY s.prod_id, s.cust_id',
      '(SELECT "CUST_MV$SUB1"."C2" "PROD_ID",
        "CUST_MV$SUB1"."C1" "CUST_ID",
        "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" 
        FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" 
        WHERE "CUST_MV$SUB1"."C1"=2005OR "CUST_MV$SUB1"."C1"=1020)
       UNION
       (SELECT "CUST_MV$SUB1"."C2" "PROD_ID",
        "CUST_MV$SUB1"."C1" "CUST_ID",
        "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
        FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" 
        WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR
              "CUST_MV$SUB1"."C1"=1005)',
      1811223110)

DROP MATERIALIZED VIEW SH.CUST_MV$SUB1
DROP MATERIALIZED VIEW SH.CUST_MV
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('SH.CUST_MV$RWEQ')

SELECT * FROM USER_TUNE_MVIEW 
WHERE TASK_NAME= 'cust_mv3'
AND SCRIPT_TYPE='IMPLEMENTATION';

CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv3'), -
   'TUNE_RESULTS', 'mv_create3.sql')