Posted by Sometimes the things we studied in college are actually useful. As much as I hated my statistics professor, I still learned a lot that I frequently use. Maybe I don’t do many multiple regression problems these days, but I use standard deviation a lot. It is a concept that every DBA or really any IT professional who must analyze performance should be familar with.
What is Standard Deviation?
Standard deviation is a measure of how much the values in a set of values vary. It helps us find values that are truly out of the norm, and not just the ones that catch our eyes while scrolling through a set of data. 95% of values in a data set will fall within two standard deviations of the average of that result set.
Basically, it helps us find numbers that are unusually high or low.
How Can Standard Deviation Help Us When Analyzing Database Performance?
I use standard deviation in combination with data collection over time to identify time periods that are outside of the ordinary, and that therefore deserve further investigation.
I’ll use an example here of Read Efficiency. If you’re a regular reader of this blog, you probably know of my love of Read Efficiency as a metric of how well indexed a database is for the queries running against it. I have a table, that among other things, records the rows read and rows returned (from mon_get_workload) every two hours – as a delta from the time period before, so the data only covers that time frame. Since I have that hourly data, I can use this query to get the data and to flag values that are outside the norm:
with re_stats as (
select decimal(avg(rows_read/rows_returned),10,2) as avg_re,
decimal(stddev(rows_read/rows_returned),10,2) as stddev_re,
decimal(avg(rows_read/rows_returned) - 2 * stddev(rows_read/rows_returned),10,2) as avg_less_2_stddev,
decimal(avg(rows_read/rows_returned) + 2 * stddev(rows_read/rows_returned),10,2) as avg_plus_2_stddev
from dbamon097.workload_hist
where workload_name='SYSDEFAULTUSERWORKLOAD'
)
select
snapshot_timestamp,
rows_read/rows_returned as read_eff,
re_stats.avg_re,
re_stats.stddev_re,
re_stats.avg_less_2_stddev,
re_stats.avg_plus_2_stddev,
case
when rows_read/rows_returned > re_stats.avg_plus_2_stddev then 'High'
when rows_read/rows_returned < re_stats.avg_less_2_stddev then 'Low'
else NULL
end as flag
from dbamon097.workload_hist,
re_stats
where workload_name='SYSDEFAULTUSERWORKLOAD'
order by snapshot_timestamp
with ur;
Now that might look a bit complicated. It starts with calculating some values over the set as a whole in a common table expression (the "with" section). It then goes on to query the read efficency for each two-hour period, and along with it the values from the common table expression for comparision's sake. They will be the same values for every row, and are presented in-line for the purposes of easy comparison. Finally, I use a case expression to flag any values that are high or low. I probably don't care to investigate the low periods, but the periods where read efficiency was high probably warrant me digging into my historical package cache data for those periods to find out what statements were problematic on Read Efficiency. Analyzing SQL based on Read Efficiency is better described in my past entry: DB2 Table Scans.
The results on the system I happened to be working with look like this:
SNAPSHOT_TIMESTAMP READ_EFF AVG_RE STDDEV_RE AVG_LESS_2_STDDEV AVG_PLUS_2_STDDEV FLAG -------------------------- -------------------- ------------ ------------ ----------------- ----------------- ---- 2013-11-29-02.59.21.678696 65 28.00 5.61 16.76 39.23 High 2013-11-29-04.59.21.561770 66 28.00 5.61 16.76 39.23 High 2013-11-29-06.59.22.446232 49 28.00 5.61 16.76 39.23 High 2013-11-29-08.59.21.000966 49 28.00 5.61 16.76 39.23 High 2013-11-29-10.59.20.750390 43 28.00 5.61 16.76 39.23 High 2013-11-29-12.59.21.405569 36 28.00 5.61 16.76 39.23 - 2013-11-29-14.59.20.893242 31 28.00 5.61 16.76 39.23 - 2013-11-30-02.59.22.827527 41 28.00 5.61 16.76 39.23 High 2013-11-30-04.59.22.391299 30 28.00 5.61 16.76 39.23 - 2013-11-30-06.59.20.800831 34 28.00 5.61 16.76 39.23 - 2013-11-30-08.59.21.861793 31 28.00 5.61 16.76 39.23 - 2013-11-30-10.59.21.530976 30 28.00 5.61 16.76 39.23 - 2013-11-30-12.59.21.529267 29 28.00 5.61 16.76 39.23 - 2013-11-30-14.59.22.085319 29 28.00 5.61 16.76 39.23 - 2013-12-01-02.59.21.676745 34 28.00 5.61 16.76 39.23 - 2013-12-01-04.59.21.405445 33 28.00 5.61 16.76 39.23 - 2013-12-01-06.59.21.932471 34 28.00 5.61 16.76 39.23 - 2013-12-01-08.59.21.543394 32 28.00 5.61 16.76 39.23 - 2013-12-01-10.59.22.596291 29 28.00 5.61 16.76 39.23 - 2013-12-01-12.59.21.486869 31 28.00 5.61 16.76 39.23 - 2013-12-01-14.59.21.670673 28 28.00 5.61 16.76 39.23 - 2013-12-02-02.59.22.128599 47 28.00 5.61 16.76 39.23 High 2013-12-02-04.59.21.271931 49 28.00 5.61 16.76 39.23 High 2013-12-02-06.59.20.718682 40 28.00 5.61 16.76 39.23 High 2013-12-02-08.59.20.812606 37 28.00 5.61 16.76 39.23 - 2013-12-02-10.59.22.788311 38 28.00 5.61 16.76 39.23 - 2013-12-02-12.59.22.455237 37 28.00 5.61 16.76 39.23 - 2013-12-02-14.59.22.002375 35 28.00 5.61 16.76 39.23 - 2013-12-03-02.59.21.538108 33 28.00 5.61 16.76 39.23 - 2013-12-03-04.59.20.730863 29 28.00 5.61 16.76 39.23 - 2013-12-03-06.59.22.163217 27 28.00 5.61 16.76 39.23 - 2013-12-03-08.59.21.714354 28 28.00 5.61 16.76 39.23 - 2013-12-03-10.59.21.461850 29 28.00 5.61 16.76 39.23 - 2013-12-03-12.59.22.083006 28 28.00 5.61 16.76 39.23 - 2013-12-03-14.59.22.867972 29 28.00 5.61 16.76 39.23 - 2013-12-04-02.59.22.061882 32 28.00 5.61 16.76 39.23 - 2013-12-04-04.59.20.627753 32 28.00 5.61 16.76 39.23 - 2013-12-04-06.59.22.843966 27 28.00 5.61 16.76 39.23 - 2013-12-04-08.59.20.596819 28 28.00 5.61 16.76 39.23 - 2013-12-04-10.59.20.767753 25 28.00 5.61 16.76 39.23 - 2013-12-04-12.59.21.496527 29 28.00 5.61 16.76 39.23 - 2013-12-04-14.59.22.482305 28 28.00 5.61 16.76 39.23 - 2013-12-05-02.59.22.340651 32 28.00 5.61 16.76 39.23 - 2013-12-05-04.59.21.745529 24 28.00 5.61 16.76 39.23 - 2013-12-05-06.59.20.901268 20 28.00 5.61 16.76 39.23 - 2013-12-05-08.59.20.677835 27 28.00 5.61 16.76 39.23 - 2013-12-05-10.59.22.461241 26 28.00 5.61 16.76 39.23 - 2013-12-05-12.59.22.487107 28 28.00 5.61 16.76 39.23 - 2013-12-05-14.59.21.526778 28 28.00 5.61 16.76 39.23 - 2013-12-06-02.59.21.447813 31 28.00 5.61 16.76 39.23 - 2013-12-06-04.59.22.112396 29 28.00 5.61 16.76 39.23 - 2013-12-06-06.59.22.583925 26 28.00 5.61 16.76 39.23 - 2013-12-06-08.59.20.140544 28 28.00 5.61 16.76 39.23 - 2013-12-06-10.59.21.617885 28 28.00 5.61 16.76 39.23 - 2013-12-06-12.59.22.074087 31 28.00 5.61 16.76 39.23 - 2013-12-06-14.59.21.735402 30 28.00 5.61 16.76 39.23 - 2013-12-07-02.59.23.448424 31 28.00 5.61 16.76 39.23 - 2013-12-07-04.59.20.642023 20 28.00 5.61 16.76 39.23 - 2013-12-07-06.59.20.075050 14 28.00 5.61 16.76 39.23 Low 2013-12-07-08.59.21.024284 19 28.00 5.61 16.76 39.23 - 2013-12-07-10.59.22.770149 29 28.00 5.61 16.76 39.23 - 2013-12-07-12.59.21.664707 27 28.00 5.61 16.76 39.23 - 2013-12-07-14.59.22.163834 28 28.00 5.61 16.76 39.23 - 2013-12-08-02.59.22.879937 32 28.00 5.61 16.76 39.23 - 2013-12-08-04.59.21.584441 30 28.00 5.61 16.76 39.23 - 2013-12-08-06.59.21.125031 31 28.00 5.61 16.76 39.23 - 2013-12-08-08.59.20.800698 30 28.00 5.61 16.76 39.23 - 2013-12-08-10.59.22.660327 28 28.00 5.61 16.76 39.23 - 2013-12-08-12.59.22.147704 28 28.00 5.61 16.76 39.23 - 2013-12-08-14.59.22.138516 28 28.00 5.61 16.76 39.23 - 2013-12-09-02.59.22.412934 33 28.00 5.61 16.76 39.23 - 2013-12-09-04.59.20.234675 30 28.00 5.61 16.76 39.23 - 2013-12-09-06.59.23.070911 29 28.00 5.61 16.76 39.23 - 2013-12-09-08.59.21.794690 29 28.00 5.61 16.76 39.23 - 2013-12-09-10.59.22.375989 29 28.00 5.61 16.76 39.23 - 2013-12-09-12.59.21.018070 28 28.00 5.61 16.76 39.23 - 2013-12-09-14.59.21.358387 31 28.00 5.61 16.76 39.23 - 2013-12-10-02.59.22.376217 28 28.00 5.61 16.76 39.23 - 2013-12-10-04.59.20.716925 29 28.00 5.61 16.76 39.23 - 2013-12-10-06.59.22.082195 26 28.00 5.61 16.76 39.23 - 2013-12-10-08.59.21.151302 29 28.00 5.61 16.76 39.23 - 2013-12-10-10.59.22.184228 28 28.00 5.61 16.76 39.23 - 2013-12-10-12.59.22.287777 28 28.00 5.61 16.76 39.23 - 2013-12-10-14.59.21.545571 27 28.00 5.61 16.76 39.23 - 2013-12-11-02.59.22.567732 32 28.00 5.61 16.76 39.23 - 2013-12-11-06.59.21.891666 27 28.00 5.61 16.76 39.23 - 2013-12-11-08.59.21.297427 28 28.00 5.61 16.76 39.23 - 2013-12-11-10.59.22.461041 29 28.00 5.61 16.76 39.23 - 2013-12-11-12.59.21.813065 28 28.00 5.61 16.76 39.23 - 2013-12-11-14.59.21.758230 29 28.00 5.61 16.76 39.23 - 2013-12-13-02.59.21.440843 36 28.00 5.61 16.76 39.23 - 2013-12-13-04.59.20.311046 29 28.00 5.61 16.76 39.23 - 2013-12-13-06.59.22.813007 28 28.00 5.61 16.76 39.23 - 2013-12-13-08.59.21.481225 29 28.00 5.61 16.76 39.23 - 2013-12-13-10.59.22.461797 29 28.00 5.61 16.76 39.23 - 2013-12-13-12.59.21.922756 28 28.00 5.61 16.76 39.23 - 2013-12-13-14.59.21.513619 29 28.00 5.61 16.76 39.23 - 2013-12-14-02.59.22.652471 32 28.00 5.61 16.76 39.23 - 2013-12-14-04.59.20.924964 30 28.00 5.61 16.76 39.23 - 2013-12-14-06.59.20.508336 30 28.00 5.61 16.76 39.23 - 2013-12-14-08.59.20.409441 30 28.00 5.61 16.76 39.23 - 2013-12-14-10.59.20.825618 29 28.00 5.61 16.76 39.23 - 2013-12-14-12.59.20.847557 26 28.00 5.61 16.76 39.23 - 2013-12-14-14.59.22.011896 28 28.00 5.61 16.76 39.23 - 2013-12-15-02.59.21.607640 32 28.00 5.61 16.76 39.23 - 2013-12-15-04.59.19.912905 30 28.00 5.61 16.76 39.23 - 2013-12-15-06.59.22.408963 30 28.00 5.61 16.76 39.23 - 2013-12-15-08.59.21.064311 30 28.00 5.61 16.76 39.23 - 2013-12-15-10.59.21.989701 29 28.00 5.61 16.76 39.23 - 2013-12-15-12.59.21.479766 30 28.00 5.61 16.76 39.23 - 2013-12-15-14.59.20.980472 31 28.00 5.61 16.76 39.23 - 2013-12-16-02.59.21.951763 33 28.00 5.61 16.76 39.23 - 2013-12-16-04.59.21.126427 32 28.00 5.61 16.76 39.23 - 2013-12-16-06.59.22.575613 28 28.00 5.61 16.76 39.23 - 2013-12-16-08.59.21.139765 30 28.00 5.61 16.76 39.23 - 2013-12-16-10.59.22.650366 28 28.00 5.61 16.76 39.23 - 2013-12-16-12.59.22.573870 26 28.00 5.61 16.76 39.23 - 2013-12-16-14.59.22.084615 27 28.00 5.61 16.76 39.23 - 2013-12-17-04.59.20.372266 33 28.00 5.61 16.76 39.23 - 2013-12-17-06.59.20.526610 28 28.00 5.61 16.76 39.23 - 2013-12-17-08.59.21.402062 29 28.00 5.61 16.76 39.23 - 2013-12-17-10.59.22.632902 27 28.00 5.61 16.76 39.23 - 2013-12-17-12.59.22.476110 28 28.00 5.61 16.76 39.23 - 2013-12-17-14.59.21.782765 33 28.00 5.61 16.76 39.23 - 2013-12-18-04.59.20.818424 35 28.00 5.61 16.76 39.23 - 2013-12-18-06.59.20.516633 28 28.00 5.61 16.76 39.23 - 2013-12-18-08.59.21.094285 35 28.00 5.61 16.76 39.23 - 2013-12-18-12.59.22.756689 57 28.00 5.61 16.76 39.23 High 2013-12-18-14.59.21.886200 56 28.00 5.61 16.76 39.23 High 2013-12-19-02.59.21.892533 39 28.00 5.61 16.76 39.23 - 2013-12-19-04.59.20.972852 21 28.00 5.61 16.76 39.23 - 2013-12-19-06.59.21.680146 21 28.00 5.61 16.76 39.23 - 2013-12-19-10.59.21.727190 31 28.00 5.61 16.76 39.23 - 2013-12-19-12.59.21.626805 29 28.00 5.61 16.76 39.23 - 2013-12-19-14.59.21.455598 31 28.00 5.61 16.76 39.23 - 2013-12-19-15.32.15.768458 36 28.00 5.61 16.76 39.23 - 2013-12-19-22.06.37.826864 41 28.00 5.61 16.76 39.23 High 2013-12-19-22.31.10.016610 36 28.00 5.61 16.76 39.23 - 2013-12-20-02.59.22.972668 18 28.00 5.61 16.76 39.23 - 2013-12-20-04.59.20.768224 14 28.00 5.61 16.76 39.23 Low 2013-12-20-06.59.22.693798 14 28.00 5.61 16.76 39.23 Low 2013-12-20-08.59.22.091257 22 28.00 5.61 16.76 39.23 - 2013-12-20-10.59.24.557123 30 28.00 5.61 16.76 39.23 - 2013-12-20-12.59.21.810379 31 28.00 5.61 16.76 39.23 - 2013-12-20-14.59.21.680007 35 28.00 5.61 16.76 39.23 - 2013-12-21-02.59.24.381142 39 28.00 5.61 16.76 39.23 - 2013-12-21-04.59.21.099288 31 28.00 5.61 16.76 39.23 - 2013-12-21-06.59.20.942414 32 28.00 5.61 16.76 39.23 - 2013-12-21-08.59.21.074281 36 28.00 5.61 16.76 39.23 - 2013-12-21-10.59.22.893373 33 28.00 5.61 16.76 39.23 - 2013-12-21-12.59.22.712093 31 28.00 5.61 16.76 39.23 - 2013-12-21-14.59.21.039331 32 28.00 5.61 16.76 39.23 - 2013-12-22-02.59.22.705713 34 28.00 5.61 16.76 39.23 - 2013-12-22-04.59.20.850466 29 28.00 5.61 16.76 39.23 - 2013-12-22-06.59.20.740133 31 28.00 5.61 16.76 39.23 - 2013-12-22-08.59.20.306049 32 28.00 5.61 16.76 39.23 - 2013-12-22-12.59.21.688949 33 28.00 5.61 16.76 39.23 - 2013-12-22-14.59.22.241338 32 28.00 5.61 16.76 39.23 - 2013-12-23-02.59.22.270223 33 28.00 5.61 16.76 39.23 - 2013-12-23-04.59.21.363317 30 28.00 5.61 16.76 39.23 - 2013-12-23-06.59.21.957604 29 28.00 5.61 16.76 39.23 - 2013-12-23-08.59.21.200456 32 28.00 5.61 16.76 39.23 - 2013-12-23-10.59.22.218280 34 28.00 5.61 16.76 39.23 - 2013-12-23-12.59.21.134243 30 28.00 5.61 16.76 39.23 - 2013-12-23-14.59.22.506637 28 28.00 5.61 16.76 39.23 - 2013-12-24-02.59.21.987251 27 28.00 5.61 16.76 39.23 - 2013-12-24-04.59.20.502894 28 28.00 5.61 16.76 39.23 - 2013-12-24-06.59.21.577223 26 28.00 5.61 16.76 39.23 - 2013-12-24-08.59.21.372526 27 28.00 5.61 16.76 39.23 - 2013-12-24-10.59.21.635678 29 28.00 5.61 16.76 39.23 - 2013-12-24-12.59.20.650443 28 28.00 5.61 16.76 39.23 - 2013-12-24-14.59.22.069508 28 28.00 5.61 16.76 39.23 - 2013-12-25-02.59.22.844608 36 28.00 5.61 16.76 39.23 - 2013-12-25-04.59.20.651782 30 28.00 5.61 16.76 39.23 - 2013-12-25-06.59.21.952356 27 28.00 5.61 16.76 39.23 - 2013-12-25-08.59.20.743815 30 28.00 5.61 16.76 39.23 - 2013-12-25-10.59.21.544750 29 28.00 5.61 16.76 39.23 - 2013-12-25-12.59.20.196126 27 28.00 5.61 16.76 39.23 - 2013-12-25-14.59.22.700092 28 28.00 5.61 16.76 39.23 - 2013-12-26-02.59.22.231738 30 28.00 5.61 16.76 39.23 - 2013-12-26-04.59.20.555040 22 28.00 5.61 16.76 39.23 - 2013-12-26-06.59.22.202527 23 28.00 5.61 16.76 39.23 - 2013-12-26-08.59.21.186378 28 28.00 5.61 16.76 39.23 - 2013-12-26-10.59.23.098623 29 28.00 5.61 16.76 39.23 - 2013-12-26-12.59.21.715525 27 28.00 5.61 16.76 39.23 - 2013-12-26-14.59.22.107563 27 28.00 5.61 16.76 39.23 - 2013-12-27-02.59.20.409212 29 28.00 5.61 16.76 39.23 - 2013-12-27-04.59.20.934032 25 28.00 5.61 16.76 39.23 - 2013-12-27-06.59.20.950084 25 28.00 5.61 16.76 39.23 - 2013-12-27-08.59.21.128115 26 28.00 5.61 16.76 39.23 - 2013-12-27-10.59.23.313964 26 28.00 5.61 16.76 39.23 - 2013-12-27-12.59.22.225356 26 28.00 5.61 16.76 39.23 - 2013-12-27-14.59.20.993054 26 28.00 5.61 16.76 39.23 - 2013-12-28-02.59.24.702003 31 28.00 5.61 16.76 39.23 - 2013-12-28-04.59.20.032912 27 28.00 5.61 16.76 39.23 - 2013-12-28-06.59.22.447925 28 28.00 5.61 16.76 39.23 - 2013-12-28-08.59.21.862514 28 28.00 5.61 16.76 39.23 - 2013-12-28-10.59.21.536016 27 28.00 5.61 16.76 39.23 - 2013-12-28-12.59.21.246238 26 28.00 5.61 16.76 39.23 - 2013-12-28-14.59.22.050081 27 28.00 5.61 16.76 39.23 - 2013-12-29-02.59.22.744609 29 28.00 5.61 16.76 39.23 - 2013-12-29-04.59.19.964291 28 28.00 5.61 16.76 39.23 - 2013-12-29-06.59.21.630926 27 28.00 5.61 16.76 39.23 - 2013-12-29-08.59.21.438009 28 28.00 5.61 16.76 39.23 - 2013-12-29-10.59.23.584227 25 28.00 5.61 16.76 39.23 - 2013-12-29-12.59.21.282163 26 28.00 5.61 16.76 39.23 - 2013-12-29-14.59.21.834594 26 28.00 5.61 16.76 39.23 - 2013-12-30-02.59.22.184235 28 28.00 5.61 16.76 39.23 - 2013-12-30-04.59.20.708676 28 28.00 5.61 16.76 39.23 - 2013-12-30-06.59.22.108368 27 28.00 5.61 16.76 39.23 - 2013-12-30-08.59.21.587636 25 28.00 5.61 16.76 39.23 - 2013-12-30-10.59.22.238998 25 28.00 5.61 16.76 39.23 - 2013-12-30-12.59.22.796775 25 28.00 5.61 16.76 39.23 - 2013-12-30-14.59.23.225294 25 28.00 5.61 16.76 39.23 - 2013-12-31-02.59.21.102429 29 28.00 5.61 16.76 39.23 - 2013-12-31-04.59.21.569479 25 28.00 5.61 16.76 39.23 - 2013-12-31-06.59.23.647414 22 28.00 5.61 16.76 39.23 - 2013-12-31-08.59.20.955653 21 28.00 5.61 16.76 39.23 - 2013-12-31-10.59.22.305062 25 28.00 5.61 16.76 39.23 - 2013-12-31-12.59.21.948062 25 28.00 5.61 16.76 39.23 - 2013-12-31-14.59.22.475186 25 28.00 5.61 16.76 39.23 - 2014-01-01-02.59.22.500463 29 28.00 5.61 16.76 39.23 - 2014-01-01-04.59.20.827279 32 28.00 5.61 16.76 39.23 - 2014-01-01-06.59.22.793983 25 28.00 5.61 16.76 39.23 - 2014-01-01-08.59.21.752541 24 28.00 5.61 16.76 39.23 - 2014-01-01-10.59.23.231037 25 28.00 5.61 16.76 39.23 - 2014-01-01-12.59.21.734755 26 28.00 5.61 16.76 39.23 - 2014-01-01-14.59.22.130908 25 28.00 5.61 16.76 39.23 - 2014-01-02-02.59.22.590687 29 28.00 5.61 16.76 39.23 - 2014-01-02-04.59.20.482540 19 28.00 5.61 16.76 39.23 - 2014-01-02-06.59.22.188450 18 28.00 5.61 16.76 39.23 - 2014-01-02-08.59.21.802411 19 28.00 5.61 16.76 39.23 - 2014-01-02-10.59.22.142143 23 28.00 5.61 16.76 39.23 - 2014-01-02-12.59.22.418678 21 28.00 5.61 16.76 39.23 - 2014-01-02-14.59.23.502079 21 28.00 5.61 16.76 39.23 - 2014-01-03-02.59.21.983852 24 28.00 5.61 16.76 39.23 - 2014-01-03-04.59.20.052375 25 28.00 5.61 16.76 39.23 - 2014-01-03-06.59.22.796332 24 28.00 5.61 16.76 39.23 - 2014-01-03-08.59.21.498664 25 28.00 5.61 16.76 39.23 - 2014-01-03-10.59.22.339537 27 28.00 5.61 16.76 39.23 - 2014-01-03-12.59.21.118107 26 28.00 5.61 16.76 39.23 - 2014-01-03-14.59.22.920441 27 28.00 5.61 16.76 39.23 - 2014-01-04-02.59.21.069736 28 28.00 5.61 16.76 39.23 - 2014-01-04-04.59.22.136560 24 28.00 5.61 16.76 39.23 - 2014-01-04-06.59.20.755371 27 28.00 5.61 16.76 39.23 - 2014-01-04-08.59.21.308001 27 28.00 5.61 16.76 39.23 - 2014-01-04-10.59.23.657845 26 28.00 5.61 16.76 39.23 - 2014-01-04-12.59.22.409798 25 28.00 5.61 16.76 39.23 - 2014-01-04-14.59.22.739971 25 28.00 5.61 16.76 39.23 - 2014-01-05-02.59.23.457778 28 28.00 5.61 16.76 39.23 - 2014-01-05-04.59.21.635014 26 28.00 5.61 16.76 39.23 - 2014-01-05-06.59.22.609648 28 28.00 5.61 16.76 39.23 - 2014-01-05-08.59.21.726117 27 28.00 5.61 16.76 39.23 - 2014-01-05-10.59.22.303560 26 28.00 5.61 16.76 39.23 - 2014-01-05-12.59.22.386106 25 28.00 5.61 16.76 39.23 - 2014-01-05-14.59.22.223748 25 28.00 5.61 16.76 39.23 - 2014-01-06-02.59.22.063243 28 28.00 5.61 16.76 39.23 - 2014-01-06-04.59.21.584976 25 28.00 5.61 16.76 39.23 - 2014-01-06-06.59.23.410732 24 28.00 5.61 16.76 39.23 - 2014-01-06-08.59.21.937380 26 28.00 5.61 16.76 39.23 - 2014-01-06-10.59.22.598955 26 28.00 5.61 16.76 39.23 - 2014-01-06-12.59.21.811886 24 28.00 5.61 16.76 39.23 - 2014-01-06-14.59.22.751316 25 28.00 5.61 16.76 39.23 - 2014-01-07-02.59.21.602402 26 28.00 5.61 16.76 39.23 - 2014-01-07-04.59.20.468643 26 28.00 5.61 16.76 39.23 - 2014-01-07-06.59.22.609312 24 28.00 5.61 16.76 39.23 - 2014-01-07-08.59.21.271486 24 28.00 5.61 16.76 39.23 - 2014-01-07-10.59.22.033371 26 28.00 5.61 16.76 39.23 - 2014-01-07-12.59.22.275233 26 28.00 5.61 16.76 39.23 - 2014-01-07-14.59.21.897717 24 28.00 5.61 16.76 39.23 - 2014-01-08-02.59.21.510988 30 28.00 5.61 16.76 39.23 - 2014-01-08-04.59.19.987685 27 28.00 5.61 16.76 39.23 - 2014-01-08-06.59.21.395913 24 28.00 5.61 16.76 39.23 - 2014-01-08-08.59.22.260213 27 28.00 5.61 16.76 39.23 - 2014-01-08-10.59.22.855947 27 28.00 5.61 16.76 39.23 - 2014-01-08-12.59.23.598764 25 28.00 5.61 16.76 39.23 - 2014-01-08-14.59.22.716030 27 28.00 5.61 16.76 39.23 - 2014-01-09-02.59.22.587781 28 28.00 5.61 16.76 39.23 - 2014-01-09-04.59.22.009546 19 28.00 5.61 16.76 39.23 - 2014-01-09-06.59.22.631618 23 28.00 5.61 16.76 39.23 - 2014-01-09-08.59.22.061175 22 28.00 5.61 16.76 39.23 - 2014-01-09-10.59.23.046605 35 28.00 5.61 16.76 39.23 - 2014-01-09-12.59.24.026556 27 28.00 5.61 16.76 39.23 - 2014-01-09-14.59.23.492942 27 28.00 5.61 16.76 39.23 - 2014-01-10-02.59.21.214946 29 28.00 5.61 16.76 39.23 - 2014-01-10-04.59.21.105968 28 28.00 5.61 16.76 39.23 - 2014-01-10-06.59.22.471112 26 28.00 5.61 16.76 39.23 - 2014-01-10-08.59.21.804102 23 28.00 5.61 16.76 39.23 - 2014-01-10-10.59.22.667157 24 28.00 5.61 16.76 39.23 - 2014-01-10-12.59.23.000467 24 28.00 5.61 16.76 39.23 - 2014-01-10-14.59.22.518814 25 28.00 5.61 16.76 39.23 - 2014-01-11-02.59.21.932748 30 28.00 5.61 16.76 39.23 - 2014-01-11-04.59.20.940886 26 28.00 5.61 16.76 39.23 - 2014-01-11-06.59.21.980739 27 28.00 5.61 16.76 39.23 - 2014-01-11-08.59.21.660744 26 28.00 5.61 16.76 39.23 - 2014-01-11-10.59.22.917988 24 28.00 5.61 16.76 39.23 - 2014-01-11-12.59.21.187285 24 28.00 5.61 16.76 39.23 - 2014-01-11-14.59.21.294114 25 28.00 5.61 16.76 39.23 - 2014-01-12-02.59.23.007270 29 28.00 5.61 16.76 39.23 - 2014-01-12-04.59.22.012880 26 28.00 5.61 16.76 39.23 - 2014-01-12-06.59.22.891887 26 28.00 5.61 16.76 39.23 - 2014-01-12-08.59.21.256522 27 28.00 5.61 16.76 39.23 - 2014-01-12-10.59.21.668845 24 28.00 5.61 16.76 39.23 - 2014-01-12-12.59.21.249321 24 28.00 5.61 16.76 39.23 - 2014-01-12-14.59.20.855943 24 28.00 5.61 16.76 39.23 - 2014-01-13-02.59.22.589254 34 28.00 5.61 16.76 39.23 - 2014-01-13-04.59.20.892853 29 28.00 5.61 16.76 39.23 - 2014-01-13-06.59.22.350211 28 28.00 5.61 16.76 39.23 - 2014-01-13-08.59.22.006137 21 28.00 5.61 16.76 39.23 - 2014-01-13-10.59.22.789798 24 28.00 5.61 16.76 39.23 - 2014-01-13-12.59.23.661211 23 28.00 5.61 16.76 39.23 - 2014-01-13-14.59.23.819190 23 28.00 5.61 16.76 39.23 - 2014-01-14-02.59.23.611699 24 28.00 5.61 16.76 39.23 - 2014-01-14-04.59.21.127165 27 28.00 5.61 16.76 39.23 - 2014-01-14-06.59.22.375993 25 28.00 5.61 16.76 39.23 - 2014-01-14-08.59.20.458124 23 28.00 5.61 16.76 39.23 - 2014-01-14-10.59.22.970577 23 28.00 5.61 16.76 39.23 - 2014-01-14-12.59.22.441829 23 28.00 5.61 16.76 39.23 - 2014-01-14-14.59.23.370164 23 28.00 5.61 16.76 39.23 - 2014-01-15-04.59.21.395974 25 28.00 5.61 16.76 39.23 - 2014-01-15-08.59.21.609034 25 28.00 5.61 16.76 39.23 - 2014-01-15-12.59.23.629735 22 28.00 5.61 16.76 39.23 - 2014-01-15-14.59.22.361221 24 28.00 5.61 16.76 39.23 - 2014-01-16-02.59.21.991339 27 28.00 5.61 16.76 39.23 - 2014-01-16-04.59.21.206935 24 28.00 5.61 16.76 39.23 - 2014-01-16-06.59.22.210430 19 28.00 5.61 16.76 39.23 - 2014-01-16-08.59.19.815104 19 28.00 5.61 16.76 39.23 - 2014-01-16-10.59.21.262909 21 28.00 5.61 16.76 39.23 - 2014-01-16-12.59.22.100124 21 28.00 5.61 16.76 39.23 - 2014-01-16-14.59.22.624671 22 28.00 5.61 16.76 39.23 - 2014-01-17-02.59.21.232186 20 28.00 5.61 16.76 39.23 - 2014-01-17-04.59.21.051346 25 28.00 5.61 16.76 39.23 - 2014-01-17-06.59.21.966438 24 28.00 5.61 16.76 39.23 - 2014-01-17-08.59.22.509178 23 28.00 5.61 16.76 39.23 - 2014-01-17-10.59.21.793022 24 28.00 5.61 16.76 39.23 - 2014-01-17-12.59.21.842303 24 28.00 5.61 16.76 39.23 - 2014-01-17-14.59.23.264198 25 28.00 5.61 16.76 39.23 - 2014-01-18-02.59.23.272991 29 28.00 5.61 16.76 39.23 - 2014-01-18-04.59.20.943209 31 28.00 5.61 16.76 39.23 - 2014-01-18-06.59.21.807379 31 28.00 5.61 16.76 39.23 - 2014-01-18-08.59.21.761240 27 28.00 5.61 16.76 39.23 - 2014-01-18-10.59.22.643974 25 28.00 5.61 16.76 39.23 - 2014-01-18-12.59.22.990952 27 28.00 5.61 16.76 39.23 - 2014-01-18-14.59.22.953930 25 28.00 5.61 16.76 39.23 - 2014-01-19-02.59.22.558543 30 28.00 5.61 16.76 39.23 - 2014-01-19-04.59.21.522863 33 28.00 5.61 16.76 39.23 - 2014-01-19-06.59.22.101563 30 28.00 5.61 16.76 39.23 - 2014-01-19-08.59.21.089700 28 28.00 5.61 16.76 39.23 - 2014-01-19-10.59.23.279773 26 28.00 5.61 16.76 39.23 - 2014-01-19-12.59.22.481062 25 28.00 5.61 16.76 39.23 - 2014-01-19-14.59.21.855787 25 28.00 5.61 16.76 39.23 - 2014-01-20-02.59.22.115376 28 28.00 5.61 16.76 39.23 - 2014-01-20-04.59.21.735677 32 28.00 5.61 16.76 39.23 - 2014-01-20-06.59.22.267019 30 28.00 5.61 16.76 39.23 - 2014-01-20-08.59.20.212518 26 28.00 5.61 16.76 39.23 - 2014-01-20-10.59.22.641589 24 28.00 5.61 16.76 39.23 - 2014-01-20-12.59.23.288991 24 28.00 5.61 16.76 39.23 - 2014-01-20-14.59.22.542587 24 28.00 5.61 16.76 39.23 - 2014-01-21-02.59.22.348606 27 28.00 5.61 16.76 39.23 - 2014-01-21-04.59.20.919254 28 28.00 5.61 16.76 39.23 - 2014-01-21-06.59.21.961640 30 28.00 5.61 16.76 39.23 - 2014-01-21-08.59.21.676432 26 28.00 5.61 16.76 39.23 - 2014-01-21-10.59.21.526975 26 28.00 5.61 16.76 39.23 - 2014-01-21-12.59.22.596964 27 28.00 5.61 16.76 39.23 - 2014-01-21-14.59.23.319420 25 28.00 5.61 16.76 39.23 - 2014-01-22-02.59.21.956936 31 28.00 5.61 16.76 39.23 - 2014-01-22-04.59.22.173973 24 28.00 5.61 16.76 39.23 - 2014-01-22-06.59.21.552401 21 28.00 5.61 16.76 39.23 - 2014-01-22-08.59.22.815353 25 28.00 5.61 16.76 39.23 - 2014-01-22-11.00.51.852215 25 28.00 5.61 16.76 39.23 - 2014-01-22-13.01.17.595478 21 28.00 5.61 16.76 39.23 - 2014-01-22-15.11.40.750636 24 28.00 5.61 16.76 39.23 - 2014-01-23-02.59.23.180439 22 28.00 5.61 16.76 39.23 - 2014-01-23-04.59.20.923251 21 28.00 5.61 16.76 39.23 - 2014-01-23-06.59.22.620758 21 28.00 5.61 16.76 39.23 - 2014-01-23-08.59.22.031207 22 28.00 5.61 16.76 39.23 - 2014-01-23-10.59.23.626307 21 28.00 5.61 16.76 39.23 - 2014-01-23-13.01.20.198051 28 28.00 5.61 16.76 39.23 - 2014-01-23-15.03.16.326583 28 28.00 5.61 16.76 39.23 - 2014-01-24-02.59.22.322328 33 28.00 5.61 16.76 39.23 - 2014-01-24-04.59.20.657527 29 28.00 5.61 16.76 39.23 - 2014-01-24-06.59.22.369911 25 28.00 5.61 16.76 39.23 - 2014-01-24-08.59.22.197838 25 28.00 5.61 16.76 39.23 - 2014-01-24-10.59.23.679719 25 28.00 5.61 16.76 39.23 - 2014-01-24-12.59.22.608257 27 28.00 5.61 16.76 39.23 - 2014-01-24-14.59.23.534684 28 28.00 5.61 16.76 39.23 - 2014-01-25-02.59.24.883155 26 28.00 5.61 16.76 39.23 - 2014-01-25-04.59.21.018134 29 28.00 5.61 16.76 39.23 - 2014-01-25-06.59.22.311110 30 28.00 5.61 16.76 39.23 - 2014-01-25-08.59.22.166274 28 28.00 5.61 16.76 39.23 - 2014-01-25-10.59.22.391029 27 28.00 5.61 16.76 39.23 - 2014-01-25-12.59.22.622846 27 28.00 5.61 16.76 39.23 - 2014-01-25-14.59.22.998443 27 28.00 5.61 16.76 39.23 - 2014-01-26-02.59.22.783339 30 28.00 5.61 16.76 39.23 - 2014-01-26-04.59.22.340053 32 28.00 5.61 16.76 39.23 - 2014-01-26-06.59.24.737830 28 28.00 5.61 16.76 39.23 - 2014-01-26-08.59.22.775793 28 28.00 5.61 16.76 39.23 - 2014-01-26-10.59.23.090475 25 28.00 5.61 16.76 39.23 - 2014-01-26-12.59.23.628500 24 28.00 5.61 16.76 39.23 - 2014-01-26-14.59.23.679945 25 28.00 5.61 16.76 39.23 - 2014-01-27-02.59.22.048384 33 28.00 5.61 16.76 39.23 - 2014-01-27-04.59.23.822694 28 28.00 5.61 16.76 39.23 - 2014-01-27-06.59.22.645934 26 28.00 5.61 16.76 39.23 - 2014-01-27-08.59.22.023026 24 28.00 5.61 16.76 39.23 - 2014-01-27-10.59.22.219356 25 28.00 5.61 16.76 39.23 - 2014-01-27-12.59.23.625206 28 28.00 5.61 16.76 39.23 - 2014-01-27-14.59.24.140337 26 28.00 5.61 16.76 39.23 - 407 record(s) selected.
Scroll to the right in this sample output to see the FLAG column that tags which rows I should be investigating further.
Caveats
The SQL provided above will not work on an out of the box DB2 database. It includes some performance tracking tables that I developed. But it does show the use of the STDDEV function to calculate a standard deviation, and how to use that information to analyze data. You can use this same methodology on many different metrics. Maybe on read efficiency in MON_GET_PKG_CACHE_STMT? Or on the number of transactions over time, or any of a thousand other metrics that you may be interested in.
Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL.
Ember lives in Denver and work from home
2 comments