260 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
6.6 Approaches to workload tuning
When tuning a query workload, you must understand the conditions under which
the workload must run and the expectations for performance. Consider the
following questions:
1. What is the elapsed execution time for each query in the workload, and for the
entire workload?
2. What are the elapsed execution times under normal conditions and under
peak usage conditions?
3. What is the service level objective for the workload?
4. What other workloads are to run concurrently with this workload, and what
are the service level objectives for those workloads?
When you have the answers to these questions, you know what is expected and
you also know how to validate any improvements you make. By using Optim
Performance Manager to configure each separate query workload, you can
easily derive the answers for questions 1, 2, and 4. In addition, you can create a
performance baseline for the system and database key indicators to evaluate any
effect that might occur if you make changes.
6.6.1 Tuning a workload for optimal performance
Use Optim Performance Manager with IBM Data Studio Version 3.1.1. Configure
Optim Performance Manager so that SQL statement text can be transferred from
the SQL Statements and Extended Insight dashboards directly into IBM Data
Studio. You can then analyze and tune the SQL statement or query workload by
using the features in IBM Data Studio.
Tuning SQL statements
An Actions drop-down box on the SQL Statements dashboard, shown in
Figure 6-20 on page 261, calls and passes the individual statement text or entire

Get Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.