DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

Chapter 3. Key performance drivers of DB2 II V8.2 107

Estimated number of rows: 250002

Number of columns: 4

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q2.O_ORDERKEY(A)+Q2.$RID$+Q2.$P-ROWID$

+Q2.O_ORDERPRIORITY

........lines have been removed......................

10) SHIP : (Ship)

Cumulative Total Cost: 184277

Cumulative CPU Cost: 2.47251e+09

Cumulative I/O Cost: 7324.35

Cumulative Re-Total Cost: 253.012

Cumulative Re-CPU Cost: 5.35653e+08

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 65.049

Estimated Bufferpool Buffers: 7325.35

Remote communication cost:258251

Arguments:

---------

CSERQY : (Remote common subexpression)

FALSE

DSTSEVER: (Destination (ship to) server)

- (NULL).

RMTQTXT : (Remote statement)

SELECT A0."L_ORDERKEY" FROM "TPCD"."LINEITEM" A0 WHERE

(A0."L_COMMITDATE" < A0."L_RECEIPTDATE") AND (A0."L_ORDERKEY" <= 1000000) AND

(1 <= A0."L_ORDERKEY") ORDER BY 1 ASC, A0."L_COMMITDATE" ASC,

A0."L_RECEIPTDATE" ASC FOR READ ONLY

SRCSEVER: (Source (ship from) server)

DB2SERV

STREAM : (Remote stream)

FALSE

Input Streams:

-------------

3) From Object DB2.LINEITEM

Estimated number of rows: 5.99861e+07

Number of columns: 4

Subquery predicate ID: Not Applicable

108 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

Column Names:

------------

+Q1.$RID$+Q1.L_RECEIPTDATE+Q1.L_COMMITDATE

+Q1.L_ORDERKEY

Output Streams:

--------------

4) To Operator #9

Estimated number of rows: 499694

Number of columns: 3

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q1.L_ORDERKEY(A)+Q1.L_COMMITDATE(A)

+Q1.L_RECEIPTDATE(A)

Objects Used in Access Plan:

---------------------------

Schema: DB2

Name: LINEITEM

Type: Nickname

Time of creation: 2004-06-11-21.33.19.482113

Last statistics update: 2004-06-11-22.38.00.532171

Number of columns: 16

Number of rows: 59986052

Width of rows: 133

Number of buffer pool pages: 2081039

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: ORA

Name: ORDERS

Type: Nickname

Time of creation: 2004-06-11-21.33.10.349783

Last statistics update: 2004-06-11-22.32.45.545670

Number of columns: 9

Number of rows: 15000000

Width of rows: 49

Number of buffer pool pages: 443840