Cost estimation in Query Patroller

Query Patroller evaluates each query that it intercepts based on the estimated size, or cost, of the query. The estimated cost of a query is the estimated total system resources that will be used in the execution of the query. A number of key Query Patroller thresholds are set based on the estimated cost of queries. On Query Patroller graphical user interfaces, this estimated cost is simply referred to as cost, for example, "Maximum cost of a query."

Query Patroller relies on DB2® for information on the estimated cost of queries. For any query submitted to DB2, there might be many different methods, or access plans, for retrieving the data from all of the required tables. An access plan is the data access strategy, which includes index usage, sort methods, locking semantics, and join methods.

The DB2 query optimizer analyzes the different access plans for a query to determine which of these plans is the most efficient in terms of estimated resource cost. In calculating the estimated cost of an access plan, the query optimizer considers both CPU cost (the number of instructions) and I/O (the number of seeks and page transfers). You can capture the details of the access plan that the query optimizer has selected to execute a query. These details are stored in explain tables, which can be queried or displayed using the DB2 explain facility.

The DB2 unit of measure for cost is the timeron. A timeron does not directly equate to any actual CPU execution time, but gives a relative measure of the estimated resources required to execute a particular query according to the selected access plan. Although the estimated cost of a query in timerons does not predict the execution time of that query, it can provide a basis for comparing two queries in the same environment under the same workload. For example, given the same external factors such as hardware capacity and concurrent workload, a very costly query involving complex joins and large table scans can be expected to have a longer execution time than a cheaper, simpler query.