Detailed Analysis of Individual Performance Metrics

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.

You may also like...

2 Responses

  1. February 4, 2014

    […] Detailed Analysis of Individual Performance Metrics […]

  2. February 25, 2014

    […] There are a number of different things and ways to look at the data here. Queries below will also make use of my blog entry on Detailed Analysis of Individual Performance Metrics. […]

Leave a Reply

Your email address will not be published. Required fields are marked *