Generating and acting on recommendations for changing the access paths for SQL statements

The Access Path Advisor examines the access plan that is chosen by the optimizer and identifies certain common access path issues. The warnings that this advisor provides can help you to understand where to look for trouble in an access plan graph or in the Access Plan Explorer.

Before you begin

About this task

The recommendations of the Access Path Advisor appear in the Access Path section of the Review Single-Query Advisor Recommendations page. The following steps are a guide to using this section to follow the recommendations.

Procedure

To generate recommendations for changing the access path of an SQL statement:

  1. After running the DDL scripts that were recommended by the Index Advisor, return to the Run Single-Query Advisors and Tools page of the Invoke section in the workflow assistant. Click the Invoke tab on the left side of the workflow assistant. If the Run Single-Query Advisors and Tools page is not open, click Run Advisors and Analysis Tools on the left side of the workflow assistant to open the page.
  2. Click the Select What to Run button.
  3. In the Select Activities window, select the Access path check box. Then, click OK. After the Access Path Advisor runs, the Review Advisor Recommendations page of the Review section opens. The Summary section shows whether there are any recommendations for revising the access path.
  4. In the Review Single-Query Advisor Recommendations page, double-click the Access path row in the Summary table. If this advisor has more than one recommendation, a row appears for each recommendation.
  5. In the Access Path section, view the underlined portion of the SQL statement. The recommendation refers to the underlined portion.
  6. Read the details of the recommendation, an explanation of the best practice behind the recommendation, and, if provided, an example of an implementation of the recommendation.
  7. Follow the instructions that the advisor gives for resolving the problem that led to the recommendation.

What to do next

Run the Query Advisor, if you have not already done so, and then run the Index Advisor.

Feedback