You can use EXPLAIN to populate a statement
table, owner. DSN_STATEMNT_TABLE, at the same time
as your PLAN_TABLE is being populated.
About this task
Db2 provides
cost estimates, in service units and in milliseconds, for SELECT,
INSERT, UPDATE, and DELETE statements, both static and dynamic. The
estimates do not take into account several factors, including cost
adjustments that are caused by parallel processing, or the use of
triggers or user-defined functions.
Procedure
Use the information provided in the statement table
to:
- Determine if a statement is not going to perform within
range of your service-level agreements and to tune accordingly.
Db2 puts
its cost estimate into one of two cost categories: category
A or category B. Estimates that go into cost category A are the ones
for which Db2 has
adequate information to make an estimate. That estimate is not likely
to be 100% accurate, but is likely to be more accurate than any estimate
that is in cost category B.
Db2 puts
estimates into cost category B when it is forced to use default values
for its estimates, such as when no statistics are available, or because
host variables are used in a query.
- Give a system programmer a basis for entering service-unit
values by which to govern dynamic statements with predictive governing.