Certain characteristics of your table spaces can affect the access plan chosen by the SQL compiler:
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 = average seek time in milliseconds + 0.5 * rotational latency
1 / RPM * 60 * 1000
TRANSFERRATE = 1 / spec_rate * 1000 / 1,024,000 * page_size
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.
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 ]