DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

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