Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 549
Cumulative Total Cost: 20.8465
Cumulative CPU Cost: 87515.2
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 10.4208
Cumulative Re-CPU Cost: 39054.2
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 20.8419
Cumulative Comm Cost:0
Cumulative First Comm Cost:0
Estimated Bufferpool Buffers: 3
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE
Predicates:
----------
2) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 5.06148e-07
Predicate Text:
--------------
(Q2.O_CUSTKEY = Q1.C_CUSTKEY)
2) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 5.06148e-07
Predicate Text:
--------------
(Q2.O_CUSTKEY = Q1.C_CUSTKEY)
Input Streams:
-------------
4) From Object TPCD.O_CK
550 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
Estimated number of rows: 1.00038e+07
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.O_CUSTKEY(A)+Q2.$RID$
Partition Column Names:
----------------------
+1: Q2.O_ORDERKEY
Output Streams:
--------------
5) To Operator #9
Estimated number of rows: 5.0634
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.O_CUSTKEY(A)+Q2.$RID$
Partition Column Names:
----------------------
+1: Q2.O_ORDERKEY
Objects Used in Access Plan:
---------------------------
Schema: TPCD
Name: O_CK
Type: Index
Time of creation: 2004-06-04-07.02.17.390759
Last statistics update: 2004-06-09-23.20.10.138687
Number of columns: 1
Number of rows: 10003789
Width of rows: -1
Number of buffer pool pages: 296184
Distinct row values: No
Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 551
Tablespace name: INDEX_TS
Tablespace overhead: 9.500000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 48
Container extent page count: 16
Index clustering statistic: 0.000046
Index leaf pages: 18875
Index tree levels: 3
Index full key cardinality: 1975707
Index first key cardinality: 1975707
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 18874
Index page density: 99
Index avg sequential pages: 18874
Index avg gap between sequences:0
Index avg random pages: 0
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 10003789
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: TPCD
Base Table Name: ORDERS
Columns in index:
O_CUSTKEY
Schema: ORA
Name: CUSTOMER
Type: Nickname
Time of creation: 2004-06-10-08.32.16.950465
Last statistics update:
Number of columns: 8
Number of rows: 1500000
Width of rows: 236
Number of buffer pool pages: 32173
Distinct row values: No
Tablespace name:
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Schema: TPCD
Name: ORDERS
552 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
Type: Table
Time of creation: 2004-06-04-07.02.15.736633
Last statistics update: 2004-06-09-23.20.10.138687
Number of columns: 9
Number of rows: 10003789
Width of rows: 115
Number of buffer pool pages: 296184
Distinct row values: No
Tablespace name: DATA_TS
Tablespace overhead: 9.500000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 48
Container extent page count: 16
Table overflow record count: 0
Table Active Blocks: -1
Access plan description
The access plan graph for our SQL query has been highlighted in Example B-10
on page 516 under “Access Plan”.
Reading this graph bottom up reveals a significant difference in the access plan
for the same query when a fenced wrapper is used:
The directed table queue operator (DTQ operator 2) indicates that a nested
loop join (NLJOIN operator 3) of the orders table and customer table occurs in
parallel on individual partitions, and the combined result of the rows
(2.27853e+07) from the individual partitions (7.5951e+06) is directed to the
coordinator partition and the RETURN operator 1.
As in the previous trusted wrapper scenario, the bottom left part of the graph
indicates that the ORA.CUSTOMER nickname has 1.5 million rows according
to the DB2 II catalog statistics, and that all these rows are processed by the
SHIP operator 5.
The BTQ operator 4 represents the distribution of remote data from the fmp
process of the fenced wrapper to the db2agntp processes that were created
in each of the partitions for accessing the TPCD.ORDERS table.
The 1.5e+06 rows from the SHIP operator 5 will be the outer table (see
Arguments of the BTQ operator 4) of a nested loop join (NLJOIN operator 3)
with the orders table. The inner table of this nested loop join is estimated to
return 5.0634 rows, and the result of the nested loop join is estimated to be
7.5951e+06 rows.
The 5.0634 rows for the inner table of the nested loop join are formed from a
series of operator executions involving a IXSCAN (operator 10), SORT
(operator 9), RIDSCN (operator 8), and FETCH (operator 7). We have not
Get DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
