DB2 Data Warehouse
Per formance
Find problem SQL queries and
tune them for quicker response
Robert Catterall
(rcatterall@catterallconsulting.
com) is president of Catterall
Consulting, a provider of DB2
consulting and training services.
Part 1 of my column, in the previous issue of IBM Database Magazine, covered IBM DB2 data warehouse performance management from a system and database perspective. In
this follow-on, I’ll provide recommendations
for SQL statement–level performance tuning.
Priority one: Target selection
When it comes to query tuning, it’s important for you to do not only good work, but
the right work. Your first impulse might be
to work on queries with long run times, but
these aren’t necessarily the “problem”
queries. A query that runs for 10 seconds can
cause a lot of dissatisfaction if it is executed
frequently and is part of a process that users
expect to complete in a couple of seconds.
Often, the best indicator of where tuning
work is most needed is “the voice of the user.”
Absent specific complaints, you’ll want to
spend your time on the queries that stand out
in terms of both run time (and/or CPU time)
and frequency of execution.
There are several tools on the market that
an facilitate query tuning target selection
(IBM’s offerings include DB2 Query Monitor
for z/OS and DB2 Performance Expert for
Linux, UNIX, and Windows). However, you
can get useful query target selection information from DB2 itself. Mainframers should look
at the STM TCACHE ALL option of the EXPLAIN
statement (introduced via DB2 for z/OS V9,
and delivered for V8 by way of the fix for APAR
PQ88073). EXPLAIN S TM TCACHE ALL will insert
c
(
into the DSN_STATEMENT_CACHE_TABLE
a row for each SQL statement in the dynamic
statement cache. Among the 40-plus columns
in the DSN_STATEMENT_CACHE_TABLE
are those in which a query’s statement text,
accumulated elapsed time, accumulated CPU
time, and number of executions are recorded.
That should help you to zero in on statements
that are likely to provide a good return on your
investment of performance-tuning time.
Over on the Linux, UNIX, and Windows
LU W) side, DB2 folks should take advantage
of the administrative views introduced with
DB2 9 (the high-level qualifier for these
views is SYSIBMADM; you can read about
them in the DB2 9 for LUW System
Monitoring Guide and Reference). One of
the views that can be particularly useful is
SYSIBMADM.TOP_DYNAMIC_SQL, which
includes columns named STMT_TEXT,
AVERAGE_EXECU TION_TIME_S, and NUM_
EXECUTIONS.
Access path selection: What did
DB2 decide, and why?
Statement-level tuning usually involves
finding a better way to access the data
needed for generating a query result set. Will
a table be scanned in its entirety? Will an
index be used? If tables are to be joined, what
join method should be used? Answering
these questions generates the query’s access
path; your tuning aim is to get a better path
that will reduce statement execution time.
