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

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.