Correlation reports for Statspack and AWR data

The upcoming now available version of Mumbai will adds correlation reports for Statspack and AWR data.

Here is how it works: Once you have one or more SP/AWR reports for “Instance activity”, “Wait events” or “Top SQL” open in Mumbai, you can click on “Correlate by time”.

Here’s an example: In the following screenshot you see an SP Wait events diagram which shows an interval of high wait times for wait event “SQL*Net more data from DB link”. Also notice that there is already a SP Top SQL report open.

With the data for “Wait events” and “Top SQL” already loaded in the background, we can now click on “Correlate by time”.

What we get then is a new window which shows two columns with identifiers for data series for wait events, SQL_IDs or system statistics (depending on what kind of reports you had open) and a third column showing a correlation value within -100% and +100%.

A high correlation value means that the two data series have a strong correlation over time. A high negative value means that when the first series has high values, the second series has low values and vice versa.

You can browse the correlation data in this table, but the significant correlation values for a data series are also shown as a hint when you hover the mouse over the graph for a series.

Going back to our example above, it looks like this:

This shows that there is a correlation (over time) between the “SQL*Net more data from DB link” wait event and the SQL statement with the SQL_ID “f410crm1ck46k”.

(EDIT 1:) If we select this statement on the “Elapsed time” tab of the “Top SQL” page, we can visually confirm that there is a correlation over time between the wait event and the elapsed time for this SQL_ID.

Here’s another example of one SQL statement with a correlating value to another SQL statement:

This seems to indicate that the SQL with SQL_ID “cj5ywur2kvhj2” is called from within the parent SQL call for that the graph is shown above. You can see this kind of correlation frequently with PL/SQL blocks and scheduler jobs.

I guess these example show what the correlation report can do for you. I hope you find it useful.

Some additional notes:

  • The correlation report is time based, what means that there is not necessarily a logical correlation between two series. It could be just coincidence.
  • The correlations are calculated not by the absolute values for the data series, but on the relative series values compared to the maximum value of the data series. This is the reason why graphs for series with very high absolute values can have a high correlation value with graphs that have low absolute values. (EDIT 3: As Sergei pointed out in the comments below, it actually doesn’t matter if the correlation is calculated based on absolute or normalized values.)
  • You might not see all correlations that actually exist. For example, if one execution of an SQL statement spans multiple snapshots, the wait events produced by this SQL will show up in all of these snapshots. However, the SQL execution times in the Top SQL report will only show up in the snapshot when the execution finished. Though there is a logical correlation between the SQL execution time and the wait events triggered by the execution of the SQL, the correlation report can’t find it, because the correlation calculation is snapshot based.
  • (EDIT 2:) Special thanks and greetings to Yuri van Buren! His “Mining for gold in the AWR” session at the DOAG conference and our conversations inspired me to build the correlation report.