Taming the AWR Tsunami 06 - Correlations Among Performance Metrics

As we journey through the intricate landscape of Oracle performance metrics, we stand at the cusp of a transformative exploration. In my ongoing series, "Taming the AWR Tsunami," my mission is to help provide you a deeper understanding of AWR performance metrics and how to use them.  Read on to see one way to unearth some of the secrets of Oracle performance.

Correlations Among Performance Metrics

As we explore database metrics it is key to build an understanding (preferably before a problem strikes) of common correlations among performance metrics.  Without getting too deep into correlation analysis, I will give my definition – correlations is about understanding the data value relationships between variables (i.e. metrics).  To accomplish this in Oracle we can use the CORR aggregate function which produces a Pearson’s correlation coefficient.  CORR produces values in the range of -1 to 1 where 1 is perfectly correlated, zero is not correlated and -1 is inversely correlated.

Example Query:

For example:  the following query can be used to give me a cross product of all metrics against all metrics, to help me understand the data value relationships between variables. 

 Usage:

This query can help you identify patterns and relationships between different performance metrics. For example, if you observe a strong positive correlation between two metrics, it may indicate that changes in one metric tend to be accompanied by changes in the other metric. This insight can be valuable for performance tuning and troubleshooting.

The Query

with metric_set_1 as (select begin_time, metric_name, value 

from v$sysmetric_history 

where upper(metric_name) like upper(nvl(:metric_name1, metric_name)))

, metric_set_2 as (select begin_time, metric_name, value 

from v$sysmetric_history 

where upper(metric_name) like upper(nvl(:metric_name2, metric_name)))

/* main select CORR function */

select s1.metric_name "Metric Name 1", s2.metric_name "Metric Name 2"

, round(CORR(s1.value, s2.value), 7) AS "Pearson's Correlation"

from metric_set_1 s1, metric_set_2 s2

where s1.begin_time = s2.begin_time and s1.METRIC_NAME <> s2.metric_name 

group by s1.METRIC_NAME, s2.metric_name 

having CORR(s1.value, s2.value) is not null

   and (CORR(s1.value, s2.value) >= .75      
     or  CORR(s1.value, s2.value) <= -.75)

order by 3 desc ;

Query Structure:

Let me explain the structure of the above query.

The query uses two common table expressions (CTEs) (metric_set_1 and metric_set_2) to retrieve the values of the specified metrics (:metric_name1 and :metric_name2) from the v$sysmetric_history view. This view contains historical data for system metrics.  You could easily use v$sysmetric_summary or dba_hist_sysmetric_summary as well, but I will leave this exercise to the reader.

The main select statement calculates the correlation between each pair of metrics (s1.metric_name and s2.metric_name) using the CORR function.

The WHERE clause ensures that the correlation is calculated only for data points with the same begin_time and excludes cases where the metric names are the same (s1.METRIC_NAME <> s2.metric_name).

The HAVING clause filters the results to include only correlations greater than or equal to 0.75 or less than or equal to -0.75, indicating strong positive or negative correlations, respectively.

The result set is ordered by the correlation coefficient in descending order.

Example Query Output:

Summary:

Overall, this approach provides a systematic way to explore and understand the relationships among performance metrics in an Oracle database, allowing you to gain deeper insights into the system's behavior and performance.

I hope this installment in my ongoing series, "Taming the AWR Tsunami," helps you to dive deep into building a better understanding of Oracle performance metrics and their interrelationships.  In future posts I hope to help you unearth more secrets of Oracle database performance analysis.