Documentation forDatabase Performance Analyzer

Access DPA query, table, or index advisors

DPA provides the following types of advisors:

  • Query advisors provide information to help you improve the performance of a specific query, including what type of waits were responsible for significant wait time, whether the statement was blocked by other sessions, whether execution plans include potentially expensive steps such as full table scans, and whether the execution plans for a query have changed.

  • Table advisors are generated when inefficient queries run against a table. These advisors provide aggregated information about the table, the inefficient queries that ran against it, and any existing indexes.

    Table tuning advisors are available for Oracle, SQL Server, Azure SQL DB, PostgreSQL, MySQL, and Percona database instances.

  • Index advisors identify indexing opportunities and provide estimates of how much time adding the suggested index could potentially save.

    Index advisors are available for Oracle, SQL Server, Azure SQL DB, PostgreSQL, MySQL, and Percona database instances.

The Tuning column on the DPA home page displays a warning or critical icon when advisors with a warning or critical status are available for a database instance. A green check mark in this column indicates that there are no advisors or that all advisors are informational.

For PostgreSQL and MySQL database instances, you can specify which databases DPA collects execution plans from.

View all advisors for a database instance

To view all advisors for a database instance, do either of the following to 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.

    A red or yellow bar on the Tuning tab indicates that critical or warning advisors are available.

The Tuning Advisors page displays the latest advisors:

  • Query advisors are calculated every hour. The most recent query advisors are for the previous hour.

  • Index and table tuning advisors are calculated once a day, at the end of the day. The most recent index and table advisors are for the previous day.

Use the drop-down menu at the top of the page to display advisors generated for a previous date.

View details

For detailed information to help you resolve performance issues:

  • Click a row on the Index Advisors table to view details about the suggested index.

  • Click a query advisor to open the Query Detail page, which displays detailed information about the query along with the most relevant statistics and metrics charts.

  • Click a table advisor to open the Table Tuning Advisor page, which displays aggregated information about the table and the inefficient queries that ran against it.