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. Index advisors are available for Oracle and SQL Server database instances.
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.
Click the Hide this advice icon on the advisor row. The Dismiss advice dialog opens.
(Optional) Enter an explanation of why you want to remove the advisor.
In the upper-right corner, DPA shows the number of hidden advisors for this database instance.
Add a hidden advisor back to the list
Hover over the number of hidden advisors to display information.
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:
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.
||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 are enabled by default. If you do not want DPA to generate index advisors, set this option to False.|
||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.|