Loading Oracle Part 3: Exponential Interarrivals & a bit of queuing theory
Introduction
In part two of the blog post series we have seen how to create a transaction load within the database itself. One of the missing things we have kept till this part is the concept of interarrival time. Remember that in previous part, as an example, we’ve scheduled 20 sessions and let them to insert 5000 records at the same time. But can you see the problem about this method?
Having a single 5000 insertion time statistics for each session usually causes a great variation in computations. This is all about what we measure. Remember that we are trying to approximate the response time of Oracle in a concurrent environment. In previous post we have only an average and a variance. Nothing more… But at the end of this model we obtain some mathematical models rather than just numbers. So what was wrong with the precious one? Let’s try to build a correlation with a supermarket (more or less both fit into queuing theory facts).
Imagine a market with 20 cash desks (an Oracle instance whose job_queue_processses parameter is set to 20). Market is closed initially and at 09:00 doors are opened. Then 20 customers arrive simultaneously and buy 5000 goods and they pay in different cash desks. We measure the time between arrival and departure of each customer and make some statistics. I think the problem is clearer now. Let me summarize what is wrong:
 Customers arrive suddenly at the same time just like 20 simultaneous sessions.
 There are only 20 timing measurements. Modeling a system with 20 points is not so sound.
Tuning the Previous Model
Previous model we have utilized in the second part of the post has those problems. Let’s now see the methods for correcting those errors:
 We rather use stochastic stationary Poisson arrivals instead of n simultaneous arrivals at a time.
 We will do statistics over much more than 20 timing measurements.
 We try to build mathematical stochastic processes.
Poisson Arrivals & Queuing Theory
Let’s first talk about the real characteristics of super market customer arrivals. Assuming that there can be only one customer at exactly the same time seems to be a sound assumption. Next assumption is another sound one. The number of arrivals between t and t+s depends only to s. In other words the expected number of customers between 09:0009:10, 09:1009:20 or 11:2111:31 is equal to each other (Even we will relax this assumption also in next parts of the post). Customers arriving between [t,t+s] and [t+s,t+v] is totally independent. Each customer decides totally free to come without interacting with other customers. That’s all. I think all seem sound. Let me summarize them more formally once more using the words of infamous Law & Kelton adapting to our Oracle scenario:
 Sessions are opened one at a time. (It is very rare that session opening timestamp differences between two consecutive Oracle sessions equal to 0).
 The distribution of N(t+s) – N(t) is independent of t for all t,s>0
 N (t+s) – N(t) (the number of arrivals in the time interval (t, t+s]) is independent of {N(u), 0<u<t}.
If those three assumptions hold, this type of arrivals called stationary Poisson arrivals. Fortunately, most queuing systems follow this distribution. I won’t give formulas on Poisson distribution but let me illustrate what some Poissons look like:
Figure 1
We will be talking about those histograms a bit and try to understand, informally, the characteristics of a Poisson distribution. Logically those distributions are discrete and may take any discrete value in [0, infinite). Mode of the distribution is always somewhere around lambda. There may be other visual characteristics but now I will focus on the meaning of these histograms. What they mean? Take lambda=2 for example. Let’s read the figure:
 Given that on the average 2 customers arrive per minute to XYZmarket, what is the probability that in 1 minute exactly 5 customers will arrive?
 Or given that on the average 6 customers arrive per minute to UVTmarket, what is the probability that less than 4 customers (exclusive) will arrive?
Answers to those questions may be given by just looking to histograms. First question asks the value you read from the 5^{th} green bar. That is ~0.36. The second one asks for the sum of 0^{th}, 1st, 2^{nd} and 3^{rd} purple bars (cumulative distribution, F (3) = P(X<4)). That is ~0.15. Isn’t it amazing that by just knowing that your customers’ arrival satisfies those three conditions, you can estimate anything about their arrival?
Let’s now focus on some other issue. If we can compute the number of customers (sessions) we will have in the next minute, is it possible to compute the time between two consecutive customers (sessions)? Exactly! Once more I won’t deal with mathematical proofs, although I like them very much, but I will put the outcome in one sentence:
“The time between two consecutive Poisson arrivals with parameter lambda follows exponential distribution with parameter 1/lambda”
Figure 2
Figure 2 illustrates the interarrival rates of two systems with different interarrival rates (0.5 sess/min and 1 sess/min). For the smaller arrival rate half of the customers arrive before 1.5 minutes whereas this duration falls to less than 1 minute for the higher arrival rate. You may see this from cumulative distribution drawings in Figure 2(red and purple lines).
Oracle in Action Again
After this much theory let’s load Oracle once more. This time we will use Java for loading Oracle. That’s not because Oracle is not eligible to do it himself but rather people like external tools. What we do is pretty simple.
We create stationary Poisson Oracle sessions at five different arrival rates as follows:
Scenario 
Arrival rate 
Scenario Description 
NoLoad 
15 sessions/minute 
Java creates 100 Poisson arrivals at given rate, each session (Java thread) inserts 5000 rows into table and measures the service time. 
LowLoad 
30 sessions/minute 

MediumLoad 
1 session/second 

HeavyLoad 
2 sessions/second 

ExtremeLoad 
10 sessions/second 
Table 1
Graphs of interarrival rates are like more or less Figure 2. Another, more important, fact is the service time of Oracle. In following figures you will see the behavior of our development system under load.
NoLoad
Figure 3 Beta Model for NoLoad response time
As you may see in Figure 3, response time for NoLoad scenario is almost deterministic. Almost 90% of all insertion requests answered in less 160 than milliseconds. That’s highly due to arrival of sessions are so rare that no session need to wait for others to complete their task. If we include outliers into account, best response time can be modeled by using a Beta distribution (37 + 632*Beta (0.128, 1.2)).
LowLoad
Figure 4 LogNormal Model for LowLoad response time
As we increase session rate to 30 sessions per minute. Oracle response time changes characteristics and response time durations fit to a lognormal model which is a common distribution in modeling duration to complete some task. Notice that tails of the distribution are fatter than the previous model. This illustrates that some sessions wait for disk I/O, latch or lock held by other sessions. Stochastic model may be formalized as 39 + Logn(199, 1100).
MediumLoad & HeavyLoad
Figure 5 Exponential Model for MediumLoad response time 
Figure 6 Exponential Model for HeavyLoad response time 
MediumLoad and HeavyLoad scenarios perfectly represent the most simple queue systems. Meaning that interarrival and response times are both exponential and there is only one server to serve. We can model Oracle response time for those session loads as 44+EXPO (388) and 43 + EXPO (2290) respectively. Notice that those exponentials are somewhat different from those we use for interarrival times. In fact they are not. There is only a shift parameter 4344. It statistically only changes the expected value of response time, not the characteristics of the model.
ExtremeLoad
Figure 7 Normal Model for ExtremeLoad response time
Final scenario’s purpose is somewhat different from the previous ones. Rather than modeling the real world, we use this scenario to show a general occurrence in queuing systems. Notice that majority of customer response time shift from left to middle of the histogram. That’s waiting time durations increase. Characteristics of the response time model changes from exponential to a centermoded model. Best model seems to be Normal distribution Norm (3380, 1960). One problem is that this model allows negative response times also .But this can be solved by absolute value function trick by disturbing the real Normal distribution model a little bit.
Conclusion
In this part we model Oracle response time for different arrival rates. Starting from this part of Loading Oracle post we start to make a little bit of statistics. This will go like this. Hopefully this much mathematics is not neither too much nor too informal. If it is so I am looking for your feedbacks.
Source codes
Below link includes the source codes I have used in this part. Be careful about running java code. A random value to arrival rate may cause an Oracle disaster for large values
http://husnu.sensoy.googlepages.com/LoadingOraclePart3.rar
Special Thanks
For the outputs of Arena Input Analyzer, I appreciate my esteemed friend Yasin Alan who is currently a PhD student in Cornell University Department of Operations Research and Information Engineering, New York.
Loading Oracle Part 4: R= S+W and Active Session History(ASH)
Posted on March 26, 2007, in Oracle. Bookmark the permalink. 2 Comments.
Pingback: H.Tonguç YILMAZ Blog » PL/SQL based Oracle Database Loader(PSODL) Release 2.0 on sourceforge.net
Pingback: PL/SQL based Oracle Database Loader(PSODL) Release 2.0 on sourceforge.net « H.Tonguç YILMAZ Oracle Blog