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

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.