An Example of Using Multidimensional Analysis
 Case Study Summary

This example describes a simple case scenario of how we use multidimensional analysis model to detect performance bottleneck and find out the root cause.

The list of steps include:

 Step 1 : Use AAS to identify when DB Time increases as performance bottleneck starts

The database performance bottleneck can usually be identified by checking the time frame when AAS is higher than normal.

AWR AAS is derived from DB Time. As shown below, AWR DB Time had the biggest increase in time slot of begin snapshot id =73527 and end snapshot id = 73528. 'DB Time S' is 'DB time in seconds'

ASH AAS is derived from Session Count. As shown below, ASH Session Count had the biggest increase in the same time slot. As we can see, the AWR AAS and ASH AAS matched pretty well in this case.

 Step 2 : Check dimension 'wait event' to see which wait event contributes to the increase of DB Time

To understand why DB Time increases, wait event is always the first dimension to check because AWR data measures which wait events have the most significant percentage of wait time with respect to the total DB time.

AWR showed the wait event 'read by other session' was a special contributor to DB Time during the time period of performance bottleneck.

Using dimension filtering on wait event 'read by other session', we see a summary of the percentage of its wait time with respect to the total DB Time over a time range.

Wait event is a common dimension for both AWR data and ASH data and it needs to be cross-checked. ASH confirmed the same wait event started showing on the top list of sampling in the same time slot.

Using dimension filtering on wait event 'read by other session', we see a summary of its percentage with respect to all samples in the sampling over a time range.

 Step 3 : Correlate dimension 'wait event' with dimension 'SQL'

As the wait event ‘read by other session’ became the symptom of performance bottleneck, we need to find out what was the root cause of it. We can correlate dimension ‘wait event’ with other dimensions in order to find out whether a cause-effect relationship exists.

To correlate dimension ‘wait event’ with dimension ‘SQL’ is a common practice. It is to answer a typical question like this: for a given wait event, was there a specific SQL statement which caused the increase of its wait time?

As shown below, dimension probing techniques have been used to reveal the SQL correlated with wait event ‘read by other session’.

For the SQL ID found, we then checked the SQL text and SQL Plan. As we can see, two SQL statements were doing full table scan on the same table. They caused wait event 'read by other session' by competing for the same set of data blocks.

 Step 4 : Correlate dimension 'wait event' with dimension 'object'

Can we confirm the cause-effect relationship we found out between dimension 'wait event' and dimension 'SQL'? Yes. One dimension always correlates with others, and therefore, we always correlate one dimension with multiple dimensions and see if a consensus on relationship can be made.

To correlate dimension ‘wait event’ with dimension ‘object’ is another common practice.

 Step 5 : Correlate dimension 'wait event' with dimension 'file'

To relate dimension ‘wait event’ with dimension ‘file’ is also a common practice.

 Step 6 : Correlate dimension 'SQL' with dimension 'service', 'application', or 'session'

At this point, we have been able to confirm the SQL as the root cause of performance bottleneck. To find out which application or session was responsible for the SQL identified, we can correlate dimension ‘SQL’ with dimension ‘service’, ‘application’, or ‘session’.

The ‘Search by’ type of report is another way of using dimension filtering.

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