View index recommendations

DPA identifies missing indexes that could improve the performance of specific queries. When DPA identifies a missing index, it creates a recommendation called an index advisor. Each index advisor includes information such as the index contents and estimated time savings.

View the list of index advisors for a database instance

To view advisors for a database instance, open the Tuning Advisors page:

• From the DPA home page, click the icon in the Tuning column.

  

• If you have drilled in to view information about a database instance, click the Tuning tab in the upper-right corner of the instance details page.

  

The Index Advisors section lists any index advisors for the most recent time period. Select a date from the drop-down menu at the top of the page to view advisors for a different period.

Index advisors are calculated once a day, at the end of the day. The most recent time period is the previous day.

For each index advisor, this section lists:

• The table and table columns to be indexed.
• The number of queries whose performance resulted in this index recommendation.
• The total wait time for all query executions during the selected time period.
• The estimated reduction in wait time as an amount and a percentage. This estimate is based on plan cost and step cost values.

Remove an index advisor from the list

If you determine that a recommended index cannot or should not be created, you can dismiss the advisor to remove it from the list. The advisor is hidden from all DPA users across all dates.

1. Click the Hide this advice icon on the advisor row. The Dismiss advice dialog opens.

2. (Optional) Enter an explanation of why you want to remove the advisor.

3. Click Dismiss.

   In the upper-right corner, DPA shows the number of hidden advisors for this database instance.

Add a hidden advisor back to the list

1. Hover over the number of hidden advisors to display information.

   

2. Click the Show this advice icon on the advisor row.

   The advisor is added back to the list across all dates.

View index advisor details

Click the blue arrow in the right column to display more information about the recommendation:

• The Index DDL is the command to create the suggested index. To copy the command, click Copy to clipboard.

• The Query origins section provides information about the query or queries whose performance resulted in this index recommendation. For more information:

  • To display all of the queries on the Find SQL page, click See affected queries.
  • To display information about a query on the Query Details page, click the query name.
  • To view the Oracle, SQL Server, or PostgreSQL execution plan for a query, click the plan hash number.

Advanced options for index advisors

The following advanced options are available to control how index advisors operate. Each option can be set globally or for a specific monitored instance.

Option	Description
INDEX_ADVISORS_BEST_PRACTICES_OVERRIDE	By default, index advisors do not recommend creating an index that is overlapping or wide, because these would violate table tuning best practices. To allow index advisors to recommend overlapping and wide indexes, set this option to True.
INDEX_ADVISORS_ENABLED	Index advisors are enabled by default. If you do not want DPA to generate index advisors, set this option to False.
INDEX_ADVISORS_WAIT_LIMIT	DPA does not generate index advisors for queries with negligible wait times (by default, less than 1 second). Edit this option if you want to change the default limit.