DPA 12.0 feature: Query performance analysis

11/1/2018 1:04 PM

6/28/2019 9:00 PM

DPA 12.0 intelligently assembles the most relevant data about a query and displays it in one place: the new Query Detail page. This page displays:

• Top waits, color-coded by the type of wait

• Query advisors and any table tuning advisors

• Intelligently assembled charts to help you investigate the issue

Database Performance Analyzer

Top waits 

A chart at the top of the page shows the query's execution time for the selected time period. To change the selected time period, you can:

• Click a bar to drill in to that time period.

• Click the time period at the top of the chart to specify a different period. You can select a predefined time period or enter specific dates.

  

The bars are color-coded by the type of wait. Click the  next to an entry in the legend to display detailed information, including possible resolutions. You can also hover over an entry in the legend to see only that type of wait on the chart.

Query and table tuning advisors 

The Query Advisors section shows the latest advice for the selected time period. Click the links to display detailed descriptions and suggestions for resolving the issue.

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. 

Intelligently assembled charts 

To help you find the root cause of performance issues, the Query Details page includes the most relevant statistics, blocking, plans, and metrics charts. As you scroll to view these charts, the Top Waits chart at the top of the page gets smaller but 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. However, you can manually add other 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. 

What is the predominant type of wait? 

The predominant type of wait is responsible for the majority of the time that the query spent waiting during the specified period. To be considered predominant, the type must be responsible for more than 20% of the total wait time for that period. 

Note: The default threshold is 20%. You can change the threshold by adjusting the PREDOMINANT_WAIT_THRESHOLD advanced option.

DPA uses the predominant type of wait to determine which charts to display by default. 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.

Investigating an issue using the Query Detail page 

The following example shows the top waits for 30 days on the Query Detail page. You can see that the wait time started to increase around April 23. 

Scroll down the page to review the charts. Notice that:

• The Statistics chart shows that the Number of Executions is unchanged but Wait Time Per Execution increased significantly around April 23.

• DPA detected a plan change and displayed the Plans chart automatically. The Plans chart shows that the increase in wait time occurred at the same time the plan changed. 

On any chart, click the bar that represents April 23 to see more details about that day. The charts show that the increased wait time definitely correlates to the plan change. You can also see the instance statistics, which don't indicate any kind of resource pressure.

From here, you can drill down into a specific hour, or you can click the plan hashes and see what changed.

Learn more 

See the DPA 12.0 release notes to find out about other new features.