The explain facility is used to display the query access
plan that was chosen by the query optimizer to run an SQL statement.
It contains extensive details about the relational operations used
to run the SQL statement, such as the plan operators, their arguments,
order of execution, and costs. Because the query access plan is one
of the most critical factors in query performance, it is important
to understand explain facility output when diagnosing query performance
problems.
Explain information is typically used to:
- Understand why application performance has changed
- Evaluate performance tuning efforts
Analyzing performance changes
To help you
understand the reasons for changes in query performance, perform the
following steps to obtain
"before and after" explain information:
- Capture explain information for the query before you make any
changes, and save the resulting explain tables. Alternatively, you
can save output from the db2exfmt utility. However,
having explain information in the explain tables makes it easy to
query them with SQL, and facilitates more sophisticated analysis.
As well, it provides all of the obvious maintenance benefits of having
data in a relational DBMS. The db2exfmt tool can
be run at any time.
- Save or print the current catalog statistics. You can also use
the db2look command to help perform this task.
In DB2® Version 9.7, you can
collect an explain snapshot when the explain tables are populated.
The explain snapshot contains all of the relevant statistics at the
time that the statement is explained. The db2exfmt utility
will automatically format the statistics that are contained in the
snapshot. This is especially important when using automatic or real-time
statistics collection, because the statistics used for query optimization
might not yet be in the system catalog tables, or they might have
changed between the time that the statement was explained and when
the statistics were retrieved from the system catalog.
- Save or print the data definition language (DDL) statements, including
those for CREATE TABLE, CREATE VIEW, CREATE INDEX, and CREATE TABLESPACE.
The db2look command will also perform this task.
The information that you collect in this way provides a reference
point for future analysis. For dynamic SQL statements, you can collect
this information when you run your application for the first time.
For static SQL statements, you can also collect this information at
bind time. It is especially important to collect this information
before a major system change, such as the installation of a new service
level or DB2 release, or before
a significant configuration change, such as adding or dropping database
partitions and redistributing data. This is because these types of
system changes might result in an adverse change to access plans.
Although access plan regression should be a rare occurrence, having
this information available will help you to resolve performance regressions
faster. To analyze a performance change, compare the information that
you collected previously with information about the query and environment
that you collect when you start your analysis.
As a simple example,
your analysis might show that an index is no longer being used as
part of an access plan. Using the catalog statistics information displayed
by
db2exfmt, you might notice that the number of
index levels (NLEVELS column) is now substantially higher than when
the query was first bound to the database. You might then choose to
perform one of the following actions:
- Reorganize the index
- Collect new statistics for your table and indexes
- Collect explain information when rebinding your query
After you perform one of these actions, examine the access plan
again. If the index is being used once again, query performance might
no longer be a problem. If the index is still not being used, or if
performance is still a problem, try a second action and examine the
results. Repeat these steps until the problem is resolved.
Evaluating performance tuning efforts
You
can take a number of actions to help improve query performance, such
as adjusting configuration parameters, adding containers, or collecting
fresh catalog statistics.
After you make a change in any of
these areas, you can use the explain facility to determine the affect,
if any, that the change has had on the chosen access plan. For example,
if you add an index or materialized query table (MQT) based on index
guidelines, the explain data can help you to determine whether the
index or materialized query table is actually being used as expected.
Although
the explain output provides information that allows you to determine
the access plan that was chosen and its relative cost, the only way
to accurately measure the performance improvement for a query is to
use benchmark testing techniques.
DB2 10.5 for Linux, UNIX, and Windows