21 Optimizing Access Paths with SQL Access Advisor

21.1.1 Purpose of SQL Access Advisor

SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, partitions, and indexes for a given workload. Materialized views, partitions, and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries.

SQL Access Advisor takes an actual workload as input, or derives a hypothetical workload from a schema. The advisor then recommends access structures for faster execution path. The advisor provides the following advantages:

• Does not require you to have expert knowledge

• Makes decisions based on rules that reside in the optimizer

• Covers all aspects of SQL access in a single advisor

• Provides simple, user-friendly GUI wizards in Cloud Control

• Generates scripts for implementation of recommendations

21.1.2 SQL Access Advisor Architecture

Automatic Tuning Optimizer is the central tool used by SQL Access Advisor. The advisor can receive SQL statements as input from the sources shown in Figure 21-1, analyze these statements using the optimizer, and then make recommendations.

Figure 21-1 shows the basic architecture of SQL Access Advisor.

Figure 21-1 SQL Access Advisor Architecture

Description of "Figure 21-1 SQL Access Advisor Architecture"

21.1.3 User Interfaces for SQL Access Advisor

Oracle recommends that you use SQL Access Advisor through its GUI wizard, which is available in Cloud Control. Oracle Database 2 Day + Performance Tuning Guide explains how to use the SQL Access Advisor wizard.

You can also invoke SQL Access Advisor through the DBMS_ADVISOR package. This chapter explains how to use the API. See Oracle Database PL/SQL Packages and Types Reference for complete semantics and syntax.

21.2.1 Creating a SQL Tuning Set as Input for SQL Access Advisor

The input workload source for SQL Access Advisor is an STS. Because an STS is stored as a separate entity, multiple advisor tasks can share it. Create an STS with the DBMS_SQLTUNE.CREATE_SQLSET statement.

After an advisor task has referenced an STS, you cannot delete or modify the STS until all advisor tasks have removed their dependency on it. A workload reference is removed when a parent advisor task is deleted, or when you manually remove the workload reference from the advisor task.

Prerequisites

The user creating the STS must have been granted the ADMINISTER SQL TUNING SET privilege. To run SQL Access Advisor on SQL tuning sets owned by other users, the user must have the ADMINISTER ANY SQL TUNING SET privilege.

Assumptions

This tutorial assumes the following:

• You want to create an STS named MY_STS_WORKLOAD.

• You want to use this STS as input for a workload derived from the sh schema.

To create an STS : 

1. Connect SQL*Plus to the database as user sh, and then set SQL*Plus variables.

   For example, enter the following commands:

   CONNECT SH
   Password: ********
   SET SERVEROUTPUT ON;
   VARIABLE task_id NUMBER;
   VARIABLE task_name VARCHAR2(255);
   VARIABLE workload_name VARCHAR2(255);
   

2. Create the SQL tuning set.

   For example, assign a value to the workload_name variable and create the STS as follows:

   EXECUTE :workload_name := 'MY_STS_WORKLOAD';
   EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purpose');
   

21.2.2 Populating a SQL Tuning Set with a User-Defined Workload

A workload consists of one or more SQL statements, plus statistics and attributes that fully describe each statement. A full workload contains all SQL statements from a target business application. A partial workload contains a subset of SQL statements. The difference is that for full workloads SQL Access Advisor may recommend dropping unused materialized views and indexes.

You cannot use SQL Access Advisor without a workload. SQL Access Advisor ranks the entries according to a specific statistic, business importance, or combination of the two, which enables the advisor to process the most important SQL statements first.

SQL Access Advisor performs best with a workload based on actual usage. You can store multiple workloads in the form of SQL tuning sets, so that you can view the different uses of a real-world data warehousing or OLTP environment over a long period and across the life cycle of database instance startup and shutdown.

Table 21-1 describes procedures that you can use to populate an STS with a user-defined workload.

Assumptions

This tutorial assumes that you want to do the following:

• Create a table named sh.user_workload to store information about SQL statements

• Load the sh.user_workload table with information about three queries of tables in the sh schema

• Populate the STS created in "Creating a SQL Tuning Set as Input for SQL Access Advisor" with the workload contained in sh.user_workload

To populate an STS with a user-defined workload: 

1. Connect SQL*Plus to the database as user sh, and then create the user_workload table.

   For example, enter the following commands:

   DROP TABLE user_workload;
   CREATE TABLE user_workload
     (
       username              varchar2(128),      /* User who executes statement */
       module                varchar2(64),           /* Application module name */
       action                varchar2(64),           /* Application action name */
       elapsed_time          number,                  /* Elapsed time for query */
       cpu_time              number,                      /* CPU time for query */
       buffer_gets           number,           /* Buffer gets consumed by query */
       disk_reads            number,            /* Disk reads consumed by query */
       rows_processed        number,       /* Number of rows processed by query */
       executions            number,          /* Number of times query executed */
       optimizer_cost        number,                /* Optimizer cost for query */
       priority              number,                /* User-priority (1,2 or 3) */
       last_execution_date   date,                  /* Last time query executed */
       stat_period           number,        /* Window execution time in seconds */
       sql_text              clob                              /* Full SQL Text */
     );
   

2. Load the user_workload table with information about queries.

   For example, execute the following statements:

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

3. Execute a PL/SQL program that fills a cursor with rows from the user_workload table, and then loads the contents of this cursor into the STS named MY_STS_WORKLOAD.

   For example, execute the following PL/SQL program:

   DECLARE
     sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
   BEGIN
     OPEN sqlset_cur FOR
       SELECT SQLSET_ROW(null,null, SQL_TEXT, null, null, 'SH', module,
                        'Action', 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, null, 2, 3,
                        sysdate, 0, 0, null, 0, null, null)
       FROM USER_WORKLOAD;
     DBMS_SQLTUNE.LOAD_SQLSET('MY_STS_WORKLOAD', sqlset_cur);
   END;
   /
   

21.2.3 Creating and Configuring a SQL Access Advisor Task

Use the DBMS_ADVISOR.CREATE_TASK procedure to create a SQL Access Advisor task. In the SQL Access Advisor task, you define what the advisor must analyze and the location of the results. You can create multiple tasks, each with its own specialization. All are based on the same Advisor task model and share the same repository.

Configuring the task involves the following steps:

• Defining task parameters

  At the time the recommendations are generated, you can apply a filter to the workload to restrict what is analyzed. This restriction provides the ability to generate different sets of recommendations based on different workload scenarios.

  SQL Access Advisor parameters control the recommendation process and customization of the workload. These parameters control various aspects of the process, such as the type of recommendation required and the naming conventions for what it recommends. See "Categories for SQL Access Advisor Task Parameters".

  If parameters are not defined, then the database uses the defaults. You can set task parameters by using the DBMS_ADVISOR.SET_TASK_PARAMETER procedure. Parameters are persistent in that they remain set for the life span of the task. When a parameter value is set using SET_TASK_PARAMETER, it does not change until you make another call to this procedure.

• Linking the task to the workload

  Because the workload is independent, you must link it to a task using the DBMS_ADVISOR.ADD_STS_REF procedure. After this link has been established, you cannot delete or modify the workload until all advisor tasks have removed their dependency on the workload. A workload reference is removed when a user deletes a parent advisor task or manually removes the workload reference from the task by using the DBMS_ADVISOR.DELETE_STS_REF procedure (see "Deleting SQL Access Advisor Tasks").

Prerequisites and Restrictions

The user creating the task must have been granted the ADVISOR privilege.

Assumptions

This tutorial assumes the following:

• You want to create a task named MYTASK.

• You want to use this task to analyze the workload that you defined in "Populating a SQL Tuning Set with a User-Defined Workload".

• You want to terminate the task if it takes longer than 30 minutes to execute.

• You want to SQL Access Advisor to only consider indexes.

To create and configure a SQL Access Advisor task: 

1. Connect SQL*Plus to the database as user sh, and then create the task.

   For example, enter the following commands:

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

2. Set task parameters.

   For example, execute the following statements:

   EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'TIME_LIMIT', 30);
   EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'ALL');
   

3. Link the task to the workload.

   For example, execute the following statement:

   EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, 'SH', :workload_name);
   

21.2.4 Executing a SQL Access Advisor Task

The DBMS_ADVISOR.EXECUTE_TASK procedure performs SQL Access Advisor analysis or evaluation for the specified task. Task execution is a synchronous operation, so the database does not return control to the user until the operation has completed, or the database detects a user interrupt. After the return or execution of the task, you can check the DBA_ADVISOR_LOG table for the execution status.

Running EXECUTE_TASK generates recommendations. A recommendation includes one or more actions, such as creating a materialized view log or a materialized view.

Prerequisites and Restrictions

When processing a workload, SQL Access Advisor attempts to validate each statement to identify table and column references. The database achieves validation by processing each statement as if it were being executed by the statement's original user.

If the user does not have SELECT privileges to a particular table, then SQL Access Advisor bypasses the statement referencing the table. This behavior can cause many statements to be excluded from analysis. If SQL Access Advisor excludes all statements in a workload, then the workload is invalid. SQL Access 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 these tables, these SELECT privileges cannot be obtained through a role.

Assumptions

This tutorial assumes that you want to execute the task you configured in "Creating and Configuring a SQL Access Advisor Task".

To create and configure a SQL Access Advisor task: 

1. Connect SQL*Plus to the database as user sh, and then execute the task.

   For example, execute the following statement:

   EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
   

2. Optionally, query USER_ADVISOR_LOG to check the status of the task.

   For example, execute the following statements (sample output included):

   COL TASK_ID FORMAT 999
   COL TASK_NAME FORMAT a25
   COL STATUS_MESSAGE FORMAT a25
   
   SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE 
   FROM   USER_ADVISOR_LOG;
   
   TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
   ------- ------------------------- ----------- -------------------------
       103 MYTASK                    COMPLETED   Access advisor execution
                                                 completed
   

21.2.5 Viewing SQL Access Advisor Task Results

You can view each recommendation generated by SQL Access Advisor using several data dictionary views, which are summarized in Table 21-2. However, it is easier to use the DBMS_ADVISOR.GET_TASK_SCRIPT procedure or Cloud Control, which graphically displays the recommendations and provides hyperlinks to quickly see which SQL statements benefit from a recommendation.

Each recommendation produced by SQL Access Advisor is linked to the SQL statement it benefits. Each recommendation corresponds to one or more actions. EAch action has one or more attributes.

Each action has attributes pertaining to the access structure properties. The name and tablespace for each applicable access structure are in the ATTR1 and ATTR2 columns of USER_ADVISOR_ATTRIBUTES (see "Action Attributes in the DBA_ADVISOR_ACTIONS View"). The space occupied by each new access structure is in the NUM_ATTR1 column. Other attributes are different for each action.

Assumptions

This tutorial assumes that you want to view results of the task you executed in "Executing a SQL Access Advisor Task".

To view the results of a SQL Access Advisor task: 

1. Connect SQL*Plus to the database with the appropriate privileges, and then query the advisor recommendations.

   For example, execute the following statements (sample output included):

   VARIABLE workload_name VARCHAR2(255); 
   VARIABLE task_name VARCHAR2(255);
   EXECUTE :task_name := 'MYTASK';
   EXECUTE :workload_name := 'MY_STS_WORKLOAD'; 
   
   SELECT REC_ID, RANK, BENEFIT
   FROM   USER_ADVISOR_RECOMMENDATIONS 
   WHERE  TASK_NAME = :task_name
   ORDER BY RANK;
   
       REC_ID       RANK    BENEFIT
   ---------- ---------- ----------
            1          1        236
            2          2        356
   

   The preceding output shows the recommendations (rec_id) produced by an SQL Access 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 queries using the recommendation.

2. Identify which query benefits from which recommendation.

   For example, execute the following query of USER_ADVISOR_SQLA_WK_STMTS (sample output included):

   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
   ORDER BY percent_benefit DESC;
   
   SQL_ID            REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
   ------------- ---------- ---------- ---------- ---------------
   fn4bsxdm98w3u          2        578        222      61.5916955
   29bbju72rv3t2          1       5750       5514      4.10434783
   133ym38r6gbar          0        772        772               0
   

   The precost and postcost numbers are in terms of the estimated optimizer cost (shown in EXPLAIN PLAN) both without and with the recommended access structure changes.

3. Display the number of distinct actions for this set of recommendations.

   For example, use the following query (sample output included):

   SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
   FROM   USER_ADVISOR_ACTIONS 
   WHERE  TASK_NAME = :task_name;
   
   'ACTIONCOUNT        CNT
   ------------ ----------
   Action Count          4
   

4. Display the actions for this set of recommendations.

   For example, use the following query (sample output included):

   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          1 PARTITION TABLE
            1          2 RETAIN INDEX
            2          1 PARTITION TABLE
            2          3 RETAIN INDEX
            2          4 RETAIN INDEX
   

5. Display attributes of the recommendations.

   For example, create the following PL/SQL procedure show_recm, and then execute it to see attributes of the actions:

   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;
   /
   
   SET SERVEROUTPUT ON SIZE 99999
   EXECUTE show_recm(:task_name);
   

   The following output shows attributes of actions in the recommendations:

   =========================================
   Task_name = MYTASK
   Action ID: 1
   Command : PARTITION TABLE
   Attr1 (name)      : "SH"."SALES"
   Attr2 (tablespace):
   Attr3             : ("TIME_ID")
   Attr4             : INTERVAL
   Attr5             :
   ----------------------------------------
   Action ID: 2
   Command : RETAIN INDEX
   Attr1 (name)      : "SH"."PRODUCTS_PK"
   Attr2 (tablespace):
   Attr3             : "SH"."PRODUCTS"
   Attr4             : BTREE
   Attr5             :
   ----------------------------------------
   Action ID: 3
   Command : RETAIN INDEX
   Attr1 (name)      : "SH"."TIMES_PK"
   Attr2 (tablespace):
   Attr3             : "SH"."TIMES"
   Attr4             : BTREE
   Attr5             :
   ----------------------------------------
   Action ID: 4
   Command : RETAIN INDEX
   Attr1 (name)      : "SH"."SALES_TIME_BIX"
   Attr2 (tablespace):
   Attr3             : "SH"."SALES"
   Attr4             : BITMAP
   Attr5             :
   ----------------------------------------
   =========END RECOMMENDATIONS============
   

21.2.6 Generating and Executing a Task Script

You can use the procedure DBMS_ADVISOR.GET_TASK_SCRIPT to create a script of the SQL statements for the SQL Access Advisor recommendations. The 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 automatically generated by using the user-specified name template. Review the generated SQL script before attempting to execute it.

Assumptions

This tutorial assumes that you want to save and execute a script that contains the recommendations generated in "Executing a SQL Access Advisor Task".

To save and execute a SQL script: 

1. Connect SQL*Plus to the database as an administrator.

2. Create a directory object and grant permissions to read and write to it.

   For example, use the following statements:

   CREATE DIRECTORY ADVISOR_RESULTS AS '/tmp';
   GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
   GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
   

3. Connect to the database as sh, and then save the script to a file.

   For example, use the following statement:

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

4. Use a text editor to view the contents of the script.

   The following is a fragment of a script generated by this procedure:

   Rem  Username:        SH
   Rem  Task:            MYTASK
   Rem  Execution date:
   Rem
    
   Rem
   Rem  Repartitioning table "SH"."SALES"
   Rem
    
   SET SERVEROUTPUT ON
   SET ECHO ON
    
   Rem
   Rem Creating new partitioned table
   Rem
     CREATE TABLE "SH"."SALES1"
      (    "PROD_ID" NUMBER,
           "CUST_ID" NUMBER,
           "TIME_ID" DATE,
           "CHANNEL_ID" NUMBER,
           "PROMO_ID" NUMBER,
           "QUANTITY_SOLD" NUMBER(10,2),
           "AMOUNT_SOLD" NUMBER(10,2)
      ) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS  NOLOGGING
     TABLESPACE "EXAMPLE"
   PARTITION BY RANGE ("TIME_ID") INTERVAL( NUMTOYMINTERVAL( 1, 'MONTH')) ( 
   PARTITION VALUES LESS THAN (TO_DATE(' 1998-02-01 00:00:00', 
   'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );
   .
   .
   .
   

5. Optionally, in SQL*Plus, run the SQL script.

   For example, enter the following command:

   @/tmp/advscript.sql
   

21.4.1 Evaluating Existing Access Structures

SQL Access Advisor operates in two modes: problem-solving and evaluation. By default, SQL Access Advisor attempts to solve access method problems by looking for enhancements to index structures, partitions, materialized views, and materialized view logs. For example, a problem-solving run may recommend creating a new index, adding a new column to a materialized view log, and so on.

When you set the ANALYSIS_SCOPE parameter to EVALUATION, SQL Access Advisor comments only on which access structures the supplied workload uses. An evaluation-only run may only produce recommendations such as retaining an index, retaining a materialized view, and so on. The evaluation mode can be useful to see exactly which indexes and materialized views a workload is using. SQL Access Advisor does not evaluate the performance impact of existing base table partitioning.

To create a task and set it to evaluation mode: 

1. Connect SQL*Plus to the database with the appropriate privileges, and then create a task.

   For example, enter the following statement, where t_name is a SQL*Plus variable set to the name of the task:

   EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:t_name);
   

2. Perform the quick tune.

   For example, the following statement sets the previous task to evaluation mode:

   EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:t_name,'ANALYSIS_SCOPE','EVALUATION');
   

21.4.2 Updating SQL Access Advisor Task Attributes

You can use the DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES procedure to do the following:

• Change the name of a task.

• Give a task a description.

• Set the task to be read-only so it cannot be changed.

• Make the task a template upon which you can define other tasks (see "Creating and Using SQL Access Advisor Task Templates").

• Changes various attributes of a task or a task template.

Assumptions

This tutorial assumes the following:

• You want to change the name of existing task MYTASK to TUNING1.

• You want to make the task TUNING1 read-only.

To update task attributes: 

1. Connect SQL*Plus to the database as user sh, and then change the name of the task.

   For example, use the following statement:

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

2. Set the task to read-only.

   For example, use the following statement:

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

21.4.3 Creating and Using SQL Access Advisor Task Templates

A task template is a saved configuration on which to base future tasks and workloads. A template enables you to set up any number of tasks or workloads that can serve as starting points or templates for future task creation. By setting up a template, you can save time when performing tuning analysis. This approach also enables you to custom fit a tuning analysis to the business operation.

Physically, there is no difference between a task and a template. However, a template cannot be executed. To create a task from a template, you specify the template to be used when a new task is created. At that time, SQL Access 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.

Table 21-3 describes procedures that you can use to manage task templates.

Assumptions

This tutorial assumes the following:

• You want to create a template named MY_TEMPLATE.

• You want to set naming conventions for indexes and materialized views that are recommended by tasks based on MY_TEMPLATE.

• You want to create task NEWTASK based on MY_TEMPLATE.

To create a template and base a task on this template: 

1. Connect SQL*Plus to the database as user sh, and then create a task as a template.

   For example, create a template named MY_TEMPLATE as follows:

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

2. Set template parameters.

   For example, the following statements set the naming conventions for recommended indexes and materialized views:

   -- set naming conventions for recommended indexes/mvs
   BEGIN 
     DBMS_ADVISOR.SET_TASK_PARAMETER ( 
       :template_name
   ,   'INDEX_NAME_TEMPLATE'
   ,   'SH_IDX$$_<SEQ>'
   );
   END;
   
   BEGIN 
     DBMS_ADVISOR.SET_TASK_PARAMETER (
       :template_name
   ,   'MVIEW_NAME_TEMPLATE'
   ,   'SH_MV$$_<SEQ>'
   );
   END;
   

3. Create a task based on a pre-existing template.

   For example, enter the following commands to create NEWTASK based on MY_TEMPLATE:

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

21.4.4 Terminating SQL Access Advisor Task Execution

SQL Access Advisor enables you to interrupt the recommendation process or allow it to complete. An interruption signals SQL Access Advisor to stop processing and marks the task as INTERRUPTED. At that point, you may update recommendation attributes and generate scripts.

Intermediate results represent recommendations for the workload contents up to that point in time. If recommendations must be sensitive to the entire workload, then Oracle recommends that you let the task complete. Additionally, recommendations made by the advisor early in the recommendation process do not contain base table partitioning recommendations. The partitioning analysis requires a large part of the workload to be processed before it can determine whether partitioning would be beneficial. Therefore, if SQL Access Advisor detects a benefit, then only later intermediate results contain base table partitioning recommendations.

This section describes two ways to terminate SQL Access Advisor task execution:

• Interrupting SQL Access Advisor Tasks

• Canceling SQL Access Advisor Tasks

DBMS_ADVISOR.INTERRUPT_TASK (task_name IN VARCHAR2);

DBMS_ADVISOR.CANCEL_TASK (task_name   IN  VARCHAR2);

DBMS_ADVISOR.RESET_TASK (task_name     IN VARCHAR2);

21.4.5 Deleting SQL Access Advisor Tasks

The DBMS_ADVISOR.DELETE_TASK procedure deletes existing SQL Access Advisor tasks from the repository. The syntax is as follows:

DBMS_ADVISOR.DELETE_TASK (task_name  IN VARCHAR2);

If a task is linked to an STS workload, and if you want to delete the task or workload, then you must remove the link between the task and the workload using the DELETE_STS_REF procedure. The following example deletes the link between task MYTASK and the current user's SQL tuning set MY_STS_WORKLOAD:

EXECUTE DBMS_ADVISOR.DELETE_STS_REF('MYTASK', null, 'MY_STS_WORKLOAD');

Assumptions

This tutorial assumes the following:

• User sh currently owns multiple SQL Access Advisor tasks.

• You want to delete MYTASK.

• The task MYTASK is currently linked to workload MY_STS_WORKLOAD.

To delete a SQL Access Advisor task: 

1. Connect SQL*Plus to the database as user sh, and then query existing SQL Access Advisor tasks.

   For example, query the data dictionary as follows (sample output included):

   SELECT TASK_NAME 
   FROM   USER_ADVISOR_TASKS 
   WHERE  ADVISOR_NAME = 'SQL Access Advisor';
    
   TASK_NAME
   -------------------------
   MYTASK
   NEWTASK
   

2. Delete the link between MYTASK and MY_STS_WORKLOAD.

   For example, delete the reference as follows:

   EXECUTE DBMS_ADVISOR.DELETE_STS_REF('MYTASK', null, 'MY_STS_WORKLOAD');
   

3. Delete the desired task.

   For example, delete MYTASK as follows:

   EXECUTE DBMS_ADVISOR.DELETE_TASK('MYTASK');
   

21.4.6 Marking SQL Access Advisor Recommendations

By default, all SQL Access Advisor recommendations are ready to be implemented. However, you can choose to skip or exclude selected recommendations by using the DBMS_ADVISOR.MARK_RECOMMENDATION procedure. MARK_RECOMMENDATION enables you to annotate a recommendation with a REJECT or IGNORE setting, which causes the GET_TASK_SCRIPT to skip it when producing the implementation procedure.

If SQL Access Advisor makes a recommendation to partition one or multiple previously unpartitioned base tables, then consider carefully before skipping this recommendation. Changing a table's partitioning scheme affects the cost of all queries, indexes, and materialized views defined on the table. Therefore, if you skip the partitioning recommendation, then the advisor's remaining recommendations on this table are no longer optimal. To see recommendations on your workload that do not contain partitioning, reset the advisor task and rerun it with the ANALYSIS_SCOPE parameter changed to exclude partitioning recommendations.

The syntax is as follows:

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

Assumptions

This tutorial assumes the following:

• You are reviewing the recommendations as described in tutorial "Viewing SQL Access Advisor Task Results".

• You want to reject the first recommendation, which partitions a table.

To mark a recommendation: 

1. Connect SQL*Plus to the database as user sh, and then mark the recommendation.

   For example, reject recommendation 1 as follows:

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

   This recommendation and any dependent recommendations do not appear in the script.

2. Generate the script as explained in "Generating and Executing a Task Script".

21.4.7 Modifying SQL Access Advisor Recommendations

Using the UPDATE_REC_ATTRIBUTES procedure, SQL Access Advisor names and assigns ownership to new objects such as indexes and materialized views during analysis. 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:

• 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.

Assumptions

This tutorial assumes the following:

• You are reviewing the recommendations as described in tutorial "Viewing SQL Access Advisor Task Results".

• You want to change the tablespace for recommendation 1, action 1 to SH_MVIEWS.

To mark a recommendation: 

1. Connect SQL*Plus to the database as user sh, and then update the recommendation attribute.

   For example, change the tablespace name to SH_MVIEWS as follows:

   BEGIN 
     DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES (
       'MYTASK'
   ,   1
   ,   1
   ,   'TABLESPACE'
   ,   'SH_MVIEWS'
   );
   END;
   

2. Generate the script as explained in "Generating and Executing a Task Script".

21.6.1 Action Attributes in the DBA_ADVISOR_ACTIONS View

Table 21-4 maps SQL Access Advisor actions to attribute columns in the DBA_ADVISOR_ACTIONS view. In the table, MV refers to a materialized view.

21.6.2 Categories for SQL Access Advisor Task Parameters

Table 21-5 groups the most relevant SQL Access Advisor task parameters into categories. All task parameters for workload filtering are deprecated.

21.6.3 SQL Access Advisor Constants

You can use the constants shown in Table 21-6 with SQL Access Advisor.

ADVISOR_ALL

ADVISOR_CURRENT

ADVISOR_DEFAULT

ADVISOR_UNLIMITED

ADVISOR_UNUSED

SQLACCESS_GENERAL

SQLACCESS_OLTP

SQLACCESS_WAREHOUSE

SQLACCESS_ADVISOR