Printer Friendly

Uniting Operations Research with time-based DB performance analysis.

MOST ORACLE PERFORMANCE ANALYSIS is now time-based. But it is "total time"-focused: Time to process a SQL statement, a batch process, or the CPU consumed plus Oracle wait time that occurred over an interval of time. This is a fantastic way to approach optimization because it is easy to monitor improvement and it is closer to what a user is experiencing. And, with just a couple of twists, we can unite Operations Research (OR) queuing theory with the Oracle time-based approach, opening up an entirely new arena for performance analysis.

The Setup

It takes time to process work, so let's clearly define both the time and the work. We'll describe an Oracle system experiencing cache buffer chain latch contention in terms of the buffer accesses. This is like describing the weather in terms of inches of rain or the wear on a tire by the miles driven, so our unit of work will be defined as a buffer access, which is commonly called a buffer get or logical IO (LIO for short).


Now, suppose over a 30-minute period of time, 218M buffers were accessed, 9000 seconds of CPU was consumed, and 23000 seconds of Oracle nonidle wait time occurred. (Oracle processes are either consuming CPU or waiting for something. There is no other option.) Another way to express this is that, on average, over the 30-minute period, it took 0.1468ms to process a single logical IO. We have just calculated the response time, which is the time it takes to process a single unit of work.

Interestingly, as the database workload increases, the CPU consumed to process a single unit of work is nearly constant, yet the Oracle wait time increases. If this sounds a lot like service time and queue time, it is because the behavior is very similar--so similar in fact that we can use this Oracle "service time" and "queue time" just as OR queuing theory indicates.

Continuing with our example, the service time will be the CPU consumed per unit of work, which is 0.0413ms/LIO. The queue time will be Oracle wait time consumed per unit of work, which is 0.1055ms/LIO. Adding the service time and the queue results in a response of 0.1468ms/LIO.

Response-Time Curve

To complete the picture, we also need the arrival rate. Loosely speaking, this is the rate at which Oracle processes work, which in our case is represented by logical IOs. Since more than a 30-minute-period, 218M logical IOs were processed, the arrival rate is 121.1111 LIO/ms. With this metric, we can plot a single point on the classic response-time curve plane. The response time is the vertical axis and the arrival rate is the horizontal axis.

Here is the response-time equation for a CPU-based system. Since we are focusing on in-memory CPU activity, the CPU- as opposed to the IO-response-time formula is more appropriate.

R = S/((1-(L*S)/M)^M)


R is the response time; 0.1468 ms/LIO

S is the service time; 0.0413 ms/LIO

L is the arrival rate; 121.1111 LIO/ms

M is the number of effective servers. A server serves transactions, which will equate to roughly the number of CPU cores.

We have one missing variable, which is M. If you are using MS-Excel, you can use the goal seek function to solve for M, but I have created a simple web application to do this, as well at http://filezone.orapub. com/cgi-bin/msolve.cgi. In this example, M is 5.3222.

How to Use This Unification

Every performance solution will impact one of the response-time equation variables. For example, if the SQL is tuned, the workload will be reduced, which means the arrival rate will be reduced. And if an Oracle instance parameter is changed, making Oracle more efficient, the CPU required to process a single unit of work will decrease; that is, the service time will be reduced. By quantifying these changes, injecting them into a response-time equation, plotting the point and replotting the initial curve and our two solutions, performance solutions can be visually compared.


Uniting OR with time-based database performance analysis opens up a powerful analytical tool when combating intense performance situations. The process will deepen your performance analysis understanding and your ability to help non-technical people understand what we are doing and why.

An expanded version of this article appears in the December E-Edition of DBTA at Get involved--the Independent Oracle Users Group (IOUG) represents the voice of Oracle technology and database professionals. Go to

Craig Shallahamer is an Oracle ACE Director, author of the book, Oracle Performance Firefighting, and a teacher specializing in Oracle performance firefighting and predictive analysis.
COPYRIGHT 2011 Information Today, Inc.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2011 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:A WIDER VIEW
Author:Shallahamer, Craig
Publication:Database Trends & Applications
Date:Dec 1, 2011
Previous Article:Compliance and data access tracking.
Next Article:Megatrend: virtualization.

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters