272 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
Objects Used in Access Plan:
---------------------------
Schema: DB2
Name: ORDERS
Type: Nickname
Time of creation: 2004-08-04-21.28.22.800997
Last statistics update:
Number of columns: 9
Number of rows: 15000000
Width of rows: 16
Number of buffer pool pages: 443840
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: LINEITEM
Type: Nickname
Time of creation: 2004-08-04-21.35.47.856522
Last statistics update: 2004-08-04-21.41.05.553007
Number of columns: 16
Number of rows: 59986052
Width of rows: 35
Number of buffer pool pages: 987218
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
4.4.6 Pushdown problems
The DB2_MAXIMAL_PUSHDOWN server option allows the user or DBA to direct
the optimizer to choose an access plan based on cost or have as much query
processing as possible be performed by the remote data sources. With the
default setting of DB2_MAXIMAL_PUSHDOWN of ‘N’, the optimizer is directed to
choose an access plan based on cost optimization.
Chapter 4. Performance problem determination scenarios 273
In this scenario, we diagnose how the default setting of the
DB2_MAXIMAL_PUSHDOWN server option may inhibit a particular federated
query (about which the user has additional domain expertise) from achieving
superior performance.
Triggering event
Users complained about poor response times with a specific query.
Hypotheses and validations
Here again, since these were user complaints about the performance of a
specific query, as per Figure 4-3 on page 122, we decided to enter the DB2
hypotheses hierarchy shown in Figure 4-1 on page 117 and described in
Example 4.2 on page 119, at a lower level, bypassing network- and
system-related problems, and focused directly on federated application/query
performance, as follows.
Hypothesis 1: Federated application/query performance
Before one can investigate the cause of a query’s performance problem, one
needs to identify the query in question. After identifying the query in question,
one can begin diagnosing whether the performance problem is at the federated
server, the remote data source, or equally divided between the two, as discussed
in Example 4.2 on page 119.
򐂰 Identify the application and query.
In this case the user specifically identified the query in question as being the
one shown in Example 4-48. It finds which supplier should be selected to
place an order for a given (brass, size 15) part in a given region (Europe)
based on the minimum supplier cost. The parts, supplier, and parts supplier
tables reside on DB2, while the nation and region tables reside on Oracle.
Example 4-48 Problem query
SELECT S_ACCTBAL, S_NAME, N_NAME, P_PARTKEY, P_MFGR, S_ADDRESS, S_PHONE,
S_COMMENT
FROM db2.PART, db2.SUPPLIER, db2.PARTSUPP, ora.NATION, ora.REGION
WHERE P_PARTKEY = PS_PARTKEY AND
S_SUPPKEY = PS_SUPPKEY AND
P_SIZE = 15 AND
P_TYPE LIKE '%BRASS' AND
S_NATIONKEY = N_NATIONKEY AND
R_NAME = 'EUROPE' AND
PS_SUPPLYCOST =
(SELECT MIN(PS_SUPPLYCOST)
FROM db2.PARTSUPP, db2.SUPPLIER, ora.NATION, ora.REGION
WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND
S_NATIONKEY = N_NATIONKEY AND
274 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
N_REGIONKEY = R_REGIONKEY AND
R_NAME = 'EUROPE' )
ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY
FETCH FIRST 100 ROWS ONLY
򐂰 Determine if the problem is at the Federated server or the data source.
Once the problem query has been identified, we need to determine if the
query’s performance problem is at the federated server, the remote data
source, or distributed between both.
This information can be determined from a dynamic SQL snapshot, as
discussed in 4.2.3, “Federated server or remote data source” on page 136.
Example 4-49 is a dynamic SQL snapshot, and includes the problem query
(highlighted) in the Statement text field.
Example 4-49 Dynamic SQL snapshot
get snapshot for dynamic sql on fedserv
Dynamic SQL Snapshot Result
Database name = FEDSERV
Database path = /data1/npart/db2i64/NODE0000/SQL00001/
Number of executions = 1
Number of compilations = 1
Worst preparation time (ms) = 142
Best preparation time (ms) = 142
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 100
Internal rows updated = 0
Rows written = 4668
Statement sorts = 62833
Statement sort overflows = 1
Total sort time = 13
Buffer pool data logical reads = 6
Buffer pool data physical reads = 1
Buffer pool temporary data logical reads = 292
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 7
Buffer pool index physical reads = 3
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 80.444751
Total user cpu time (sec.ms) = 9.920000
Total system cpu time (sec.ms) = 1.980000

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.