An article in the Journal of Database Management Experimenta

An article in the Journal of Database Management [\"Experimental Study of a Self-Tuning Algorithm for DBMS Buffer Pools\" (2005, Vol. 16, pp. 1–20)] provided the workload used in the TPC-C OLTP (Transaction Processing Performance Council\'s Version C On-Line Transaction Processing) benchmark, which simulates a typical order entry application. The frequency of each type of transaction (in the second column) can be used as the percentage of each type of transaction. The average number of selects operations required for each type transaction is shown.

Transaction

Frequency

Selects

Updates

Inserts

Deletes

Non-Unique Selects

Joins

New order

41

23

11

12

0

0

0

Payment

46

4.2

3

1

0

0.6

0

Order status

4

11.4

0

0

0

0.6

0

Delivery

5

130

120

0

10

0

0

Stock level

4

0

0

0

0

0

1

a) Determine the mean and standard deviation of the number of selects operations for a transaction from the distribution of types shown in the above table.

b) Determine the mean and standard deviation of the total number of operations (selects, updates, ... , and joins) for a transaction from the distribution of types shown in the table below.

Transaction	Frequency	Selects	Updates	Inserts	Deletes	Non-Unique Selects	Joins
New order	41	23	11	12	0	0	0
Payment	46	4.2	3	1	0	0.6	0
Order status	4	11.4	0	0	0	0.6	0
Delivery	5	130	120	0	10	0	0
Stock level	4	0	0	0	0	0	1

Solution

a)

Mean of number of selects operations = sum (selects*% of transcations ) =18.318

variance = 1075.203-(18.318)^2 =739.6537

Standard deviation =sqrt(variance) =sqrt(739.6357) = 27.2

b)

Mean number of total operations =sum(total operations*%of transactions) =36.428

variance =4288.9-(36.428)^2 = 2961.98

standard deviation =sqrt(variance) = 54.42

Transaction	Frequency	Selects	Updates	Inserts	Deletes	Non-Unique Selects	Joins	percentage of transcation	selects*%of tansaction	selects^2*%of tansaction
New order	41	23	11	12	0	0	0	0.41	9.43	216.89
Payment	46	4.2	3	1	0	0.6	0	0.46	1.932	8.1144
Order status	4	11.4	0	0	0	0.6	0	0.04	0.456	5.1984
Delivery	5	130	120	0	10	0	0	0.05	6.5	845
Stock level	4	0	0	0	0	0	1	0.04	0	0
									18.318	1075.203