# Universal Scalability Law in Oracle

In August 2011 Dave Abercrombie wrote an article about the Universal
Scalability Law (USL) in an Oracle OLTP
database.
He used *Mathematica* for his calculations and wanted to provide a solution
based on R later. Currently I am keen to learn more about the Universal
Scalability Law and therefore I decided to transform his calculations into R
code myself.

You might have heard or read about Amdahl’s Law. It is about the maximum improvement you can expect from a system when you parallelize parts of it. Every system eventually is limited by the parts that cannot be parallelized and this will cause performance to level off. This is known as the point of diminishing returns: adding more processing power to the system yields a much smaller improvement than expected.

Dr. Neil Gunther developed the Universal Scalability Law as a generalization of Amdahl’s Law by including not only concurrency but also coherency in the scalability model. This model predicts that performance will not only level off but actually decrease when parallelization is increased unreasonably. It seems that this model approximates real systems better than Amdahl’s Law does.

The following calculations will follow the steps performed by Dave
Abercrombie in his *Mathematica* notebook. I also used his headlines to make
a clear connection to the original calculations.

## USL model detailed description

As in the original article I will try to estimate the parameters
`α`

and `β`

of the Universal Scalability
Law:

\( capacityRatio = \frac{n}{1 + \alpha * (n-1) + \beta * n * (n-1)} \)

The capacityRatio is the normalized throughput per concurrent user. We take
the Oracle metric *buffer gets* to model throughput and *Average Active
Sessions* (AAS) to model concurrent users. The article Concurrency of a
connection pooled OLTP database:
N=AAS
shows that AAS is a reasonable metric to model the number of concurrent
users.

## Observations from a production database

Dave Abercrombie has provided a CSV file with AWR data from a production database. First we need to read the data into R.

`> awrObservations <- read.csv(file="db9-awr.csv")`

Then we create a new data frame `nThroughPut`

containing only the
relevant columns AAS (derived from DB_TIME_CSPH) and meanThroughPut (derived
from BUFFER_GETS_PH). We perform the same sanity check as in the original
article and print the first ten rows.

```
> nThroughPut <- with(
awrObservations,
data.frame(AAS=DB_TIME_CSPH / (3600*100),
meanThroughPut=BUFFER_GETS_PH / (3600*1000)
))
> head(nThroughPut, n=10)
AAS meanThroughPut
1 1.987661 165.2103
2 1.948708 152.5213
3 2.093981 159.3359
4 2.296250 167.6869
5 2.373292 165.1114
6 2.196808 163.3581
7 2.224908 157.0620
8 1.790683 142.2059
9 1.381636 121.5163
10 1.218381 112.1319
```

The formatting in R is a bit different compared to *Mathematica* but the data
is the same.

## Estimate throughput at N=1

To get the normalized capacityRatio we need to find the throughput for N=1. Therefore we create a subset of the data with AAS less than 1.5 to be used in linear regression.

`> nThroughPutSubset <- subset(nThroughPut, AAS < 1.5)`

Next we create a scatterplot of the data for a quick sanity check.

`> plot(nThroughPutSubset, pch=20)`

Now we can use the `lm()`

function to create the linear model and
estimate the dependency between AAS and meanThroughPut. The
`summary()`

function tells us more about the fitted model.

```
> nThroughPutSubsetLm <- lm(meanThroughPut ~ AAS,
data = nThroughPutSubset)
> summary(nThroughPutSubsetLm)
Call:
lm(formula = meanThroughPut ~ AAS, data = nThroughPutSubset)
Residuals:
Min 1Q Median 3Q Max
-6.0757 -2.7737 -0.7096 1.7995 9.3835
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 34.602 7.953 4.351 0.000186 ***
AAS 67.018 6.107 10.974 2.96e-11 ***
---
Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1
Residual standard error: 3.944 on 26 degrees of freedom
Multiple R-squared: 0.8224, Adjusted R-squared: 0.8156
F-statistic: 120.4 on 1 and 26 DF, p-value: 2.957e-11
```

The model calculated the coefficents 34.602 and 67.018 which matches the
result in the *Mathematica* notebook. We can also see from the p-value of the
model and the t values that we actually have found significant parameters for
the linear model.

Then the `predict()`

function is used to estimate the throughput
for AAS=1. R returns 101.6202 as the result and again this matches the result
calculated with *Mathematica*.

```
> throughPutOne <- predict(
nThroughPutSubsetLm,
newdata = data.frame(AAS=c(1)))[[1]]
> throughPutOne
[1] 101.6202
```

A plot of the calculated liner model can be added to the existing scatterplot and we get the same graph as in the original article.

`> abline(nThroughPutSubsetLm)`

## Normalize throughput observations to convert to capacityRatio

In order to get the normalized capacity ratio we create a new data frame containing the original observations divided by 101.6202 (the estimated value for AAS=1). A printout of the first ten observations and a scatterplot of the data frame shows we are still following the original calculations.

```
> nCapacityRatio <- with(
nThroughPut,
data.frame(AAS = AAS,
nThroughPut = meanThroughPut / throughPutOne
))
> head(nCapacityRatio, n=10)
AAS nThroughPut
1 1.987661 1.625763
2 1.948708 1.500896
3 2.093981 1.567955
4 2.296250 1.650134
5 2.373292 1.624789
6 2.196808 1.607536
7 2.224908 1.545579
8 1.790683 1.399386
9 1.381636 1.195789
10 1.218381 1.103441
> plot(nCapacityRatio, pch=20,
xlim=c(0,5.6), ylim=c(0,2.5))
```

## Fit USL model to observations to estimate parameters `α`

and `β`

Now I define the function `usl()`

in R to make the following code
easier to read.

```
> usl <- function(n, alpha, beta) {
n / (1 + (alpha * (n - 1)) + (beta * n * (n - 1)))
}
```

The R function `nls()`

is used to solve the non-linear function
`usl()`

based on the values in the data frame
`nCapacityRatio`

. Again the `summary()`

function is
used to show details about the result.

```
> bestFitUSL <- nls(nThroughPut ~ usl(AAS, alpha, beta),
data = nCapacityRatio,
start = c(alpha=0.1, beta=0.01))
> summary(bestFitUSL)
Formula: nThroughPut ~ usl(AAS, alpha, beta)
Parameters:
Estimate Std. Error t value Pr(>|t|)
alpha 0.189005 0.017125 11.037 < 2e-16 ***
beta 0.033517 0.005261 6.371 1.64e-09 ***
---
Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1
Residual standard error: 0.1102 on 173 degrees of freedom
Number of iterations to convergence: 5
Achieved convergence tolerance: 5.256e-07
```

Again the t values show a high significance of the result. We can use
`coef()`

if we are only interested in the coefficients.

```
> coef(bestFitUSL)
alpha beta
0.18900524 0.03351659
```

We come to the same result as in the *Mathematica* calculations. Plotting the
curve using the estimated values for `α`

and
`β`

shows the same graph.

```
> curve(usl(x,
coef(bestFitUSL)[["alpha"]],
coef(bestFitUSL)[["beta"]]),
from=0, to=8, col="lightblue", add=TRUE)
```

Using the formula given by Dr. Neil Gunther we can also calculate the point where the system reaches its maximum scalability.

```
> sqrt((1 - coef(bestFitUSL)[["alpha"]]) /
coef(bestFitUSL)[["beta"]])
[1] 4.919026
```

So the measured data seams to belong to a system that reaches peak scalability with about five concurrent users. Having more than five Average Active Sessions (=concurrent users) seemingly leads to a degradation of throughput where throughput is defined as the number of Oracle buffer gets per hour.

In this article I have illustrated how R can be used to apply the Universal Scalability Law (USL) to Oracle performance metrics. Credits for the math behind this go to Dr. Neil Gunther and Dave Abercrombie; errors in the R implementation are all my fault.