Running SQL Access Advisor: Recommendation Options

To improve the underlying data access methods chosen by the optimizer for the workload, SQL Access Advisor provides recommendations for indexes, materialized views, and partitioning. Using these access structures can significantly improve the performance of the workload by reducing the time required to read data from the database. However, you must balance the benefits of using these access structures against the cost to maintain them.

To specify recommendation options: 

1. On the SQL Access Advisor: Recommendation Options page, under Access Structures to Recommend, select the type of access structures to be recommended by SQL Access Advisor:

   • Indexes

   • Materialized Views

   • Partitioning

2. Under Scope, select the mode in which SQL Access Advisor will run. Do one of the following:

   • Select Limited.

     In limited mode, SQL Access Advisor focuses on SQL statements with the highest cost in the workload. The analysis is quicker, but the recommendations may be limited.

   • Select Comprehensive.

     In comprehensive mode, SQL Access Advisor analyzes all SQL statements in the workload. The analysis can take much longer, but the recommendations will be exhaustive.

3. Optionally, click Advanced Options.

   The Advanced Options section expands. This section contains the following subsections:

   • Workload Categorization

     In this section, you can specify the type of workload for which you want a recommendation. The following categories are available:

     • Workload Volatility

       Select Consider only queries if the workload primarily contains read-only operations, as in data warehouses. Volatility data is useful for online transaction processing (OLTP) systems, where the performance of INSERT, UPDATE, and DELETE operations is critical.

     • Workload Scope

       Select Recommend dropping unused access structures if the workload represents all access structure use cases.

   • Space Restrictions

     Indexes and materialized views increase performance at the cost of space. Do one of the following:

     • Select No, show me all recommendations (unlimited space) to specify no space limits. When SQL Access Advisor is invoked with no space limits, it makes the best possible performance recommendations.

     • Select Yes, limit additional space to and then enter the space limit in megabytes, gigabytes, or terabytes. When SQL Access Advisor is invoked with a space limit, it produces only recommendations with space requirements that do not exceed the specified limit.

   • Tuning Prioritization

     This section enables you to specify how SQL statements will be tuned. Complete the following steps:

     • From the Prioritize tuning of SQL statements by list, select a method by which SQL statements are to be tuned and then click Add.

     • Optionally, select Allow Advisor to consider creation costs when forming recommendations to weigh the cost of creating access structures against the frequency and potential improvement of SQL statement execution time. Otherwise, creation cost will be ignored. You should select this option if you want specific recommendations generated for SQL statements that are executed frequently.

   • Default Storage Locations

     Use this section to override the defaults defined for schema and tablespace locations. By default, indexes are in the schema and tablespace of the table they reference. Materialized views are in the schema and tablespace of the first table referenced in the query. Materialized view logs are in the default tablespace of the schema of the table that they reference.

4. Click Next.

   The SQL Access Advisor: Schedule page appears.

5. Proceed to the next step, as described in "Running SQL Access Advisor: Schedule".