Using the Advisors for Tuning

Let's now look at how to use the SQL advisors for tuning by taking a tour using OEM. As indicated before, the advisors are available from a variety of sources including the Advisor Central link in most OEM screens.

OEM Interface to the SQL Tuning Advisor

Launch the SQL Tuning Advisor from its link on the Advisor Central page. This displays a screen that lists all the possible inputs to this advisor. The links are as follows:

• Top SQL. Useful for obtaining tuning advice for either Spot SQL or Period SQL. The former shows all the Top SQL executed in a five-minute period in the immediate past, while the latter shows all the Top SQL for the past 24 hours or so. These SQLs are sorted by some criteriain this case, activity percentageso it is easy to pick out the top few. Note that you can sort them by other criteria and use the slider to select a specific time period. An example is shown in Figure 13.1. Note that you can select a number of SQL statements after viewing their details by clicking on the SQL ID link.

  Figure 13.1. Spot SQL details seen when the Top SQL link in the SQL Tuning Advisor main page is clicked.

  
  
  

• SQL Tuning Sets. Click this link to view and create SQL tuning sets. In fact, the screen that is displayed allows you to create such sets from Spot SQL, Period SQL, or from high-load SQL seen either in previous snapshots or even in preserved snapshot sets. As shown in Figure 13.2, we created a SQL tuning set named SQL Tuning Set 2 with the description "Problematic period" from a few statements in the Top Spot SQL shown in Figure 13.1. Note that the details of the selected SQLs in SQL Tuning Set 2 are shown in a separate screen, as seen in Figure 13.3.

  Figure 13.2. SQL tuning set screen showing a freshly created tuning set from the Top SQL screen (note the launch buttons for further advisors).

  
  
  

  Figure 13.3. Details of the SQLs within SQL Tuning Set 2, sorted by elapsed time.

  
  
  

• Snapshots. Clicking this link takes you to the AWR Snapshots page shown in Chapter 11, "Effectively Using the Automatic Workload Repository." You can use the Snapshots page to select a pair or a set of snapshots that can be used to generate a SQL tuning set that will then be submitted to the SQL Tuning Advisor.

• Preserved Snapshot sets. This link is similar to the links in the snapshots page in Chapter 11, "Effectively Using the Automatic Workload Repository," where you can use a preserved snapshot set to generate a SQL tuning set.

When an SQL or a set of SQL statements is ready for submission, you can launch the SQL advisor from the launch points. This will bring you to the Schedule Advisor screen shown in Figure 13.4, which can be used to determine both the scope and schedule for that run. Note that two modes, limited and comprehensive, are available. In limited mode, analysis is conducted without the long-running SQL profile option. The comprehensive mode takes longer, but generates an SQL profile if appropriate.

Figure 13.4. Schedule Advisor screen.

Executing this schedule advisor for a SQL tuning set named "SQL Tuning Set from Spot SQL" displays the screen shown in Figure 13.5.

Figure 13.5. SQL Tuning Advisor results screen.

Note that the running of the analysis actually took only about two seconds, and a set of recommendations are shown. These recommendations are based on one or more of the four different areas of analysis mentioned before, namely statistics analysis, SQL profiling, access path analysis (indexing), and SQL structural analysis. When a recommendation is available for one of these areas, this is indicated by a check mark. For example, you see that the first and second queries could possibly be improved via the use of a SQL profile. A miscellaneous section as well as errors encountered are displayed (not shown in the figure). Clicking the View Recommendation button would show a screen that asks you to accept the SQL profile after viewing the old and new explain plans. An example of this was shown in detail in Chapter 10, "Adopting a New Approach to Tuning."

OEM Interface to the SQL Access Advisor

The SQL Access Advisor can be launched from a variety of screens, but we will consider the link from the Advisor Central screen. Clicking the SQL Access Advisor link found there opens the screen shown in Figure 13.6.

Figure 13.6. SQL Access Advisor input screen.

This screen is arranged into four distinct steps, where input and various options can be entered. These steps can be seen in the form of a workflow on top of the screen and involve specifying the workload source, recommendation options, and scheduling parameters, and finally viewing a review screen. All the options are not shown in the figures, but in this first screen, you can specify the source of the workload, along with some options. The source can be from a variety of inputs:

• SQL tuning set. The SQL Tuning set link in the second option in Figure 13.6 can be used to return to the screen shown in Figure 13.2, where you can select a SQL tuning set that is already stored and available or create a new input set using the procedure described previously.

• User-defined workload. In this source, you use the Torch icon to choose a table in a schema that contains at least the SQL_TEXT and a USERNAME column. You can use this option to run the advisor on a prepopulated set of SQL statements.

• Hypothetical load. This is the most intriguing of the choices. Basically, it enables a performance analyst to choose a schema or a set of tables that contain dimensions or primary and foreign key constraints. SQL Access Advisor can then generate recommendations for indexes and materialized views based on these relationships.

An advanced set of options is available; access them by clicking the + sign. These allow for the following options and filters:

• Choose the workload type. Opt for read-only, as in a data warehouse, or allow the advisor to determine the type depending on the SQL content of the workload.

• Include recommendations for dropping unused indexes. The default is no, assuming that other statements not in the workload may be affected by dropping the indexes.

• Filters. You can either evaluate the entire workload or use filters to reduce and focus the workload. These filters are in the form of Top N consumers on various criteria including optimizer cost, buffer gets, CPU time, physical reads, elapsed time, and execution count (SQL executed by a set of selected users, or those that access a set of tables). If you use the MODULE_ID and ACTION fields set by the DBMS_APPLICATION_INFO package to identify the source and current status of SQLs, these can be used in the filter as well.

When this is specified, you can move forward to the second page of inputs.

Identifying Unused Indexes

We recommend using index monitoring in order to identify unused indexes rather than allowing the SQL Access Advisor to identify them for you. Index monitoring was made available in Oracle 9i, and is enabled by default in Oracle Database 10g.We will deal with this topic in Chapter 14, but keep in mind that you should monitor the index usage over a sufficient periodpreferably over one or two complete business-cycle periods and events, such as month-end, quarter, or yearly closesbefore deciding to drop them.

In the second page, you can specify the following. The last three options are available when you click the + arrow for Show Advanced Options:

• Recommendation types. Specify whether the advisor should consider just indexes, just materialized views, or both.

• Advisor mode. The choices are Limited and Comprehensive. When the former is chosen, the workload will be processed quickly, potentially ignoring statements with an estimated cost below a threshold. In comprehensive mode, a complete and exhaustive analysis will be performed.

• Space restrictions. Because indexes and materialized views take up database space, you can specify an upper limit to the estimated space that may be used if the recommendations are implemented. We suggest that all recommendations be shown and that a performance analyst be allowed to make the choice later on.

• Tuning options. You can choose the criteria by which SQL statements will be prioritized for tuning. This is the same list as for the selection criteria. When a sort order other than the default, optimizer cost, is chosen, this becomes a secondary sort order.

  Bug in the Name Used for Submission

  It seems that the default advisor task name used in creating the submitted job is prefixed with the text ADV_. When a long name is used, the additional prefix overflows the available column space used for the job name in the DBMS_SCHEDULER call and an error, Failed to COMMIT, is displayed with message ORA-27452, ADV_Your_chosen_Advisor_task_name is an invalid name for a database object. For this reason, we suggest using a short but meaningful name rather than the default.

  
  

• Default storage locations. You can choose the default locations and schemas where the recommended indexes and materialized views could be created.

When this is complete, move on to the third page of inputs, where you can schedule the advisor run. First, you must specify a task name (to override the default name) and a description, and then use one of the following schedules. By default, the advisor is run immediately. If scheduled to run later, a database job is created and submitted to the database scheduler using the DBMS_SCHEDULE built-in PL/SQL package.

When a schedule is chosen, the scheduling parameters change as well in the input screen:

• Standard. You can choose to run the advisor immediately or at a later time using a calendar. You can also choose to repeat the analysis on a periodic basis, with periodicity ranging from minutes to years.

• Standard using PL/SQL for repeated intervals. This is similar to the Standard schedule, with the only difference being that you can use a PL/SQL expression to calculate the schedule.

• Predefined schedule. You can choose a predefined schedule that is available in the system.

• Predefined window. You can choose a predefined time period that is defined as a relatively inactive window in the system.

Once this entry is complete, you can move to the final submission page, which simply confirms all the inputs. Clicking the Submit button will create the task and set it up for execution as per the schedule.

Scheduling Constraints

When you schedule a resource-intensive analysis such as analyzing a hypothetical workload or generating advice for materialized views, you will not be able to schedule them immediately, even if the choice is allowed. Such analysis will always be scheduled during a time that the database determines is off hours based on AWR data.

When the SQL Access Advisor analysis is complete, you can view the results using links from the Advisor Central screen; select the completed run for the SQL Access Advisor using the drop-down list. Figure 13.7 shows a sample screen with the results of one such run.

Figure 13.7. SQL Access Advisor results screen.

You will be able to see the impact of each of the recommendations in a graphical form, so you can immediately zero in on the most effective ones. Below this graph, each of the recommendations and related actions are shown, along with a benefit percentage, estimated space used (if the option is selected), and any SQL that has been generated. In this case, we clicked on the first recommendation, which brought up the screen shown in Figure 13.8. You can also select the recommendations to be implemented and schedule them to be run at a later time. To view the complete SQL, click the Show SQL button. You can also change the view to see the SQL statements affected by the recommendations. The advanced options display the details of the run such as the source, limits, filters, and so on.

Figure 13.8. SQL Access Advisor recommendation details.

A table lists the actions that are needed to implement the selected recommendation. Certain fields such as the name, schema, and tablespace name in the case of indexes and materialized views to be created can be changed. Dependent names will be changed as required. If the Tablespace field is left blank, the default tablespace of the schema will be used. When you click OK, the SQL script is modified, but it is not actually executed until you select Schedule Implementation on the Recommendations page.

APIs to the SQL Tuning Advisor

Just as with all the advisors described previously, the SQL Tuning Advisor and the SQL Access Advisor can be viewed and manipulated using APIs in the DBMS_SQLTUNE and the now-familiar DBMS_ADVISOR built-in PL/SQL packages. The framework and general methodology is the same for both types of advisors in the sense that you will need to create tasks, set up parameters for executing them, schedule the execution, and view the results, all using specific calls in these PL/SQL packages. Additionally, the SQL tuning sets can be manipulated using the DBMS_SQLTUNE package. Because this could be used as an input to both the SQL Access Advisor as well as the SQL Tuning Advisor, you will need to understand both packages together in this context.

Package Usage Inconsistency

It seems that there is some inconsistency in grouping and naming the PL/SQL packages used as APIs to the advisors. While the DBMS_ADVISOR package is used for both the ADDM and the SQL Access Advisor, the DBMS_SQLTUNE is a separate package specifically for the SQL Tuning Advisor and for manipulating SQL tuning sets. Because the framework and general methodology of creating, submitting, and viewing advisor tasks remain the same, we hope there is a good reason for this deviation.

Setting Up and Using SQL Tunings Sets

As stated previously, an SQL tuning set (STS) is a new object for capturing SQL workload information. SQL tuning sets provide a common infrastructure for dealing with SQL workloads and enable tuning of a large number of SQL statements as a collective, related unit. SQL tuning sets store SQL statements along with the execution context, such as the parsing schema name and bind values, as well as execution statistics such as average elapsed time and execution count.

SQL tuning sets can be created by filtering or ranking SQL statements from several sources including the cursor cache (Spot SQL), Top SQL statements executed during some prior interval from AWR snapshots, a user-defined workload, or even other SQL tuning sets.

You first need to understand how SQL tuning sets can be manipulated using the DBMS_SQLTUNE package because they can be used as input to both the SQL Tuning Advisor and the SQL Access Advisor. You can use the functions and procedures in Table 13.1 to manipulate SQL tuning sets.

Table 13.1. DBMS_SQLTUNE Functions and Procedures Specific to SQL Tuning Sets

Function/Procedure	Description
CREATE_SQLSET, DROP_SQLSET	These procedures create/drop a SqlSet object in the database by name. They are exposed via the view DBA_SQLSET. The STATEMENT_COUNT column shows the number of statements in the STS.
LOAD_SQLSET	This procedure populates the STS with a set of selected SQLs using the Sqlset_Cursor type.
SELECT_SQLSET	This function collects SQL statements from the cursor cache.
SELECT_WORKLOAD_REPOSITORY	This function collects SQL statements from the workload repository.
UPDATE_SQLSET	This procedure updates selected string fields such as MODULE and ACTION or updates numerical attributes of a SQL in a SQL set.
ADD_SQLSET_REFERENCE, DROP_SQLSET_REFERENCE	These functions add/drop a reference to/from an existing SqlSet to indicate its use (or lack thereof) by a client. They are recorded in the DBA_SQLSET_REFERENCES view.

Accessing the SQL Tuning Advisor APIs

The SQL Tuning Advisor uses the DBMS_SQLTUNE built-in PL/SQL package to perform its tuning activities. The relevant procedures and functions can be seen in Table 13.2.

Table 13.2. DBMS_SQLTUNE Functions and Procedures Specific to SQL Tuning Advisor

Function/Procedure	Description
CREATE_TUNING_TASK, DROP_TUNING_TASK	These functions create/drop a SQL Tuning Advisor task in the database by name. CREATE_TUNING_TASK is an overloaded function that can take in a variety of inputs including a single SQL statement to tune either by specifying its text or the IDs of a statement in the cursor cache, a SQL set, or a range of snapshot IDs from AWR. Other inputs include bind variables, scope, and time limit, as well as SQL-statement ranking parameters.
EXECUTE_TUNING_TASK, INTERRUPT_TUNING_TASK, CANCEL_TUNING_TASK, RESUME_TUNING_TASK	These procedures execute/interrupt/resume/cancel a named tuning task, respectively.
REPORT_TUNING_TASK	This function displays the result of a tuning task given a task (by default the last task for that user). Optionally, an object within an SQL set and limits of reporting can be specified.
ACCEPT_SQL_PROFILE, ALTER_SQL_PROFILE, DROP_SQL_PROFILE	These procedures create/alter/drop SQL profiles for the specified SQL tuning task, respectively. An SQL profile can be enabled or disabled.

More details about DBMS_SQLTUNE functions and procedures specific to SQL tuning sets are available in the Oracle Database 10g PL/SQL Packages and Types Reference. An example can be seen in MetaLink Note #259188.1.

A simple example of setting up and executing an SQL tuning task for a single SQL statement is shown in Listing 13.1. The same SQL statement discussed in Listing 10.13 of Chapter 10 is listed. In order to force a problem, we dropped both an index as well as the statistics.

Listing 13.1. Use of the DBMS_SQLTUNE Package

 SQL> drop index HR.EMP_DEPARTMENT_IX; Index dropped SQL> execute dbms_stats.delete_table_stats('HR','EMPLOYEES'); PL/SQL procedure successfully completed. SQL> execute dbms_sqltune.drop_tuning_task('STA Tuning task 2'); PL/SQL procedure successfully completed. SQL> declare   2          sql_tuning_task VARCHAR2(30);   3          sql_to_tune     CLOB;   4  begin   5          sql_to_tune := 'select e.* ' ||   6          'from hr.employees e, hr.departments d, hr.locations l, ' ||   7          'hr.countries c, hr.regions r ' ||   8          'where e.department_id = d.department_id ' ||   9          'and d.location_id = l.location_id ' ||  10          'and l.country_id = c.country_id ' ||  11          'and c.region_id = r.region_id ' ||  12          'and employee_id = ' ||  13          'substr(to_char(abs(dbms_random.random)),1,4)';  14          sql_tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK (  15                  sql_text        => sql_to_tune,  16                  scope           => 'COMPREHENSIVE',  17                  time_limit      => 30,  18                  task_name       => 'STA Tuning task 2',  19                  description     => 'STA Tuning task desc');  20          DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'STA Tuning task 2');  21  end;  22  / PL/SQL procedure successfully completed. SQL> select dbms_sqltune.report_tuning_task('STA Tuning task 2')   2  from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('STATUNINGTASK2') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : STA Tuning task 2 Scope              : COMPREHENSIVE Time Limit(seconds): 30 Completion Status  : COMPLETED Started at         : 03/05/2005 18:09:14 Completed at       : 03/05/2005 18:09:16 ------------------------------------------------------------------------------- SQL ID  : c3zbnzmnart60 SQL Text: select e.* from hr.employees e, hr.departments d, hr.locations l,           hr.countries c, hr.regions r where e.department_id =           d.department_id and d.location_id = l.location_id and l.country_id           = c.country_id and c.region_id = r.region_id and employee_id =           substr(to_char(abs(dbms_random.random)),1,4) ------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------- 1- Statistics Finding --------------- 

The Finding section correctly identifies the problem and states that statistics need to be gathered for the objects involved. When this is performed and the tuning task is rerun, the finding There are no recommendations to improve the statement is displayed.

Please note that the SQL statement should be syntactically and semantically correct. If not, an Errors section with the relevant error is displayed instead of the Findings section. An example is shown in Listing 13.2. This is a snippet of the code shown in Listing 13.1, but with some subtle syntactical errors deliberately introduced.

Listing 13.2. Snippets Showing Errors

   7          'hr.countries c, hr.regions r' ||   8          'where e.department_id = d.department_id' ||   9          'and d.location_id = l.location_id' ||  10          'and l.country_id = c.country_id' ||  11          'and c.region_id = r.region_id' ||  12          'and employee_id = ' || <snipped to reduce space> SQL ID  : a75nmwywsu87r SQL Text: select e.* from hr.employees e, hr.departments d, hr.locations l,           hr.countries c, hr.regions rwhere e.department_id =           d.department_idand d.location_id = l.location_idand l.country_id =           c.country_idand c.region_id = r.region_idand employee_id =           substr(to_char(abs(dbms_random.random)),1,4) ------------------------------------------------------------------------------- ERRORS SECTION ------------------------------------------------------------------------------- - ORA-00933: SQL command not properly ended 

Can you spot the errors in the bold-faced text? There was no space after the id terms at the end of every line, and the concatenation produced incorrect SQL. Keep in mind that such simple mistakes will produce errors when the advisor is run.

When appropriate, additional information such as rationales and execution plans may be displayed. As well, when a tuning task is set up using the CREATE_TUNING_TASK function, but never executed using the EXECUTE_TUNING_TASK procedure, you will get the Error: task Your tuning task needs to be executed first message if you try to use the REPORT_TUNING_TASK function on it.

Accessing the SQL Access Advisor APIs

The SQL Access Advisor uses certain procedures and functions from the DBMS_ADVISOR built-in PL/SQL package. You already saw some of its functions and procedures in the previous chapter, so keeping those in mind, we've listed only those that are relevant to the SQL Access Advisor in Table 13.3.

Table 13.3. DBMS_ADVISOR Functions and Procedures Specific to SQL Access Advisor

Function/Procedure	Description
CREATE_SQLWKLD, DELETE_SQLWKLD	These procedures create/delete an SQL workload object to hold all the relevant details of a set of SQL statements.
ADD_SQLWKLD_STATEMENT, DELETE_SQLWKLD_STATEMENT	These procedures add/delete a single SQL statement along with required workload parameters to/from a previously defined workload.
ADD_SQLWKLD_REF, DELETE_SQLWKLD_REF	These procedures establish/remove the link between the SQL workload object and an advisor task, thus setting up/removing the task to be executed later using the EXECUTE_TASK procedure common to advisors.
IMPORT_SQLWKLD_SCHEMA, IMPORT_SQLWKLD_SQLCACHE, IMPORT_SQLWKLD_STS, IMPORT_SQLWKLD_SUMADV IMPORT_SQLWKLD_USER	These procedures import SQL statements from a, variety of sources including the SQL cache, a SQL tuning set, a Summary Advisor workload (from Oracle 9i), or a specified user table, respectively. This mirrors the input sources explained earlier.
GET_TASK_SCRIPT, CREATE_FILE	The GET_TASK_SCRIPT function and the CREATE_FILE procedure can be used in a combination to create a script that will implement the recommendations as seen in Listing 13.5.

Note that you will use some of the previously defined procedures and functions such as the CREATE_TASK and EXECUTE_TASK calls that are common to all the advisors. These were listed in the previous chapter. Listing 13.3 shows procedures that can be used to help tune a summary SQL. In this case, we chose the SH schema from the sample schemas and created an SQL that performed a summary of sales as shown in the SQL. Because a summary is being performed, you might expect that one of the recommendations would be to create a materialized view.

Listing 13.3. Use of the DBMS_ADVISOR Package for the SQL Access Advisor

 SQL> DECLARE   2  workload_name VARCHAR2(30);   3  task_id       VARCHAR2(30);   4  task_name     VARCHAR2(30);   5  BEGIN   6  workload_name := 'SAA Workload';   7  task_id       := 'SAA Task';   8  task_name     := 'Generate Access Advice Task';   9  -- Create the SQL Access Advisor workload  10  dbms_advisor.create_sqlwkld(workload_name, 'Test SQL Access Adv Workload');  11  -- Setup the SQL Access Advisor , including cost of access and SQL  12  dbms_advisor.add_sqlwkld_statement(workload_name, 'Reporting', 'Rollup',  13  200,420,3424,4203,640445,680000,2,1,SYSDATE,1,'SH',  14  'select p.prod_category, t.week_ending_day, s.cust_id, ' ||  15  'sum(s.amount_sold) from sh.sales s, sh.products p, sh.times t ' ||  16  'where s.time_id = t.time_id and s.prod_id = p.prod_id ' ||  17  'group by p.prod_category, t.week_ending_day, s.cust_id');  18  -- Now create an Advisor task for the SQL Access Advisor  19  dbms_advisor.create_task ('SQL Access Advisor', task_id, task_name);  20  -- Associate the Workload to the Advisor task  21  dbms_advisor.add_sqlwkld_ref(task_id, workload_name);  22  -- Execute the Advisor task  23  dbms_advisor.execute_task(task_id);  24  END;  25  / PL/SQL procedure successfully completed. 

The calls to the specific DBMS_ADVISOR package are in bold, and the SQL statement itself is in italics. The workload SAA Workload is first created and a particular SQL statement attached to it. The SQL statement needs certain other inputs such as module, action, CPU and elapsed time, disk reads, buffer gets, rows processed, optimizer cost, number of executions, priority, execution date, user name, and so on, in addition to the actual SQL statement. These values are required in order to provide a cost to the query so that an internal comparison to the initially supplied values can be made once tuning is complete. Similar to the input specified to the SQL Tuning Advisor, this SQL needs to be syntactically and semantically correct. When this is done, a task named SAA Task is created with the named advisor (in this case, SQL Access Advisor) and associated with the workload. It is then executed via the previously explained EXECUTE_TASK call just like other advisors.

The processed results are exposed through a number of DBA_ADVISOR_ tables. The SQLs against these specific tables and outputs shown in Listing 13.4 show the end result.

Listing 13.4. Cross-Check the Results

 SQL> select sql_id, rec_id, precost, postcost,   2  (precost-postcost)*100/precost pct_benefit, importance   3  from dba_advisor_sqla_wk_stmts   4  where task_name = 'SAA Task';     SQL_ID     REC_ID    PRECOST   POSTCOST PCT_BENEFIT IMPORTANCE ---------- ---------- ---------- ---------- ----------- ----------         42          1      43138       4020  90.6810701        100 SQL> column command format a30 SQL> select rec_id, action_id, substr(command,1,30) command   2  from dba_advisor_actions   3  where task_name = 'SAA Task' order by rec_id, action_id;     REC_ID  ACTION_ID COMMAND ---------- ---------- ------------------------------          1          1 CREATE MATERIALIZED VIEW LOG          1          3 CREATE MATERIALIZED VIEW LOG          1          5 CREATE MATERIALIZED VIEW LOG          1          7 CREATE MATERIALIZED VIEW          1          8 GATHER TABLE STATISTICS 

The PRECOST and POSTCOST columns show the actual estimated cost of access for the original SQL as well as the estimated cost of the rewritten SQL that would now execute using the materialized view on behalf of the user. Now that the results are in, you can use the code shown in Listing 13.5 to obtain a physical script that can be used for implementation later. The resultant script that was produced is also shown.

Listing 13.5. Creating the Implementation Scripts

 SQL> create directory advisor_results as '/tmp/adv_results'; Directory created. SQL> exec dbms_advisor.create_file (dbms_advisor.get_task_script('SAA Task') > , 'ADVISOR_RESULTS','saa_task_cr_script.sql'); PL/SQL procedure successfully completed. $ cat /tmp/adv_results/saa_task_cr_script.sql Rem  SQL Access Advisor: Version 10.1.0.1 - Production Rem Rem  Username:        SYS Rem  Task:            SAA Task Rem  Execution date:  07/03/2005 18:53 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_CATEGORY")     INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON     "SH"."TIMES"     WITH ROWID, SEQUENCE("TIME_ID","WEEK_ENDING_DAY")     INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON     "SH"."SALES"     WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","TIME_ID","AMOUNT_SOLD")     INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW "SYS"."MV$$_0BBF0002"     REFRESH FAST WITH ROWID     ENABLE QUERY REWRITE     AS SELECT SH.PRODUCTS.PROD_CATEGORY C1, SH.TIMES.WEEK_ENDING_DAY C2,     SH.SALES.CUST_ID C3,     SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2,     COUNT(*) M3 FROM SH.PRODUCTS, SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID        = SH.TIMES.TIME_ID AND SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY        SH.PRODUCTS.PROD_CATEGORY, SH.TIMES.WEEK_ENDING_DAY, SH.SALES.CUST_ID; begin   dbms_stats.gather_table_stats('"SYS"','"MV$$_0BBF0002"',NULL,dbms_stats.auto_s ample_size); end; / whenever sqlerror EXIT SQL.SQLCODE begin   dbms_advisor.mark_recommendation('SAA Task',1,'IMPLEMENTED'); end; / 

Note that the system generated its own names for the objects. You have the choice of changing them prior to implementation.