Multidimensional Analysis of AWR & ASH data
 What is a dimension?

To simply put, a dimension is a variable which can be used to categorize data for measurements.

For a given dimension, it comprises three basic components:

  •   Data - The purpose of defining a dimension is to study data and the data is the target.
  •   Data categorization - A dimension represents a category of the data.
  •   Data measurement - A dimension is associated with one or multiple measurements to measure data.
 Oracle performance data

Oracle database has two different categories of performance data: cumulative statistics and sampled data.

Good examples of cumulative statistics are those available in DBA_HIST_SYSSTAT view. Cumulative statistics are information summed up at database instance level. Their value keeps growing as database activities go on and will be reset when database instance restarts. When cumulative statistics are examined, the delta value between two points of time is calculated and the amount of change is measured. Metrics are derived from cumulative statistics by calculating the rate of their changes against a time unit, or other units like transaction, database call etc.

Good examples of sampled data are those available in DBA_HIST_ACTIVE_SESS_HISTORY view. Oracle uses ASH framework to collect session level information by sampling active sessions on database. When sampled data is examined, their percentage within each sampling set and their frequency across multiple sampling sets are calculated. The significance threshold is therefore derived.

Because Oracle uses AWR as framework to save all performance data into DBA_HIST views, there is a tendency to treat ASH as a component of AWR. However, Oracle provides AWR report and ASH report as two separate tools to analyze performance data, which marks the clear differences between these two categories:

  •   AWR data is cumulative data at database instance level
  •   ASH data is sampled data at session level

From the perspective of Oracle database tuning, DBspeed considers the 'data' as performance data stored in both AWR and ASH.

 Categorize Oracle performance data consistently

For the purpose of streamline these two different sets of performance data and categorize them, DBspeed defines a list of dimensions including:

  •   'common' dimensions which are consistent across AWR and ASH data
  •   'unique' dimensions which are specific to either AWR data or ASH data

DBspeed believes all dimensions are variables which contribute to the true image of how database performance looks like. It advocates multidimensional analysis based on the following two rules:

  •   Common dimensions serve as link between AWR data and ASH data
  • DBspeed believes that the common dimensions not only serve as the link which allows drill down for analysis throughout these two different performance data sets, but also as the important cross-check points which should always be compared and contrasted.

  •   One dimension always relates to others
  • DBspeed believes that dimensions are tangled delicately in one way or another. When performance issue happens, and we see the performance data in one dimension changes during a particular period of time, we should always check other dimensions for either corresponding symptoms, or root cause explanations. In order to probe the relationship among dimensions, DBspeed offers many features such as adhoc dimension expanding, dimension peeking, dimension filtering, etc.

 Putting the measurement in context

Oracle sets the tone of how to measure performance and the format in AWR and ASH reports have been shown as good examples. To make those measurements more meaningful, DBspeed adopts two basic techniques:

  •   'time-series' analysis
  • DBspeed believes that the performance data in AWR and ASH at performance trouble time are skewed compared to those at the non-trouble time. The performance data are more meaningful when being reviewed under the context of a time frame window where measurements can be driven down to a series of consecutive time unit (such as AWR snapshot interval, or hour, or day, etc) and therefore the data changes over time can be revealed.

  •   'data filtering' based on user-defined criteria
  • The volume of performance data can grow rapidly. Under situations where only a certain piece of performance data are interested, DBspeed believes that being able to filter data before applying measurement is important for a speedy analysis. DBspeed allows the user-defined filtering criteria, which puts the total control in users’ hand and offers great flexibility.

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us