Using SQL Access Advisor to improve indexes and materialized views

Adding indexes and materialized views should improve query performance, but it's not always the case. Learn how SQL Access Advisor can ensure it does.

When you increase the number of materialized views and indexes against a set of tables, generally the query performance...

against those tables will improve. But this is no guarantee, and the Oracle SQL Access Advisor is designed to ensure an efficient path to the data.

Matthew MorrisMatthew Morris

Creating an index against the wrong column or a badly designed materialized view that goes unused could generate zero performance improvement. And whether or not performance improves, there is always a cost associated with creating indexes and materialized views -- both in disk space and the time to maintain them. When database administrators (DBAs) add these objects to the database, it is with the assumption that the benefits will outweigh the costs.

The SQL Access Advisor recommends an optimal set of materialized views, materialized view logs and indexes for a given workload. As part of its analysis, SQL Access Advisor weighs tradeoffs between space use and query performance. It will then generate recommendations that include one or more actions. For recommendations with multiple actions, the entire set must be implemented to achieve the full benefit.

Part of the analysis includes determining if one or more base tables should be partitioned. It will collect all individual partition actions into a single recommendation. Some or all of the remaining recommendations may be dependent on the partitioning actions being implemented. Index and materialized view advice is heavily dependent on the partitioning of the underlying tables.

The SQL Access Advisor API can perform the following functions:

  • Recommend materialized views and indexes based on collected, user-supplied or hypothetical workload information;
  • Recommend partitioning of tables, indexes and materialized views;
  • Mark, update and remove recommendations;
  • Perform a quick tune using a single SQL statement;
  • Show how to make a materialized view fast refreshable; and
  • Show how to change a materialized view so that general query rewrite is possible.

The advisor relies on structural statistics about table and index cardinalities of dimension-level columns, join key columns and fact table key columns. If these statistics are missing for a given table, SQL Access Advisor marks the queries referencing this table as invalid in the workload, and it will not make recommendations for those queries. You should also make sure to analyze existing indexes and materialized views.

SQL Access Advisor has two modes of operation: problem solving and evaluation. The default mode is problem solving. In this mode, SQL Access Advisor will attempt to solve access-method problems by looking for new objects to create. In evaluation mode, the advisor will only comment on existing access paths that the given workload will use. A problem solving run might suggest creating a new index, whereas an evaluation-only scenario will only produce recommendations such as retaining an existing index. The evaluation mode is useful in determining which indexes and materialized views are being used.

Advisor tasks define what it is you want to analyze and where the analysis results should be placed. It is possible to create any number of tasks, each with a given specialization. All are based on the same Advisor task model and share the same repository. Tasks are created using the CREATE_TASK procedure of the DBMS_ADVISOR package:


VARIABLE task_name VARCHAR2(255);

EXECUTE :task_name := 'MYTASK';


         ('SQL Access Advisor', :task_id, :task_name);

The input workload for the SQL Access Advisor is the SQL Tuning Set. An important benefit of using a SQL Tuning Set is that they can be referenced by many different advisor tasks. A SQL Tuning Set workload is created using DBMS_SQLTUNE. SQL Workload objects are pulled into a SQL Tuning Set using the DBMS_ADVISOR package:


You must link tasks to a SQL Tuning Set in order to generate advisor recommendations. You can create links with the ADD_STS_REF procedure, using their respective names to link the task to a Tuning Set. Once a connection has been defined, the SQL Tuning Set is protected from removal or update.


Before deleting a task or before a SQL Tuning Set workload can be deleted, you must remove any existing links between the task and the workload. Links are removed using the DELETE_STS_REF procedure.


You can generate recommendations by using the EXECUTE_TASK procedure. After the task completes, the DBA_ADVISOR_LOG table will show execution status and the number of recommendations and actions produced. EXECUTE_TASK is a synchronous operation, so control will not be returned to the user until the operation has completed, or is interrupted. The recommendations can be queried by task name in DBA_ADVISOR_RECOMMENDATIONS and the actions in DBA_ADVISOR_ACTIONS.


SQL Access Advisor can be an extremely useful tool for tuning the SQL performance on your system. Ideally you should periodically locate high-cost SQL statements in your database and place them into a SQL Tuning Set (STS). Once in an STS, you should evaluate the statements with SQL Access Advisor and SQL Tuning Advisor. Each advisor will make recommendations. Those recommendations can be used as input for Oracle's SQL Performance Analyzer to gauge the impact they would have. Used in combination, the advisors provided by Oracle can remove much of the guesswork and experimentation that has traditionally been required to resolve performance bottlenecks.

About the author:
Matthew Morris is a database engineer for Computer Sciences Corporation in Orlando, Fla. For over 17 years he has worked with the Oracle Database as a support engineer, database administrator, developer and architect.

Dig Deeper on Oracle database performance problems and tuning