SQL Access Advisor in 11G release 1

The SQL Access Advisor was introduced in Oracle 10g to make suggestions about additional indexes and materialized views which might improve system performance. Oracle 11g has made two significant changes to the SQL Access Advisor:

1. The advisor now includes advice on partitioning schemes that may improve performance.
2. The original workload manipulation has been deprecated and replaced by SQL tuning sets.

This article is a rewrite the 10g SQL Access Advisor article to incorporate these changes.

Enterprise Manager

DBMS_ADVISOR

Related Views

Enterprise Manager

Enterprise Manager provides a very simple interface for the SQL Access Advisor (Advisor Central > SQL Advisor >SQL Access Advisor). The first page allows you to create tasks to test existing indexes, materialized view and partitions, or create tasks to suggest new structures. Select the "Recommend new access structures" option and click the "Continue" button.



The "Workload Source" page allows you to define the workload to associate with the task. The basic options allow the workload to be gathered from the cursor cache, an existing SQL tuning set, or a hypothetical workload based on specific schema objects. The "Filter Options" allow more flexibility by filtering statements based on Users, Tables, SQL Text, Modules and Actions. Select the "Current and Recent SQL Activity" option and click the "Next" button.



The "Recommendation Options" page allows you to define which type of recommendations you are interested in (Indexes, Materialized Views and Partitioning). In addition, you can define the scope of the analysis (Limited or Comprehensive). The "Advanced Options" give you even more flexibility for fine tuning the recommendations. Check the Indexes, Materialized Views, Partitioning and Limited options and click the "Next" button.



The "Schedule" page allows you to name the task, define the task retention time and the maximum execution time, in addition to scheduling the job. Accept the system generated task name and immediate schedule. If the time zone doesn't default to the correct value, set this and click the "Next" button.



The "Review" screen contains a summary of the task definition. Clicking the "Show SQL" button displays the PL/SQL API calls used to generate the task. Click the "Submit" button to execute the task.



Once the task is created it is visible in the list of the tasks on the "Advisor Central" screen. Once the status of the task is "Complete", click on the task name to review the findings.



After reviewing the result of the analysis you can decide if you should accept or ignore the suggested recommendations.

DBMS_ADVISOR

The DBMS_ADVISOR package, in cojunction with the DBMS_SQLTUNE package, is used to create and execute any advisor tasks, including SQL Access Advisor tasks. The easiest way to see how to use these packages is to define a task using Enterprise Manager and click the "Show SQL" button on the "Review" screen. The following code was produced by Enterprise Manager when defining the previous task.

DECLARE
  taskname varchar2(30) := 'SQLACCESS3638195';
  task_desc varchar2(256) := 'SQL Access Advisor';
  task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
  task_id number := 0;
  num_found number;
  sts_name varchar2(256) := 'SQLACCESS3638195_sts';
  sts_cursor dbms_sqltune.sqlset_cursor;
BEGIN
  /* Create Task */
  dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
                           task_id,
                           taskname,
                           task_desc,
                           task_or_template);

  /* Reset Task */
  dbms_advisor.reset_task(taskname);

  /* Delete Previous STS Workload Task Link */
  select count(*)
  into   num_found
  from   user_advisor_sqla_wk_map
  where  task_name = taskname
  and    workload_name = sts_name;
  IF num_found > 0 THEN
    dbms_advisor.delete_sqlwkld_ref(taskname,sts_name,1);
  END IF;

  /* Delete Previous STS */
  select count(*)
  into   num_found
  from   user_advisor_sqlw_sum
  where  workload_name = sts_name;
  IF num_found > 0 THEN
    dbms_sqltune.delete_sqlset(sts_name);
  END IF;

  /* Create STS */
  dbms_sqltune.create_sqlset(sts_name, 'Obtain workload from cursor cache');

  /* Select all statements in the cursor cache. */
  OPEN sts_cursor FOR
    SELECT VALUE(P)
    FROM TABLE(dbms_sqltune.select_cursor_cache) P;

  /* Load the statements into STS. */
  dbms_sqltune.load_sqlset(sts_name, sts_cursor);
  CLOSE sts_cursor;

  /* Link STS Workload to Task */
  dbms_advisor.add_sqlwkld_ref(taskname,sts_name,1);

  /* Set STS Workload Parameters */
  dbms_advisor.set_task_parameter(taskname,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT','25');
  dbms_advisor.set_task_parameter(taskname,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');

  /* Set Task Parameters */
  dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','ALL');
  dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
  dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
  dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED');
  dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
  dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
  dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
  dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
  dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
  dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');

  /* Execute Task */
  dbms_advisor.execute_task(taskname);
END;
/

Notice the use of the DBMS_SQLTUNE package to generate an SQL tuning set as a workload. In 10g, this would have been done using the DBMS_ADVISOR.CREATE_SQLWKLD procedure. The ANALYSIS_SCOPE parameter defines the structures to be analyzed. Setting this to "ALL" is comparable to the comma separated list "INDEX, MVIEW, TABLE, PARTITION". Subsets of this information can be used if desired.

The "GET_TASK_SCRIPT" function allows you to display the recommendations once the task is complete.

-- Display the resulting script.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('SQLACCESS3638195') AS script
FROM   dual;
SET PAGESIZE 24

The value for the SET LONG command should be adjusted to allow the whole script to be displayed.

Related Views

The following views can be used to display the SQL Access Advisor output without using Enterprise Manager or the get_task_script function:

• - Basic information about existing tasks.
• - Status information about existing tasks.
• - Findings identified for an existing task.
• - Recommendations for the problems identified by an existing task.

For further information see: