Use DPA's query performance analysis to find the root cause of performance issues

To help you investigate the root cause of a query's performance problems, DPA intelligently assembles the most relevant data about the query and displays it on the Query Details page. Use the Query Details page to:

See an example of using the Query Details page to investigate an increase in wait time.

Open the Query Details page

Click the SQL hash or name in any chart legend to open the Query Details page.

Select a time period

All data on the Query Details page reflects the selected time period, which is displayed at the top of the page.

When you open the Query Details page, it defaults to the time period selected for the previous chart. For example, if you open the Query Details page while viewing the Top SQL Statements for one day, the Query Details page shows data for that day.

To select a different time period, you can:

  • Click a bar to drill in to that time period.
  • Click the date range at the top of the page to open the date picker. Then select a predefined time period or enter specific dates.

See what type of waits are affecting performance

The Top Waits chart at the top of the page shows the query's execution time for the selected time period. The bars are color-coded by the type of wait. Knowing what type of waits are causing the performance issue can help you determine how to fix the issue.

On this chart, you can:

  • Click the next to an entry in the legend to display detailed information about that type of wait, including possible resolutions.

  • Hover over an entry in the legend to dim other waits in the chart and better visualize the impact of this type of wait.

Review query and table tuning advisors

The Query Advisors section shows the latest advice for the selected time period. Query advisors provide information such as:

  • What type of wait activities the SQL statement spend significant time on.
  • Whether the statement was blocked by other sessions.
  • Whether the statement took longer than normal to execute.
  • If multiple execution plans were used, or if plans include potentially expensive steps such as full table scans.

If any table tuning advisors included information about this query, you can click through for aggregated information about the table and all inefficient queries that ran on it.

Correlate query wait times with other events

To help you find the root cause of performance issues, the Query Details page includes the most relevant statistics, blocking, plan, and metrics charts. Sections with data to display are automatically expanded. Other sections are collapsed by default. For example, if there is no blocking data, the Blocking section is collapsed.

When you scroll down to view these charts, the Top Waits chart at the top of the page remains visible so you can correlate query wait times with other events during the same time period.

DPA uses the predominant type of wait and other information to automatically select the most relevant charts. For example, if the predominant type for an Oracle database instance is Memory/CPU, DPA includes charts such as OS/CPU Utilization, CPU Utilization by DB, and Buffer Cache Hit Ratio.

The predominant type of wait is the type responsible for the majority of the time that a query spent waiting during the specified period.

To be considered predominant, the type must be responsible for more than a certain percent of the total wait time for that period. By default, this threshold is 20%. You can change the threshold by changing the advanced option PREDOMINANT_WAIT_THRESHOLD.

You can manually select other statistics or metrics charts to include.

Display other statistics charts

  1. If the Statistics section is collapsed, expand it.
  2. On the right side of the Statistics section, click Add Statistic.
  3. Select the statistics you want to include, and deselect any you want to remove.

  4. Click outside the drop-down to close it.

Display other metrics charts

  1. If the Instance Resource Metrics section is collapsed, expand it.
  2. On the right side of the section, click Add Metrics.

    The Add Metrics dialog box opens.

  3. Filter or sort the list to locate the metrics you want to add:

    • Select one or more categories to filter by those categories.

    • Enter a string in the Search box to show only metric names containing that string. (Wildcards are not supported.)

    • Sort by name or by category.

  4. Select one or more metrics. Use the selection drop-down menu to quickly select multiple metrics:
    • All: Selects all metrics on the current page.
    • All Pages: Select all metrics on all pages.

  5. Click Save Changes to display the selected metrics.