Browse Prior Art Database

Efficient Monitoring of Plan Execution CPU Costs in a Database Server

IP.com Disclosure Number: IPCOM000188191D
Original Publication Date: 2009-Sep-25
Included in the Prior Art Database: 2009-Sep-25
Document File: 9 page(s) / 65K

Publishing Venue

IBM

Abstract

We present a novel method of collecting CPU time for statement executions in a relational database management system that allows us to obtain relatively accurate CPU measurements while significantly reducing the number of system calls required to obtain these usage metrics.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 11% of the total text.

Page 1 of 9

Efficient Monitoring of Plan Execution CPU Costs in a Database Server

Problem Description

One of the key metrics in monitoring the operation of a Relational DBMS is the reporting of the CPU time consumed by various aspects of the system (eg. individual transactions and statements). Knowing the CPU time taken by statements and transactions gives the administrator insight into which users and statements are consuming the most CPU processing resource on the server machine. Knowledge of the CPU consumed by statement executions in particular can be invaluable for the diagnosis of operational performance issues with the database server, including identifying those statements consuming large amounts of CPU on the system, comparing the relative cost or impact of individual statements on the system, and identifying execution plan changes or inefficient plans. It can further be used for performing tasks such as CPU based financial chargeback to RDBMS users at the statement level.

The monitoring of CPU usage for statement executions however poses a particular challenge to implement in a database system as instrumenting the measurement of the CPU time taken by individual statements is relatively expensive in terms of overhead, especially when the statement being measured is extremely short in duration. As an example, on recent TPC-C benchmarks on the DB2 for LUW database server,

just enablin

4% to the execution time of each statement which is a significant issue for customers with very sensitive performance objectives in mind. In other words, the capture of CPU costs for a statement can significantly impact the performance of that statement.

The particular challenge with monitoring the CPU for statement executions is the number of times the low-level CPU usage system call (eg. on AIX, getrusage64 call) must be called in order to obtain accurate metrics. Note that the overall goal is to specifically capture the execution time of individual SQL statements, that is, the portion of work that was driven specifically by the query plan itself as opposed to the CPU involved in the preparation of said plan or any other CPU consumption by the server that did not go towards actual execution of the statement plan. This is important because when comparing the CPU time consumed by multiple executions of various statements we want to be able to make apples to apples comparisons, and if one-time costs like compilation are included in these numbers, it will prevent us from making valid comparisons of this type. The objective is to use the relative CPU costs for the actual statement execution to compare the efficacy of different access plans for the SQL statement.

We can illustrate the particular difficulty with efficiently measuring CPU time for statements using the following scenarios which demonstrate a few sample transactions on the server and indicate where the CPU usage calls need to be made in order to compute ac...