Since a long time I am busy using queuing formula’s to be able to calculate cpu queue’s and I/O queues. One of the big problems I was facing that the formula’s I like to use on big data sets with my GAPP analysis were only available in perl. For a long time I was using proximity functions to avoid the perl programmed Erlang-C formula and some other. Last weekend I just had the time to start programming the formula’s in PLSQL, just to have them easily accessible in my database. After finish programming the package I realized that the package can also be very handy for other people, so I decided to create this blog. The created package has the following important functions: Erlang-C, Erlang-B, Response Time in multi-server environments (ErlangR in the package), Queue length in multi-server environments (ErlangQ in the package) and Response Time in multi-queue environments like IO (paratqr in the package).

The formula’s are described in the book “Analyzing Computer System Performance with Perl::PDQ” from Dr. Neil J. Gunther 2005. In the source of the package are the exact locations in the documentation documented.

The code of the package looks like (erlangghh_pkg_v1_0):

CREATE OR REPLACE PACKAGE GHH_PKG AS
/* ==### Package GHH_PKG 12-10-2010 by G. Hendriksen v1.0 ###==
**
** Formula’s from book:
** “Analyzing Computer System Performance with Perl::PDQ”
** by Dr. Neil J. Gunther 2005
**
** Initial creation 10-10-2010 G.Hendriksen v0.1
** Updated formula 11-10-2010 G.Hendriksen v0.2
** Updated formula 12-10-2010 G.Hendriksen v1.0
*/

/* Function erlangb is used to get the Erlang-B (multiserver queueing system)
** probability that all the servers are busy and calls are dropped completely
** rather than being queued up. The function has input values number of servers
** (m) and utilization (p) — See pag. 83 formula 2.67
*/
function erlangb (m number, p number) return number;

/* Function erlangc is used to get the Erlang-C, this is the probability that
** all the servers are busy and therefore calls arriving causing utilization p
** will have to wait for service. The function has input values number of
** servers (m) and utilization (p) — See pag. 81 formula 2.66
*/
function erlangc (m number, p number) return number;

/* Function erlangr is used to calculate the exact multiserver residence time.
** The function has input values number of servers (m), utilization (p) and
** service time (s) — See pag. 81 formula 2.64
*/
function erlangr (m number, p number, s number) return number;

/* Function erlangq is used to calculate the queue length in a multiserver
** environment. The function has input values number of servers (m) and
** utilization (p) — See pag. 81 formula 2.65
*/
function erlangq (m number, p number) return number;

/* Function PDF is used to calculate the Discrete Probability Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function PDF (m number, p number) return number;

/* Function CDF is used to calculate the Cumulative Distriburion Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function CDF (m number, p number) return number;

/* Function PoisonR is used to calculate the Poison Ratio. The function has
** input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.69
*/
function PoisonR (m number, p number) return number;

/* Function Utilp is used to calculate the utilization (p). The function has
** input values calls per time lambda (l), service time (s) and number of queues
** (q) — See pag. 74 formula 2.45
*/
function Utilp (l number, s number, q number) return number;

/* Function Paralqr is used to calculate the response time (r) for parallel
** queues, with utilization per queue. The function has input values
** utilization (p) and service time (s) — See pag. 75 formula 2.46
*/
function Paralqr (p number, s number) return number;

/* Function Paratqr is used to calculate the response time (r) for parallel
** queues, with utilization over all queues. The function has input values
** number of queues (q), utilization (p) and service time (s) — See pag. 75
** formula 2.46
*/
function Paratqr (q number, p number, s number) return number;

/* Function fact is used to get the factorial value with given number
*/
function fact (v number ) return number;

END GHH_PKG;
/

CREATE OR REPLACE PACKAGE BODY GHH_PKG AS
/* ==### Package GHH_PKG 12-10-2010 by G. Hendriksen v1.0 ###==
**
** Formula’s from book:
** “Analyzing Computer System Performance with Perl::PDQ”
** by Dr. Neil J. Gunther 2005
**
** Initial creation 10-10-2010 G.Hendriksen v0.1
** Updated formula 11-10-2010 G.Hendriksen v0.2
** Updated formula 12-10-2010 G.Hendriksen v1.0
*/

/* Function erlangb is used to get the Erlang-B (multiserver queueing system)
** probability that all the servers are busy and calls are dropped completely
** rather than being queued up. The function has input values number of servers
** (m) and utilization (p) — See pag. 83 formula 2.67
*/
function erlangb (m number, p number) return number AS
b number;
– Sigma inside formula is calculated here:
function sigm (v_m number, v_p number) return number AS
s number;
begin
s := 0;
for n in 0..(v_m)
loop
s := s + ( power((v_m*v_p),n)/fact(n) );
end loop;
return s;
end sigm;
BEGIN
b:=( power((m*p),m)/fact(m) ) / ( sigm(m,p) );
RETURN b;
END erlangb;

/* Function erlangc is used to get the Erlang-C, this is the probability that
** all the servers are busy and therefore calls arriving causing utilization p
** will have to wait for service. The function has input values number of
** servers (m) and utilization (p) — See pag. 81 formula 2.66
*/
function erlangc (m number, p number) return number AS
c number;
function sigm (v_m number, v_p number) return number AS
s number;
begin
s := 0;
for n in 0..(v_m-1)
loop
s := s + ( power((v_m*v_p),n)/fact(n) );
end loop;
return s;
end sigm;
BEGIN
c:=( power((m*p),m)/fact(m) ) /( ( (1-p)* sigm(m,p)) + ( (power((m*p),m)/fact(m)) ) );
RETURN c;
END erlangc;

/* Function erlangr is used to calculate the exact multiserver residence time.
** The function has input values number of servers (m), utilization (p) and
** service time (s) — See pag. 81 formula 2.64
*/
function erlangr (m number, p number, s number) return number AS
r number;
BEGIN
r := ( ( erlangc(m,p)*s ) / ( m*(1-p) ) ) + s;
RETURN r;
END erlangr;

/* Function erlangq is used to calculate the queue length in a multiserver
** environment. The function has input values number of servers (m) and
** utilization (p) — See pag. 81 formula 2.65
*/
function erlangq (m number, p number) return number AS
q number;
BEGIN
q := ( ( p*erlangc(m,p) ) / ( m*(1-p) ) ) + (m*p);
RETURN q;
END erlangq;

/* Function PDF is used to calculate the Discrete Probability Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function PDF (m number, p number) return number AS
pdf number;
BEGIN
pdf := ( exp(-m*p)* ( power((m*p),m) / fact(m) ) );
RETURN pdf;
END pdf;

/* Function CDF is used to calculate the Cumulative Distriburion Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function CDF (m number, p number) return number AS
cdf number;
function sigm (v_m number, v_p number) return number AS
s number;
begin
s := 0;
for n in 0..(v_m)
loop
s := s + ( power((v_m*v_p),n)/fact(n) );
end loop;
return s;
end sigm;
BEGIN
cdf := ( exp(-m*p)*(sigm(m,p)) );
RETURN cdf;
END CDF;

/* Function PoisonR is used to calculate the Poison Ratio. The function has
** input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.69
*/
function PoisonR (m number, p number) return number AS
r number;
BEGIN
r := ( 1 – (pdf(m,p)/cdf(m,p)) );
RETURN r;
END PoisonR;

/* Function Utilp is used to calculate the utilization (p). The function has
** input values calls per time lambda (l), service time (s) and number of queues
** (q) — See pag. 74 formula 2.45
*/
function Utilp (l number, s number, q number) return number AS
p number;
BEGIN
p := ( (l*s)/q );
RETURN p;
END Utilp;

/* Function Paralqr is used to calculate the response time (r) for parallel
** queues, with utilization per queue. The function has input values
** utilization (p) and service time (s) — See pag. 75 formula 2.46
*/
function Paralqr (p number, s number) return number AS
r number;
BEGIN
r := ( s / (1-p) );
RETURN r;
END Paralqr;

/* Function Paratqr is used to calculate the response time (r) for parallel
** queues, with utilization over all queues. The function has input values
** number of queues (q), utilization (p) and service time (s) — See pag. 75
** formula 2.46
*/
function Paratqr (q number, p number, s number) return number AS
r number;
BEGIN
r := ( s / (1-(p/q)) );
RETURN r;
END Paratqr;

/* Function fact is used to get the factorial value with given number
*/
function fact (v number ) return number AS
min1 number;
prod number;
begin
min1 := v – 1;
if ( min1 > 0 )
then
prod := v*fact(min1);
return prod;
end if;
– important correction factorial value of zero is one.
if v=0 then return 1;
else return v;
end if;
END fact;

END GHH_PKG;
/

All the formula’s have been checked and the output is provide in this excel file (erlangpacktest) . When checking the output of the functions, the response time in multiple servers situation will look like the picture:

I hope I made some people happy by programming the functions in PLSQL. If something is wrong with them, please comment on the blog or of course for any suggestion. I will probably enhance the package in future, if so I will keep you posted.

Regards, Gerwin