calculate (overhead and transfer rates)

Overhead 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.

Transfer rate provides an estimate (in milliseconds) of the time required to read one page of data into memory.

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 transfer to use. If the array is relatively small, then you can multiply the transfer_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 at one of the other I/O subsystem components such as disk controllers, I/O buses, or the system bus. In this case, you cannot assume that the I/O throughput capability is the product of the transfer_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 transfer_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 transfer of (1/25) * 1000 / 1 024 000 * 4096 = 0.16 milliseconds per page.

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 rerun 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 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.

For more information, see the Table Space Impact on Query Optimization section in the Administration Guide .