The DPA approach to investigating performance issues

See this video: How to Navigate the Database Performance Analyzer UI to Diagnose Database Performance Issues.

DPA provides a unique approach to investigating performance issues. Use the wait-based analysis approach in DPA to focus on issues that provide the greatest performance improvements.

The agentless architecture in DPA uses less than one percent of database resources, so it can monitor production systems without affecting their performance.

Wait-based analysis

Traditional database monitoring tools focus on database health metrics to troubleshoot performance problems. DBAs can spend hours tuning the database to improve these metrics, only to find that their changes had little or no effect on performance.

Instead of database health metrics, DPA focuses on application and end-user wait times. DPA graphically shows you where the longest wait times are, and it also identifies time periods when wait times that are longer than expected (anomalies). You can drill in to find the root cause of a performance issue and get advice on how to fix it. When you use DPA to find and fix the issues that are directly responsible for long wait times, you can deliver performance improvements that get noticed.

Use the DPA home page to quickly identify database instances with high wait times or anomalies, and then drill down for details.

Query performance analysis

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 this information to:

Find out what type of waits are affecting performance, and view detailed information and recommendations about each type of wait
Review query and table advisors
Examine statistics and metrics charts to correlate query wait times with other events

DPA uses the predominant type of wait and other information to automatically select the most relevant statistics, blocking, plan, and metrics charts. When you scroll down to view these charts, the Top Waits chart remains visible so you can correlate query wait times with other events during the same time period. This information provides the context you need to identify the root cause of complex performance problems.

Table advisors

You must consider many factors when you're determining how to improve the performance of an inefficient query—that is, a query that reads a large number of rows but returns relatively few. DPA's table advisors help you make informed decisions. Each day, DPA identifies tables that had inefficient queries run against them. For each table, the Table Tuning Advisor page displays aggregated information about the inefficient queries, the table structure, and any existing indexes. This information can help you answer questions such as:

Which steps should I focus on when I review the plan for the query?
How many indexes currently exist on the table and what do they look like?
Can I add an index to improve performance?
Are statistics stale?
How much churn (inserts, updates, and deletes) does the table undergo?

Anomaly detection

DPA uses an anomaly detection algorithm to identify unexpected increases in wait time. During certain time periods, high wait times might be normal. DPA uses historical data to "learn" what normal is and makes predictions based on this data. When wait times for a time period are significantly higher than expected, DPA reports an anomaly.

Using DPA to investigate problems