Administration Guide

Table Space Impact on Query Optimization

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

• Physical disk characteristics

  Physical disk 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 physical disk before any data is read into memory. This overhead activity includes the disk'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.

  • TRANSFERRATE, which provides an estimate (in milliseconds) of the time required to read one page of data into memory.

    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 page the size (either 4 KB or 8 KB)

  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.

  This I/O cost information 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. The EXTENTSIZE can only be set when creating a table space (for example using the CREATE TABLESPACE statement), while PREFETCHSIZE can be set when creating a table space and also using the ALTER TABLESPACE statement.

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.

[ DB2 List of Books | Search the DB2 Books ]