The Application DBA Workload Management


Workload measurement is the process of measuring a system’s workload and relating that workload to the system’s overall capacity. Workload characterization is the process of identifying what applications or processes are contributing to a system’s workload and by how much. Workload management is the overall process of identifying a system’s workload. Workload management addresses the following areas:

bullet Workload measurement
bullet Workload characterization
bullet Capacity planning
bullet Workload reduction

It is important to understand that workload management is not performance tuning. Workload management may provide indicators of wasteful workload, but workload measurement is not a substitute for an economically based performance tuning strategy. If a system has excess capacity, then any waste caused by sub-optimal workloads is probably not a problem. In other words, why tune something if there is no economic benefit to it. However, when business processes fail to meet service level requirements then it becomes economically beneficial to correct them. In this case, AppsDBA Consulting recommends the Hotsos Method R approach for all performance tuning, or response time optimization needs.

However, when a system’s workload consists of highly optimized jobs or jobs that are not economically justifiable to further optimize, and that workload has outgrown the resources available, then workload management may provide information that can be used to better balance the workload through the use of work shifts, prioritization, or even elimination.

See the AppsDBA paper "Oracle Workload Measurement" for more details.

Workload Measurement

The ability to measure workload rates has been instrumented into the Oracle database through various system and session level "statistics". These statistics are, in general, fairly accurate and can be used to develop a good view of database workload. One way of verifying this accuracy is to also use operating system measurements as a check against the Oracle statistics. This is the approach taken by the Open Source utility Sysmon.

Workload Characterization

Characterizing workload is the process of identifying classes of workload, measuring those classes and then calculating the costs of those classes of workload. The purpose of workload characterization is to be able to allocate available capacity to the classes that are most important to the business. See the AppsDBA paper "Oracle Workload Characterization" and the AppsDBA utility Workmon.

Capacity Planning

Capacity planning is the process of predicting when future load levels will saturate the system and determining the most cost-effective way of delaying system saturation as much as possible [Menasce and Almeida (2002), 12-13]. However, most capacity planning efforts seem to focus on new system rollouts and whether there is enough hardware to support the application. Capacity planning should be an ongoing process, and part of the input to a good capacity planning methodology is past workload.

Workload Reduction

Premise: It's always OK to reduce workload. So, in the absence of end user complaints:

  1. Identify the most wasteful, previously unoptimized workload over a workload cycle.
  2. Measure the benefit of reducing or eliminating that workload waste versus the cost of reducing or eliminating it.
  3. If economically beneficial then fix it, otherwise stop until something changes.
  4. Repeat step 1.

The key to effective workload reduction is to define the workload cycle(s) and create a simple methodology to estimate the costs of both the workload waste and the fix to remove the waste. In identifying wasteful workload and the cost to fix it, it may also be beneficial to consider the current system workload and take into account work with a high usage of the resource with the least amount of remaining headroom. It may be the case that there is an opportunity to consume less of a resource with little headroom even though the total "cost" may increase with additional use of resources having higher headroom.

Copyright 1998-2013 AppsDBA Consulting. All Rights Reserved.