Resource consumption estimates

The following information describes the resource consumption estimates as shown in the SQL PA cost summary report.

The first line of the report contains the QUERY number and the elapsed time. Db2 SQL Performance Analyzer assigns the QUERY number in the 100,000,000 range. SECONDS OF ELAPSED TIME indicates the Db2 SQL Performance Analyzer estimate for how long the query runs before returning data. Elapsed time, also known as response time, can be critical for online systems. Elapsed time is a computed value for INSERT, UPDATE, and DELETE statements based on the following major contributors:

  • CPU processing to be done by application and Db2® address spaces
  • The number of I/O requests and their type (prefetch, synchronous, and asynchronous)
  • The Db2 SQL Performance Analyzer calculated estimate of I/O length (based on the disk drive speed)
  • The processor usage of the attach facility, such as IMS, SPUFI, CAF, or CICS®
  • Known wait times for data set opens and closes, lock waits, and dynamic binds
  • Any other time increments that apply to your SQL query according to Db2 SQL Performance Analyzer

Db2 SQL Performance Analyzer also includes the writing of the pages back out to disk, and logging, processes often accomplished after the query terminates. The Db2 SQL Performance Analyzer elapsed time estimate does not include network time, LAN or VTAM®, which varies depending upon the baud rate, terminal type, modem specifications, and other network characteristics. Elapsed time is presented in seconds.

The second line of the report contains CPU time as a calculated value. The value is the CPU time necessary to perform all the processing for the query, from the time it is initiated, until it terminates. This processing estimate includes, at a minimum, the CPU consumed by the attach facility (unless NONE is selected for the CONNECT parameter), all the I/O processing, including driving the sequential and list prefetches, synchronous reads, asynchronous writes, logging (if any), the stage 1 and 2 predicate processing by Db2, fetching of the rows and columns, locking, RID list manipulation, sorting, get page calls, binding, plus many other facets of the process. CPU time is based upon privately benchmarked path lengths (instruction counts). The path lengths are derived for the many internal processes of Db2, which are then converted into CPU time using the processing power of the target host system.

The third line of the report contains the I/O Estimate. Only the physical I/O counts are included here, not the logical I/O. The physical I/O requirements can vary, depending on which pages are found in the buffer pool, and which must be physically read in from disk.

To understand physical and logical I/O, assume that you have a query that scans 64 pages and returns 25 rows every time you run it. Unless you change the data in the table, or your selection criteria, it always scans 64 pages, and retrieve 25 rows. The 64 pages must be read physically from disk, and brought into the buffer pool, where Db2 can logically scan them (using getpage). Those 64 pages are logical I/O requests. Db2 must scan all those pages to produce your 25 rows. Now, assume that your table is being read by other users. Those other users might have left some pages in the buffer pool the last time they processed. You might no longer need to bring 64 pages into the buffer pool to satisfy your query. Maybe this time you only need 32, or next time, 12, or 40. The logical I/O requirements of your query have not changed (it is still 64 pages), but the physical I/O requirements can vary, depending on which pages are found in the buffer pool, and which must be physically read in from disk.

Db2 counts I/O in a rather peculiar way. A synchronous read I/O is a request for a single page, and it counts as one I/O. A sequential or list prefetch I/O might be for 32 pages, or 16, but it also counts as one I/O. An I/O is defined by the MVS Start I/O instruction (SIO), and it could be issued for a single 4 K page, or for many pages, chained together as a single request. The Db2 SQL Performance Analyzer I/O estimates (and estimates of Db2) do not reflect the number of pages processed. The I/O estimates reflect the number of Start I/O commands issued for physical disk pages. Db2 SQL Performance Analyzer I/O estimates include both reads and writes for tables and indexes, and work files and the Db2 log. For page counts, see the getpage statistic.