DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

Chapter 4. Performance problem determination scenarios 339

Since tuning tends to be a trial-and-error iterative process, it is more than likely

that some of the options suggested could lead to other performance problems

that would need to be investigated and resolved.

4.4.7 Default DB2_FENCED wrapper option with DPF

The DB2_FENCED wrapper option is new in DB2 II V8.2 and allows the DBA to

decide whether the wrapper should operate in trusted or fenced mode. When the

DB2_FENCED parameter is set to ‘Y’ and DB2 II is installed in a DPF

environment, the federated server is able to generate access plans that

parallelize the processing of nickname data, and thereby improve query

performance. With the default setting of DB2_FENCED = ‘N’, there is no

inter-partition parallelism for nickname data. For more details on the performance

considerations of the DB2_FENCED wrapper option, refer to “Choice of DPF” on

page 62.

In this scenario, we diagnose how the default setting of the DB2_FENCED

wrapper option may inhibit a federated query accessing both local and nickname

data in a DPF environment from delivering superior performance.

Triggering event

Users complained about poor response times with a specific query. The users

claimed that they had never really experienced good performance from this

query.

Hypotheses and validations

As before, 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 described in Figure 4-1 on page 117 and 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.

Attention: In the final analysis, what really matters is not what the DB2

optimizer estimates to be the optimal access path based on timerons, but the

actual run times experienced by the user.

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

򐂰 Identify the application and query.

In this case the user specifically identified the query in question as being the

one shown in Example 4-53. It lists all orders by customer. The orders table is

a local DB2 table, while the customer table is a nickname referencing a

remote Oracle table.

Example 4-53 Problem query

select *

from tpcd.orders o, ora.customer c

where o.o_custkey = c.c_custkey

and c_mktsegment = 'AUTOMOBILE'

and c_nationkey > 20;

򐂰 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-54 is a dynamic SQL snapshot, and includes the problem query

(highlighted) in the Statement text field.

Example 4-54 Dynamic SQL snapshot

get snapshot for dynamic sql on fedserv

Dynamic SQL Snapshot Result

Database name = FEDSERV

Database path = /data1/part/db2i64p/NODE0000/SQL00001/

Number of executions = 1

Number of compilations = 1

Worst preparation time (ms) = 816

Best preparation time (ms) = 816

Internal rows deleted = 0

Internal rows inserted = 0

Rows read = 483185

Internal rows updated = 0

Rows written = 483185

Statement sorts = 0

Statement sort overflows = 0

Total sort time = 0

Buffer pool data logical reads = 4