DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

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

I/O blocks are allocated for blocking cursors. If cursors are unable to block

data, performance can be affected.

򐂰 Accepted Block Remote Cursor requests is a counter that records the

number of times a request for an I/O block at the database server was

accepted.

Compute the following metric for tuning purposes. The percentage of rejected

block remote requests (PRBRR) is as follows:

PRBRR = ((Rejected Block Remote Cursor requests) / (Accepted Block Remote

Cursor requests + Rejected Block Remote Cursor requests)) * 100

Consider increasing RQRIOBLK:

򐂰 If PRBRR is consistently high; QUERY_HEAP_SZ should also be increased

correspondingly.

򐂰 When applications receive SQL1221N or SQL1222N messages or see

DIA3605C in the db2diag.log, increase RQRIOBLK incrementally until these

conditions do not reappear.

The number of send and receive requests occurring for a particular application

can be found by enabling the CLI trace facility.

3.4.3 Data source considerations

The DB2 optimizer generates an access plan based on a number of factors, as

discussed in “Query optimization” on page 68, and then generates a query

fragment to be executed at the remote data source.

This query fragment sent to the data source for execution can be identified in the

RMTQTXT field of the SHIP operator in db2exfmt output for the query, as shown

in Example 3-7.

If it is suspected that the query fragment sent to the remote data source is not

performing well, then it can be tuned according to the unique considerations

associated with the particular data source by that data source administrator.

Example 3-7 db2exfmt output with query fragment in RMTQTXT of SHIP operator

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002

Licensed Material - Program Property of IBM

IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

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

DB2_VERSION: 08.02.0

SOURCE_NAME: TOOL1E00

SOURCE_SCHEMA: NULLID

SOURCE_VERSION:

EXPLAIN_TIME: 2004-07-02-19.24.26.071589

EXPLAIN_REQUESTER: KAWA

Database Context:

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

Parallelism: None

CPU Speed: 4.723442e-07

Comm Speed: 100

Buffer Pool size: 78000

Sort Heap size: 20000

Database Heap size: 1200

Lock List size: 100

Maximum Lock List: 10

Average Applications: 1

Locks Available: 1020

Package Context:

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

SQL Type: Dynamic

Optimization Level: 5

Blocking: Block All Cursors

Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 1 ----------------

QUERYNO: 1

QUERYTAG:

Statement Type: Select

Updatable: No

Deletable: No

Query Degree: 1

Original Statement:

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

SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT

FROM ORA.ORDERS

WHERE O_ORDERKEY BETWEEN 1 AND 1000000 AND EXISTS

(SELECT *

FROM DB2.LINEITEM

WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE )

GROUP BY O_ORDERPRIORITY

ORDER BY O_ORDERPRIORITY

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

Optimized Statement:

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

SELECT Q4.$C0 AS "O_ORDERPRIORITY", Q4.$C1 AS "ORDER_COUNT"

FROM

(SELECT Q3.$C0, COUNT(* )

FROM

(SELECT DISTINCT Q2.O_ORDERPRIORITY, Q2.$P-ROWID$

FROM DB2.LINEITEM AS Q1, ORA.ORDERS AS Q2

WHERE (Q1.L_ORDERKEY = Q2.O_ORDERKEY) AND (Q1.L_COMMITDATE <

Q1.L_RECEIPTDATE) AND (Q2.O_ORDERKEY <= 1000000) AND (1 <=

Q2.O_ORDERKEY)) AS Q3

GROUP BY Q3.$C0) AS Q4

ORDER BY Q4.$C0

Access Plan:

-----------

Total Cost: 222926

Query Degree:1

Rows

RETURN

( 1)

Cost

I/O

|

5

GRPBY

( 2)

222926

16810.7

|

250002

TBSCAN

( 3)

222896

16810.7

|

250002

SORT

( 4)

222762

16810.7

|

250002

MSJOIN

( 5)

222162

16810.7

/---+---\

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

250002 1.99876

SHIP FILTER

( 6) ( 9)

37714.3 184277

9486.34 7324.35

| |

1.5e+07 499694

NICKNM: ORA SHIP

ORDERS ( 10)

184277

7324.35

|

5.99861e+07

NICKNM: DB2

LINEITEM

1) RETURN: (Return Result)

Cumulative Total Cost: 222926

Cumulative CPU Cost: 4.89221e+09

Cumulative I/O Cost: 16810.7

Cumulative Re-Total Cost: 222327

Cumulative Re-CPU Cost: 3.62365e+09

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 222794

Estimated Bufferpool Buffers: 0

Remote communication cost:397356

Arguments:

---------

BLDLEVEL: (Build level)

DB2 v8.1.1.64 : s040509

ENVVAR : (Environment Variable)

DB2_EXTENDED_OPTIMIZATION = ON

STMTHEAP: (Statement heap size)

8192

Input Streams:

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

9) From Operator #2

Estimated number of rows: 5

Number of columns: 2

Subquery predicate ID: Not Applicable

Column Names:

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

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

+Q5.O_ORDERPRIORITY(A)+Q5.ORDER_COUNT

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

6) SHIP : (Ship)

Cumulative Total Cost: 37714.3

Cumulative CPU Cost: 4.40279e+08

Cumulative I/O Cost: 9486.34

Cumulative Re-Total Cost: 168.177

Cumulative Re-CPU Cost: 3.56048e+08

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 100.032

Estimated Bufferpool Buffers: 9486.51

Remote communication cost:139105

Arguments:

---------

CSERQY : (Remote common subexpression)

FALSE

DSTSEVER: (Destination (ship to) server)

- (NULL).

JN INPUT: (Join input leg)

OUTER

RMTQTXT : (Remote statement)

SELECT A0."O_ORDERKEY", A0."O_ORDERPRIORITY", A0.ROWID FROM

"IITEST"."ORDERS" A0 WHERE (1 <= A0."O_ORDERKEY") AND (A0."O_ORDERKEY" <=

1000000) ORDER BY 1 ASC

SRCSEVER: (Source (ship from) server)

ORASERV

STREAM : (Remote stream)

FALSE

Input Streams:

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

1) From Object ORA.ORDERS

Estimated number of rows: 1.5e+07

Number of columns: 1

Subquery predicate ID: Not Applicable

Column Names:

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

+Q2.$RID$

Output Streams:

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

2) To Operator #5