Crosscheck Multiple Dimensions to Separate Symptoms and Root Cause
 Case Study Summary

This example describes a case scenario of trouble shooting database performance issue due to IO bottleneck. Oracle uses many difference kinds of IO and corresponding wait events. When IO bottleneck happens, multiple IO related events will surface. The key point is to cross check wait event with IO statistics and SQL Stats to confirm what are symptoms and what are root casues.

The list of steps include:

 Step 1: Check DB Time to determine when performance bottleneck started

 Step 2: Check instance load profile to determine which area had the biggest change during performance bottleneck

 Step 3: Check IOPS to confirm the IO workload was increased during performance bottleneck

 Step 4: Perform time series analysis on wait events to check which event was moving up to the top during performance bottleneck

 Step 5: Correlate dimension 'wait event' with dimension 'IO Statistics'

 Step 6: Correlate dimension 'wait event' with dimension 'SQL' for wait event 'direct path read'

We further check SQL statistics to confirm that wait event 'direct path read' was the root cause of performance bottleneck

In AWR, we need undertand the relationship between SQL statistics and wait event statistics in order to correlate dimension 'wait event' with dimension 'SQL'.

 Step 7: Perform time series analysis on SQL Stats for the SQL statements identified

 Step 8: Check SQL text and SQL plan for the problem SQL statements identified

 Step 9: Correlate dimension 'wait event' with dimension 'SQL' for wait event 'log file sync'

We further check SQL statistics to confirm that 'log file sync' was the victim, not the root cause, of performance bottleneck

In ASH, we can correlate dimension 'wait event' with dimension 'SQL' for the wait event name directly.

 Step 10: Perform time series analysis on SQL Stats for the SQL statements identified

The SQL stats shows that the SQL statement related to wait event 'log file sync' was victim of performance bottleneck.

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