View detailed information about a query

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:

• View waits, statistics, and metrics from any time period
• See what type of waits are affecting performance
• Review query and table advisors
• Examine statistics and metrics charts to correlate query wait times with other events

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

Open the Query Details page

You can open the Query Details page in any of the following ways:

• From a query advisor.

  When DPA identifies a query with possible performance issues, it creates a query advisor. Open the Tuning Advisors page to view all advisors for a database instance. Click any query advisor to view information on the Query Details page.

• From a chart legend.

  When you are viewing data on a DPA Trends chart, click the SQL hash or name in the chart legend to view information on the Query Details page.

  

• From the Find SQL page.

  When you search for a SQL statement, click the blue arrow to the right of the Wait Time to display more information about a SQL statement in the search results. Then click the SQL hash or SQL name to view information on 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 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 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 (by default) so you can correlate query wait times with other events during the same time period.

If you do not want the Top Waits chart to remain visible, click the pin in the top-right corner to unpin it.

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.

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.