Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 453
nickname and the plan using the MQT is lower cost than the plan using the
nickname, then:
򐂰 The original SQL statement contains the nickname.
򐂰 The optimized SQL contains the nickname.
򐂰 The access plan contains the MQT name.
If Query Rewrite internal rules do not cause it to substitute the MQT for the
nickname, and the optimizer determines that the MQT can be substituted for the
nickname but the plan using the MQT is higher cost than the plan using the
nickname, then:
򐂰 The original SQL statement contains the nickname.
򐂰 The optimized SQL contains the nickname.
򐂰 The access plan contains the nickname (the MQT is not used).
If Query Rewrite internal rules do not cause it to substitute the MQT for the
nickname, and the optimizer determines that the MQT cannot be substituted for
the
򐂰 The original SQL statement contains the nickname.
򐂰 The optimized SQL contains the nickname.
򐂰 The access plan contains the nickname (the MQT is not used).
Access plan graph
Example B-4 shows this section of the output as a a graphical representation of
the plan created to execute the SQL statement.
Example: B-4 Access Plan section
Access Plan:
-----------
Total Cost: 8.22466e+06
Query Degree:1
Rows
RETURN
( 1)
Cost
I/O
|
6
SHIP
( 2)
8.22466e+06
2.08104e+06
|
454 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
5.99861e+07
NICKNM: ORA
LINEITEM
At the top of the access plan graph is the total query cost in timerons
1
and the
query degree of parallelism used.
The following points can be noted about the graph:
򐂰 The graph is made up of objects (nickname ORA.LINEITEM)—operators
(RETURN and SHIP) that are connected by lines. Table B-3 on page 446
defines the function of the operators shown in the db2exfmt output.
򐂰 Each operator has a reference number included in parentheses, for example,
the reference number for the SHIP operator is 2. This reference number does
not imply the operator execution order.
򐂰 Values above and below the object and operators represent cardinality and
costs, as described below.
The access plan graph should be read as follows:
򐂰 Read the graph from the bottom up since it represents the order of the
sub-operations as they would be executed. The RETURN operator at the top
represents the query result.
򐂰 For each operator, the legend for the numbers above and below are explained
in the operator with the title ‘RETURN’.
The number above the operator is the number of rows estimated to be
involved in this sub-operation.
The reference number for finding details of this operator is in the section
below the access plan graph (in parenthesis).
The number immediately below the operator is the cumulative estimated
cost expressed in timerons for the sub-operation and the sub-operations
that precede it reading from the bottom up. The incremental estimated
cost of an operation is the number below the operator minus the number
below the operator that precedes it.
The second number below an operator is an estimated cumulative I/O cost
of the operator.
򐂰 For an object such as the ORA.LINEITEM nickname, the value above its
name represents the cardinality of the object as derived from the CARD
column of the SYSCAT.TABLES view in the DB2 II catalog.
1
A timeron is an abstract unit of measure that does not directly equate to any actual elapsed time,
but gives a relative estimate of the resources required by the database manager to execute an access
plan. The resources calculated in the estimate include weighted CPU, I/O, and remote
communication costs.

Get DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.