2011 IOUG Presentation: Unit Of Work Time Based Analysis

Posted on 28-May-2011 by Craig Shallahamer, craig@orapub.com

A couple of weeks ago I was given the opportunity to submit another presentation for the upcoming April 2011 Collaborate/IOUG conference in Orlando, Florida. I am particularly excited about this presentation because in terms of Oracle Database tuning and performance analysis and doing experiments, this is my current passion. And, over the last few months I have been able to significantly purify and clarify my thinking in this area. I will be posting the conference presentation once it's complete.

The session number 484 and I will make the presentation on Wednesday afternoon April 13. While the official submission title is, Unifying Time Based Performance Analysis a better title would be, Unit of Work Time Based Performance Analysis. If you're going to be at the conference, I hope you can make the presentation!

What's it about? If you have attended my Advanced Oracle Performance Analysis course, read the last chapter in the Oracle Performance Firefighting book, or read my paper Evaluating Alternative Performance Solutions you will have a pretty good idea about what the presentation is all about. Without specific reference to this topic, I also blogged about this in detail in my four part series entitled, Altering insert commit batch size.

Here is the conference abstract:

Time based performance analysis typically focuses on a specific process, SQL statement, or time interval (such as an hour or day). While this is the indeed valuable, switching the focus to the time related to complete a single small unit of work has a number of distinct advantages employed by computing system performance analysts for many years.

By focusing on an individual unit of work, the time to complete each unit of work can be broken down into the classic performance analysis categories of service time and queue time. The "leap" is made by placing a unit of work's CPU consumption into the service time bucket and non-idle wait time into the queue time bucket. Having completed this transformation, Oracle performance analysis can now benefit from the history of proven methods, theory, and mathematics of classic computing system performance analysis. These benefits include predictive analysis, alternative hardware and architectural analysis, and understanding how Oracle performance solutions will impact performance down to the unit of work up to the elapsed time of a SQL statement or an entire process. This type of analysis also leads to highly communicative visual representations.

Since this approach is new to many DBAs, data from multiple real Oracle systems is used to clearly demonstrate that indeed real Oracle systems behave surprisingly close to the performance mathematics.

This presentation will lead the listener step-by-step through the analysis process starting with a standard Statspack/AWR report, transforming the interval time based analysis into a unit of work time based analysis, how to graphically display the situation, and how the impact of performance solutions can be anticipated. All the tools and templates presented a freely available and special effort is made to make the analysis very practical.

Thanks for reading!


If you have any questions or comments, feel free to email me directly at craig at orapub.com.

Using ASH Data To Solve A Performance Problem Using A Time Based-Like Approach Altering Oracle Database DML Batch Sizes (Log File Sync) - Part 2 Impact Of Oracle Database Consistent Reads