DB2 10.5 for Linux, UNIX, and Windows

Explain information for data operators

A single access plan can perform several operations on the data to satisfy the SQL or XQuery statement and provide results back to you. The query compiler determines the operations that are required, such as a table scan, an index scan, a nested loop join, or a group-by operator.

In addition to showing information about each operator that is used in an access plan, explain output also shows the cumulative effects of the access plan.

Estimated cost information

The following cumulative cost estimates for operators are recorded. These costs are for the chosen access plan, up to and including the operator for which the information is captured.
  • The total cost (in timerons)
  • The number of page I/Os
  • The number of processing instructions
  • The cost (in timerons) of fetching the first row, including any required initial overhead
  • The communication cost (in frames)

A timeron is an invented relative unit of measurement. Timeron values are determined by the optimizer, based on internal values such as statistics that change as the database is used. As a result, the timeron values for an SQL or XQuery statement are not guaranteed to be the same every time an estimated cost in timerons is determined.

If there is more than one network adapter involved, the cumulative communication cost for the adapter with the highest value is returned. These communication cost values only include the costs of network traffic between physical machines. They do not include the cost of passing frames between node partitions on the same physical machine in a partitioned database environment, which therefore do not flow across the network.

Operator properties

The following information that describes the properties of each operator is recorded by the explain facility:
  • The set of tables that have been accessed
  • The set of columns that have been accessed
  • The columns on which the data is ordered, if the optimizer has determined that this ordering can be used by subsequent operators
  • The set of predicates that have been applied
  • The estimated number of rows that will be returned (cardinality)