Administration Guide

Table Space Impact on Query Optimization

Certain characteristics of your table spaces can affect the access plan chosen by the SQL compiler:

Container characteristics
Container characteristics can have a significant impact on the I/O cost associated when executing a query. When selecting an access plan the SQL optimizer considers these I/O costs, including any cost differences for accessing data from different table spaces. Two columns in the SYSCAT.TABLESPACES system catalog are used by the optimizer to help estimate the I/O costs of accessing data from a table space:
- OVERHEAD, which provides an estimate (in milliseconds) of the time required by the container before any data is read into memory. This overhead activity includes the container's I/O controller overhead as well as the disk latency time, which includes the disk seek time.
  You may use the following formula to help you estimate the overhead cost:
```
   OVERHEAD = average seek time in milliseconds
              + (0.5 * rotational latency)
```
  where:
  - 0.5 represents an average overhead of one half rotation
  - Rotational latency is calculated, in milliseconds for each full rotation, as follows:
```
   (1 / RPM) * 60 * 1000
```
    where you:
    - Divide by rotations per minute to get minutes per rotation
    - Multiply by 60 seconds per minute
    - Multiply by 1000 milliseconds per second.
  As an example, let the rotations per minute for the disk be 7 200. This would produce, using the rotational latency formula,
```
   (1 / 7200) * 60 * 1000 = 8.328 milliseconds
```
  which can then be used in the calculation of the OVERHEAD estimate with an assumed average seek time of 11 milliseconds:
```
   OVERHEAD = 11 + (0.5 * 8.328)
            = 15.164
```
  giving an estimated OVERHEAD value of about 15 milliseconds.
- TRANSFERRATE, which provides an estimate (in milliseconds) of the time required to read one page of data into memory.
  
  If each table space container is a single physical disk then you may use the following formula to help you estimate the transfer cost in milliseconds per page:
```
   TRANSFERRATE = (1 / spec_rate) * 1000 / 1 024 000 * page_size
```
  where:
  - spec_rate represents the disk specification for the transfer rate, in MB per second
  - Divide by spec_rate to get Seconds per MB
  - Multiply by 1000 milliseconds per second
  - Divide by 1 024 000 bytes per MB
  - Multiply by the page size in bytes (for example, 4 096 bytes for a 4 KB page)
  As an example, suppose the specification rate for the disk is 3 MB per second. This would produce the following calculation
```
   TRANSFERRATE = (1 / 3) * 1000 / 1024000 * 4096
                = 1.333248
```
  giving an estimated TRANSFERRATE value of about 1.3 milliseconds per page.
  If the table space containers are not single physical disks but rather are arrays of disks (such as RAID), then there are additional considerations when attempting to determine the TRANSFERRATE to use. If the array is relatively small then you can multiply the spec_rate by the number of disks, assuming that the bottleneck is at the disk level. However, if the number of disks in the array making up the container is large, then the bottleneck may not be at the disk level, but rather be at one of the other I/O subsystem components such as disk controllers, I/O busses, or the system bus. In this case, you cannot assume that the I/O throughput capability is the product of the spec_rate and the number of disks. Instead, you must measure the actual I/O rate (in MBs) during a sequential scan. For example, a sequential scan could be select count(*) from big_table and will be MBs in size. Divide the result by the number of containers that make up the table space in which big_table resides. Use the result as a substitute for spec_rate in the formula given above. For example, a measured sequential I/O rate of 100 MBs while scanning a table in a four container table space would imply 25 MBs per container, or a TRANSFERRATE of (1/25) * 1000 / 1024000 * 4096 = 0.16 milliseconds per page.
Each of the containers assigned to a table space may reside on different physical disks. For best results, all physical disks used for a given table space should have the same OVERHEAD and TRANSFERRATE characteristics. If these characteristics are not the same, you should use the average when setting the values for OVERHEAD and TRANSFERRATE.
You can obtain media specific values for these columns from the hardware specifications or through experimentation. These values may be specified on the CREATE TABLESPACE and ALTER TABLESPACE statements.
Experimentation becomes especially important in the environment mention above where you may have a disk array as a container. You should create a simple query that moves data and use it in conjunction with a platform-specific measuring utility. You can then re-run the query with different container configurations within your table space. You can use the CREATE and ALTER TABLESPACE statements to change how data is transferred in your environment.
The I/O cost information through these two vaules could influence the optimizer in a number of ways, including whether or not to use an index to access the data, and which table to select for the inner and outer tables in a join.
Prefetching
When considering the I/O cost of accessing data from a table space, the optimizer will also consider the potential impact that prefetching data and index pages from disk can have on the query performance. Prefetching data and index pages can reduce the overhead and waiting time associated with reading the data into the buffer pool. For more information, see Prefetching Data into the Buffer Pool.
The optimizer uses the information from the PREFETCHSIZE and EXTENTSIZE columns in SYSCAT.TABLESPACES to estimate the amount of prefetching that will occur for a table space.
- EXTENTSIZE can only be set when creating a table space (for example using the CREATE TABLESPACE statement). The default extent size is 32 pages (of 4 KB each) and is usually sufficient.
- PREFETCHSIZE can be set when creating a table space and also using the ALTER TABLESPACE statement. The default prefetch size is determined by the value of the DFT_PREFETCH_SZ database configuration parameter which varies depending on the operating system. You should review the recommendations for sizing this parameter in the Default Prefetch Size (dft_prefetch_sz) description and make changes as needed to improve the movement of data.

The following shows an example of the syntax to change the characteristics of the RESOURCE table space:

    ALTER TABLESPACE RESOURCE
      PREFETCHSIZE 64
      OVERHEAD     19.3
      TRANSFERRATE 0.9

After making any changes to your table spaces you should consider rebinding your applications and use the RUNSTATS utility to collect the latest statistics about the indexes to ensure the best access plans are being used.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]