• Container characteristics
  Container characteristics can have a significant impact on the I/O cost associated during query execution. When it selects an access plan, the query 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. Using the rotational-latency formula, this would produce:

       (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 / 1024000 * 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 are arrays of disks (such as RAID), then you must take additional considerations into account when you attempt 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 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 mentioned above where you 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 provided through these two values 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 also considers 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 wait time associated with reading the 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 you create a table space and or use the ALTER TABLESPACE statement. The default prefetch size is determined by the value of the DFT_PREFETCH_SZ database configuration parameter. Review the recommendations for sizing this parameter and make changes as needed to improve the data movement.

    ALTER TABLESPACE RESOURCE
      PREFETCHSIZE 64
      OVERHEAD     19.3
      TRANSFERRATE 0.9