Find and investigate unusually long wait times (anomalies)

DPA's anomaly detection algorithm identifies unexpected increases in wait time. DPA collects historical data and uses it to "learn" what normal is. DPA's proprietary algorithm makes predictions based on this data. When wait times for a time period are higher than expected, DPA reports an anomaly.

Get notified when wait time is higher than expected

Configure the Database Instance Wait Time Anomaly alert to be notified whenever wait time is significantly higher than expected for a database instance. (To do this, configure a Wait Time alert and select Database Instance Wait Time Anomaly as the Alert Type.) This alert is triggered if the wait time for an instance was abnormally high during the most recently completed hour.

View information about wait time anomalies

The wait time meter on the DPA home page indicates recently detected anomalies. Drill in to a database instance to view more detailed information on the Anomaly Detection charts.

Wait time meter

On the DPA home page, the wait time meter for each database instance provides information about recent database activity:

• The bar length shows the amount of wait time for each database instance as compared to all other monitored instances. Use the bar length to quickly identify instances with the highest wait times.

• The bar color identifies instances where DPA detected higher-than-expected wait times (anomalies). Yellow indicates a warning status, and red indicates a critical status. (For information about these thresholds, see Anomaly thresholds.)

The wait time meter reflects recent database activity (a rolling one-hour time period). It is updated every 10 minutes to show the wait activity that occurred during the previous 60 minutes.

Anomaly Detection chart (30-day period)

If DPA detects wait time anomalies for a database instance, click the database instance on the DPA home page to drill in for more information. The Top SQL Statements chart and the Anomaly Detection chart show information from the past 30 days. These charts work together to help you understand the waits occurring in this database instance:

• The Top SQL Statements chart identifies the SQL statements with the highest wait times. In many cases, these are candidates for tuning. But in other cases, further tuning is not possible or the wait times are not a problem. The large bars are normal, and you are more interested in unexpected increases in wait time.

  An anomaly is detected when the combined wait time for all SQL statements is higher than expected. The Top SQL Statements chart shows only the SQL statements with the highest waits, which might not be responsible for the anomaly.

• The Anomaly Detection chart identifies days when wait times were significantly higher than expected (wait time anomalies occurred).

Each bar on the Anomaly Detection chart shows a roll-up of the amount of wait time that the database instance experienced during that day.

• Red segments indicate that wait times for one or more hours were much higher than expected (critical).

• Yellow segments indicate that wait times for one or more hours were higher than expected (warning).

• Green segments above the baseline (0) indicate that wait times for one or more hours were within the normal range, but slightly higher than expected.

• Green segments below the baseline indicate that wait times for one or more hours were lower than expected.

DPA classifies all lower-than-expected wait times as normal, and does not alert on them.

Show only warning and critical segments

To focus only on segments that indicate wait time anomalies, you can deselect Show Normal Bars to hide the green bars.

Drill in further

Click a bar that represents a day when anomalies occurred to display the Anomaly detection chart for that day.

Anomaly Detection chart (one-day period)

The Anomaly Detection chart for a one-day period shows the differences between the predicted wait times and actual wait times for each hour. The bar for the current hour shows the differences during the six most recent 10-minute intervals (a rolling one-hour time period).

The baseline (0) represents the predicted value for the hour.

Investigate higher-than-expected wait times

After you determine when anomalies are occurring, you can use either query performance analysis or DPA reports to help you determine which SQL statements are responsible for the anomalies.

Determine when anomalies occurred

Use the Anomaly Detection charts to determine when anomalies occurred, and to see which SQL statements were running during that time period.

1. From the DPA home page, click the database instance that is experiencing anomalies to display the 30-day Anomaly Detection chart.

2. Click a bar that represents a day when wait times were much higher than expected.

   The one-day Anomaly Detection chart shows the hours when anomalies occurred. In this example, the 2 PM hour had the highest unexpected wait times.

   

3. Open the Anomaly Detection chart for a one-day period, and find the hours with large red segments. These are the hours when wait times were much higher than expected.

4. Click the bar that represents the hour, and view information about the SQL statements with high wait times that ran during that hour.

   

Display historic wait times and performance analysis for these queries

To determine which SQL statement is causing the anomaly, you can use the Query Detail page to view the historic wait times. It's usually a good idea to start with the bars at the top of the list. Also remember that more than one SQL statement might be causing the anomaly.

1. Click a bar that represents a SQL statement.

   The Query Details page displays wait times for that SQL statement during the selected one-hour time period.

2. Click the time period at the top of the page and change the time range. For example, select Last 30 days or Last 90 days.

   In this example, the wait times for February 2 are clearly an anomaly.

   

3. You can also scroll down to review DPA's query performance analysis for this SQL statement.

   In this example, we can see that the SQL statement was being blocked by other queries when the anomaly occurred.

   

Use DPA reports to review wait or query details

After you determine when the anomalies are occurring, you can create a report to review the wait times for that hour during the last 30 days to look for unusually high wait times.

1. Click Reports.
2. Select the database instance that is experiencing anomalies.

3. Select Top Waits as the Report Type.

   

4. Click Report Options.

5. Under Waits to Display, select the Top 50 Waits.

   

6. Under Dates to Display, select Last N Days as the Date Range, and leave 30 as the number of days.

7. Change the Hour Range to the time period when anomalies are occurring.

   

8. Click Display Report and review the wait times.

   In this example, the anomaly stands out.

   

Investigate lower-than-expected wait times

If wait times are much lower than expected, consider investigating to determine whether any SQL statements that normally run during that time period are missing.

1. Open the Anomaly Detection chart for a one-day period, and find the hour with the largest green segment below the baseline. Note the date and hour.
2. Click Reports.
3. Select the database instance, and select Top SQLs as the Report Type.
4. Click Report Options.

5. Under SQL Statements to Display, select the Top 50 SQL Statements.

   

6. Under Dates to Display, select Last N Days as the Date Range, and leave 30 as the number of days.

7. Change the Hour Range to the time period when wait time was much lower than expected.

   

8. Click Display Report and review the SQLs that ran each day to help determine if anything is missing.